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 Aug 16, 2004

Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures - Page 5

By William Pearson

Procedure - Creating a Derived Measure

We will next create a derived measure, whose role will be to improve performance from the perspective of querying, and thus from that of the information consumers. The derived measure will be directly based upon columns in the FoodMart database, upon which we will use SQL to perform the required logic, as we shall see. The key reason, we will recall, for creating the derived measure is that the measure is calculated as a part of cube processing, and is stored completely in MSAS for rapid retrieval, versus being generated at runtime like the calculated measure.

We will name our derived measure DM_Cost with Promo Alloc, and, once we verify that it stores the desired values, will replace the MSAS Admin's calculated member that we recreated in the last section.

1.  In the tree pane of the Cube Editor, right click the Measures folder.

2.  Select New Measure from the context menu that appears, as shown in Illustration 14.

Click for larger image

Illustration 14: Select New Measure ...

The Insert Measure dialog box appears, offering us the appropriate columns in the designated fact table for the cube, sales_fact_1997.

3.  Click the store_cost column to select it, as depicted in Illustration 15.

Illustration 15: Select Store_Cost (Circled) ...

4.  Click OK to accept the selection, and to close the Insert Measure dialog.

The new measure appears in the tree, named Store Cost 1 by default (because a measure named Store Cost already exists in the cube), as shown in Illustration 16.

Illustration 16: The New Measure Appears ...

5.  Click the new Store Cost 1 measure to select it, if necessary.

We will now rename the measure, and then add the syntax to the Source Column property to enable the same logic in the measure that we saw in the original calculated measure, and which we have confirmed with management to be correct.

6.  Expand the Properties pane that appears below the tree if required, by clicking Properties, as depicted in Illustration 17.

Illustration 17: Upward Arrow Indicates Properties Pane is Collapsed ...

The Properties pane, Basic tab, appears, expanded, as shown in Illustration 18.

Illustration 18: Expanded Properties - Basic Tab

7.  In the Name property, replace the existing name, Store Cost 1, with the following name:

DM_Cost with Promo Alloc 

8.  Type the following directly into the Source Column property, replacing "sales_fact_1997"."store_cost":

IIf("sales_fact_1997"."promotion_id" = 0, 
  "sales_fact_1997"."store_cost"* 1.15 )

The Properties pane, Basic tab, with our modifications, appears as depicted in Illustration 19.

Illustration 19: Properties Pane - Basics Tab, with our Modifications

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