INF: How to Create a Derived Measure Instead of a Calculated Member (304341)

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 "derived" measure that is directly based on source data columns to improve query performance. Unlike calculated members, a "derived" measure is calculated during cube processing and is stored in Analysis Services.

This article shows you how to create a "derived" 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 "derived" measure named Profit_Derived to replace the existing calculated member Profit of cube Sales, follow these steps:

Start Analysis Manager. Under the FoodMart 2000 database, expand the Cubes folder.
Right-click the Sales cube, and then click Edit...
In the Cube Editor, on the Insert menu, click Measure.
In the Insert Measure dialog box, click store_sales as the source column, and then click OK to create a new measure named "Store Sales 1".
In the Editor tree pane, select the measure Store Sales 1.
If the Properties pane is not expanded, expand it by clicking Properties beneath the tree pane.
In the Properties pane, change the name to Profit_Derived.
Click the value beside the Source Column and change it to "sales_fact_1997"."store_sales" - "sales_fact_1997"."store_cost".
Delete the calculated member Profit from the Editor tree pane. You will now use Profit_Derived.
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.