- Terminology
- Pivoting Cube Data
- Member Names and Keys
- Calculated Members
- Member Properties
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Calculated Members
We saw calculated members in the left pane of the Cube Editor. In a nutshell, calculated members are measures derived from other measures. The Foodmart sample database contains very simple calculated members: profit and sales average. Profit is calculated by subtracting the cost from total sales, whereas sales average is total sales divided by count of sales. Notice that we could easily perform the same calculations within an MDX query, as follows:
WITH MEMBER [measures].[store profit] AS '[Measures].[Store Sales]-[Measures].[Store Cost]'
MEMBER [measures].[sales avg] AS '[measures].[store sales] / [measures].[sales count]'
SELECT {[store type].members} ON ROWS,
{measures.[store profit], measures.[sales avg] } ON COLUMNS
FROM sales
WHERE ([time].[year].[1997])
So what's the advantage of calculated members? Analysis Services treats the calculated members just as any other measure; so the aggregations for calculated members are calculated during cube processing. If you perform calculations directly within your query, Analysis Services has to calculate the summary values during query execution. Therefore, calculated members will perform better than equivalent calculations performed within queries.
In the real world, the calculated members can get much more complex than average or profit. For example, let's try adding a standard deviation calculation to the sales cube. Standard deviation can be calculated as follows:
Calculate the sum of the square of each sale.
Multiply the result of step 1 times the sales count
Sum all sales.
Square the result of step 3.
Subtract the result of step 4 from the result of step 2.
Multiply the sales count by one less than sales count ("sales_count" * ("sales_count" – 1)).
Divide the result of step 5 by the result of step 6.
Standard deviation will be the square root of step 7.
Sounds like fun, right? Indeed it's not simple, but it can be done. First, let's make sure that we have all the needed measures. Perhaps one of the challenges is to realize that sum of the squares is not the same as the square of the sum. In order to calculate the sum of the store sales squares, we need to add a measure store_sales_squared to the cube. The easiest way to accomplish that is to open the MS Access database containing Foodmart and add a column called store_sales_squared to the sales_fact_1997 table.
NOTE
you have to close the Cube Editor before you can modify the FoodMart2000 Access database. Foodmart2000 data resides in a Microsoft Access database called FoodMart2000.mdb found in the Analysis Services installation directory in the Samples folder.
Next, you can run a query within Access:
UPDATE sales_fact_1997 SET store_sales_squared = store_sales * store_sales;
Next, you need to add a measure to the Sales cube; let's close the Access database and go back to the Cube Editor. Right-click Measures within the Cube Editor and choose New Measure to get the following dialog box:
)
Figure
9Here, choose the store_sales_squared column and click OK. Now you have all necessary measures: store sales, store sales squared and sales count. The standard deviation calculation will look as follows:
((([Measures].[sales count]*[Measures].[store sales squared]) - ([Measures].[store sales]*[measures].[store sales]))/ ([Measures].[sales count]*([Measures].[sales count]-1) )) ^ (.5)
To add a calculated member, right-click Calculated Members within the Cube Editor and choose New Calculated Member. Paste the standard deviation calculation within the Value expression box, as shown next:
)
Figure
10After you process the cube, the standard deviation and store sales squared will be available within your MDX queries. For example, the following query returns sales standard deviation, sales average, and sales squared for various store types within 1997:
SELECT {[store type].members} ON ROWS,
{measures.[store sales squared], measures.[standard_deviation], measures.[sales average] } ON COLUMNS
FROM sales
WHERE ([time].[year].[1997])
Results are shown as follows.
)
Figure
11Calculated members can thus be used effectively to extend the existing measures and perform calculations within the cubes. Calculated members can also be used to expose member properties as measures.