You design a SQL Server 2008 Analysis Services (SSAS) solution. Your solution has a measure named Sales Amount and a dimension named Date. The Date dimension has a hierarchy named Fiscal that has levels named Fiscal Year, Fiscal Quarter, and Fiscal Month.
You need to create a calculated member to analyze the Sales Amount share percentage at different levels compared to the total sales for a given Fiscal Year.
Which code segment should you use?
A.
Case
When [Date].[Fiscal].CurrentMember.Level.Ordinal = 0 Then 1
Else
[Measures].[Sales Amount]/([Measures].[Sales Amount], Ancestor([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Fiscal Year])) End
B.
Case
When [Date].[Fiscal].CurrentMember.Level.Ordinal = 0 Then 1
Else
[Measures].[Sales Amount]/([Measures].[Sales Amount], [Date].[Fiscal].CurrentMember.Parent)
End
C.
Case
When [Date].[Fiscal].CurrentMember.Level.Ordinal = 0 Then 1
Else
[Measures].[Sales Amount]/([Measures].[Sales Amount], Descendants([Date].[Fiscal].CurrentMember,
[Date].[Fiscal].[Fiscal Year], SELF_AND_AFTER))
End
D.
Case
When [Date].[Fiscal].CurrentMember.Level.Ordinal = 0 Then 1
Else
[Measures].[Sales Amount]/([Measures].[Sales Amount], [Date].[Fiscal].[Fiscal Year].CurrentMember)
End
Explanation:
Tip: “different levels compared” = “Ancestor”ANCESTOR MDX Function
A function that returns the ancestor of a specified member at a specified level or at a specified distance from the member.
Level syntax
Ancestor(Member_Expression, Level_Expression)
Member_Expression – A valid Multidimensional Expressions (MDX) expression that returns a member.
Level_Expression – A valid Multidimensional Expressions (MDX) expression that returns a level.