Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I - Page 2
November 17, 2003
Creating a Calculated Cell
We can create calculated cells with the Calculated Cell Wizard or the Cube Editor, both of which are accessed from within Analysis Services. Calculated cells originated in this way are created with a global scope, meaning that they will be "permanently" available to all information consumers who browse the cube, much like a calculated member created within the cube superstructure.
We can also create calculated cells with a session scope, meaning that they are available only to MDX queries executed within the session in which they are born. These calculated cells are essentially "creatures of the MDX query," and they can be used to provide a higher level of flexibility for client applications (among other uses).
We will examine the steps involved in creating calculated cells under both global and session scopes, in this and the subsequent article, respectively. We will focus on the calculated cell in an MDX query in our next lesson, Using Calculated Cells in Analysis Services, Part I.
Creating a calculated cell within Analysis Services, the primary focus of this lesson, will serve to introduce us to general concepts from the perspective of the Calculated Cell Wizard, in which the process of creation is largely guided and user-friendly. This brief overview with the wizard will afford us an opportunity to focus on the attributes and characteristics of the calculated cell itself, before getting involved in the MDX syntax in our next lesson.
Creating the Basic Calculated Cell
We will start Analysis Services, navigating to the Warehouse cube, which is supplied as a sample when we install Analysis Services.
1. Start Analysis Manager.
2. Expand the Analysis Servers folder by clicking the + sign to its left.
3. Expand your server (typically named the same as your PC, but determined by the installation/setup), exposing the FoodMart2000 database, as shown in Illustration 1.
4. Expand the FoodMart2000 database by clicking on the + sign to its left.
5. Expand the Cubes folder by clicking the + sign to its left.
The cubes appear, similar to those shown in Illustration 2.
6. Right-click the Warehouse cube, then click Edit from the context menu.
The Cube Editor appears.
First, let's set the stage for the steps to come. We will be performing an analysis that focuses on individual warehouses, so let's set up the Data viewing pane to conform to our needs.
7. Click the Data tab on the right half of the screen.
8. Drag the Warehouse dimension to the rows area (the left column of the lower pane section), dropping the icon onto the current column heading.
9. Ensure that 1997 is displayed in the filter box in the Time dimension in the top half of the pane, indicating that we are filtering on 1997.
10. Double-click USA, in the Country column, to expand the hierarchy and show the underlying states.
11. Double-click the State Province column heading to drill down to display members of the City level.
12. Double-click the City column heading to drill down to display individual Warehouse Names.
13. Scroll over so that the Warehouse Cost measure appears alongside the individual warehouse members.
The Cube Editor Preview pane should appear, as partially shown in Illustration 3.
14. Select Insert from the top menu.
15. Click Calculated Cells... on the Insert menu, as shown in Illustration 4.
The Calculated Cells Wizard Welcome dialog appears, as shown in Illustration 5.
16. Click Next.
The Define the Calculation Subcube dialog box appears.