Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 17, 2003

Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I - Page 2

By William Pearson

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.

Illustration 1: Navigate to the FoodMart2000 Database in Analysis Manager

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.

Illustration 2: Sample Cubes Provided with the Analysis Services Installation

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.

Illustration 3: Cube Editor Preview Pane (Partial View), After Adjustments

14.  Select Insert from the top menu.

15.  Click Calculated Cells... on the Insert menu, as shown in Illustration 4.

Illustration 4: Select Calculated Cells... to Initialize the Calculated Cells Wizard

The Calculated Cells Wizard Welcome dialog appears, as shown in Illustration 5.

Illustration 5: The Calculated Cells Wizard Welcome Dialog Box

16.  Click Next.

The Define the Calculation Subcube dialog box appears.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM