MDX - Calculated Members
From SQLServerPedia
|
See Also: Main_Page - Database Administration - Analysis Services - MDX with Analysis Services Calculated members allow you to enrich the data presented to your users without increasing the size of your cubes. Unlike regular cube measures that represent columns in your fact tables, calculated members are not aggregated at cube processing time. Nor are they stored in the cube calculated members' values are determined during query execution. Writing MDX for calculated members (and named sets) is somewhat different from writing MDX queries because you don't have to specify SELECT, FROM and WHERE clauses; instead you create a calculation based on the existing measures and dimensions. Yet another difference is that you don't actually see the result set right away you will have to reference your calculated member in an MDX query before you can see the result. Let's examine a few calculations that are commonly used in business applications. With MSAS 2000 you cannot aggregate a measure with the AVG function; if you need to display averages on your analytical reports you must create a calculated member. The simplest average would divide the sum of all members by the member count. For example, you could divide total sales by the number of orders to get an average sale amount as follows: ([measures].[sales amount] / [measures].[order count]) Similarly, you could use the following formula to determine the average salary per employee: [Measures].[Salary]/[Measures].[Number of Employees] Other calculations that you might commonly see as calculated members are measure comparisons for various members in a dimension hierarchy. For example we could compare sales in the current time period to the sales in prior period as follows: ([date].CurrentMember, [measures].[sales amount] - [date].CurrentMember.PrevMember, [measures].[sales amount]) You could measure the growth in customer count using the following formula: Case /*if the current member is the topmost member in time dimension Then we cannot examine the growth because we have nothing to compare the current customer count to. */ When [Date].[Fiscal].CurrentMember.Level.Ordinal = 0 Then "NA" /* similarly, if the previous period didn't have any customers then we shouldn't compare the current customer count to previous period */ When IsEmpty ( ([Date].[Fiscal].CurrentMember.PrevMember, [Measures].[Customer Count] ) ) Then Null Else ( ( [Date].[Fiscal].CurrentMember, [Measures].[Customer Count] ) - ( [Date].[Fiscal].PrevMember, [Measures].[Customer Count] ) ) / ( [Date].[Fiscal].PrevMember,[Measures].[Customer Count] ) End Note that this formula only works in MSAS 2005; MSAS 2000 does not support CASE statements. The same expression in MSAS 2000 would require nested IIF statements, like this: IIF([Date].[Fiscal].CurrentMember.Level.Ordinal = 0, "NA", IIF(ISEMPTY( ([Date].[Fiscal].CurrentMember.PrevMember, [Measures].[Customer Count] ) ), NULL, ( [Date].[Fiscal].CurrentMember, [Measures].[Customer Count] ) - ( [Date].[Fiscal].PrevMember, [Measures].[Customer Count] ) ) / ( [Date].[Fiscal].PrevMember,[Measures].[Customer Count] ) ) Suppose you wanted to get the percentage of sales attributable to the current product compared to the total sales of its parent (product category); you could start with the following: /* divide the sales for the current member by the sales for its parent then multiply by 100 to get percentage */ (([Product].[Product Categories].CurrentMember, [Measures].[Sales Amount]) / ([Product].[Product Categories].CurrentMember.Parent, [Measures].[Sales Amount]) )* 100 However, this formula does not work if the current member of product dimension is "all products" because it has no parent. You could enhance the formula as follows to account for the topmost member of parent dimension: Case When [Product].[Product Categories].CurrentMember.Level.Ordinal = 0 Then 1 Else ([Product].[Product Categories].CurrentMember, [Measures].[Sales Amount]) / ([Product].[Product Categories].CurrentMember.Parent,[Measures].[Sales Amount] ) End With MSAS 2000 you could use the LookupCube function to reference a measure in a different cube. Since MSAS 2005 allows multiple fact tables per cube you should not have to use the LookupCube function any longer. For example, the following query looks up the count of active employees for the current facility by querying the "HR" cube: LookupCube( "HR", "(" + MemberToStr([Facility].CurrentMember) + "," + MemberToStr([status].[active]) + ", [measures].[employee count])")
|