Microsoft KB Archive/304341

= INF: How to Create a Derived Measure Instead of a Calculated Member =

Article ID: 304341

Article Last Modified on 2/21/2007

-

APPLIES TO


 * Microsoft SQL Server OLAP Services
 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q304341



SUMMARY
SQL Server Books Online states that a calculated member is a dimension member whose value is calculated at run-time by using the expression specified. Only the definitions for calculated members are stored and values are calculated in memory when needed to answer a query.

In some cases, you can create a &quot;derived&quot; measure that is directly based on source data columns to improve query performance. Unlike calculated members, a &quot;derived&quot; measure is calculated during cube processing and is stored in Analysis Services.

This article shows you how to create a &quot;derived&quot; measure named Profit_Derived to replace the existing calculated member Profit of cube Sales in the FoodMart 2000 sample database.



MORE INFORMATION
To create a &quot;derived&quot; measure named Profit_Derived to replace the existing calculated member Profit of cube Sales, follow these steps:
 * 1) Start Analysis Manager. Under the FoodMart 2000 database, expand the Cubes folder.
 * 2) Right-click the Sales cube, and then click Edit...
 * 3) In the Cube Editor, on the Insert menu, click Measure.
 * 4) In the Insert Measure dialog box, click store_sales as the source column, and then click OK to create a new measure named &quot;Store Sales 1&quot;.
 * 5) In the Editor tree pane, select the measure Store Sales 1.
 * 6) If the Properties pane is not expanded, expand it by clicking Properties beneath the tree pane.
 * 7) In the Properties pane, change the name to Profit_Derived.
 * 8) Click the value beside the Source Column and change it to &quot;sales_fact_1997&quot;.&quot;store_sales&quot; - &quot;sales_fact_1997&quot;.&quot;store_cost&quot;.
 * 9) Delete the calculated member Profit from the Editor tree pane. You will now use Profit_Derived.
 * 10) Re-process the Sales cube.

The Source Column string is used to construct the Select statement against the source data during processing, so it accepts most built-in functions that are defined by the source relational database management system (RDBMS). If a MDX function is required when you define a measure, use a calculated member instead.

Additional query words: OLAP MDX BIHOWTO

Keywords: kbinfo KB304341

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.