Cube Calculated Member Question

Question

I'm rather new to SSAS and am completely stumped on how to solve this problem. I have a dimension called Thresholds. Within the Thresholds dimension there are the following Members:

[Threshold Year], [Threshold1 Amount], [Threshold2 Amount], [Threshold3 Amount]

I also have a measures called [Qualifying Commission], [Tier1 Amount], [Tier2 Amount], [Tier3 Amount]

On top of that there is also a time dimension called [Statement Dates]

So basically here is what I am trying to do. I want to create a calculated member that when an end user selects a time frame from [Statement Dates] I want to sum up [Qualifying Commission] and check it against the threshold amounts for the corresponding year. If it is greater than [Threshold1 Amount], I want it to return the [Tier1 Amount] measure with the same logic applying to thresholds 2 and 3.

Can somebody give me a road map on what I need to do? If I need to restructure the data in the underlying database to make this work then that would be really helpful to know. Thanks in advance for the help.

Answer 1

Well, let's assume that [Qualifying Commission] is an additive measure, meaning the sum is taken care of for us by SSAS.

Essentially, we just need to find whatever year we're in under [Statement Dates] to get our thresholds.

So, let's break it into some chunks. The first thing we do is to find our right [Threshold Year]:

create set [Current Threshold] as
    iif([Statement Dates].[YQM].CurrentMember IS [All]
        ,[Threshold Year].[All]
        ,StrToSet("[Threshold Year].[" + 
            Ancestor([Statement Dates].[YQM].CurrentMember
            , [Statement Dates].[YQM].[Year]).Name +  
        "]"))

Next, we'll apply this threshold to get the right number:

create member currentcube.[Measures].[Threshold Amount] as
    case
        when [Qualifying Commission] > 
                 [Current Threshold].Item(0).Properties("Threshold1 Amount") then
            [Measures].[Tier1 Amount]
        when [Qualifying Commission] > 
                 [Current Threshold].Item(0).Properties("Threshold2 Amount") then
            [Measures].[Tier2 Amount]
        when [Qualifying Commission] > 
                 [Current Threshold].Item(0).Properties("Threshold3 Amount") then
            [Measures].[Tier3 Amount]
        else 0
    end

And voila, you're cooking with gas.

Answer 2

You should also look into using the scope operator. It can help performance when you need to implement conditional sums.

Scope operator From MSDN