Sql-server – SSAS OLAP Semi-Additive Measure over Time Dimension

olapsql serverssas

Issue:

Help, I'm relatively new to building OLAP cubes in Microsoft's SQL Server Analysis Services and I need help with a semi-additive measure over a time dimension.

In other words, I want to show the ending balance as a non-additive measure at quarterly and yearly aggregation points.

Facts:

I have a measure called Ending Balance which is the sum of all the accounts ending balances.

Dimensions:

I have a date dimension called As of Date. This dimension can be aggregated by year, quarter, and show monthly data points.

Desired Outcome:

I am using SQL Server Enterprise edition.

I've made sure that the Time dimension is properly specified with the correct types and attribute relationships. Reference link

Best Answer

Solution

The easiest way to accomplish the above is to set the CustomRollupColumn in your date dimension for both your Quarter and Year dimensions to the following equation:

'TAIL(NonEmptyCrossJoin(
    DESCENDANTS([Date].[Date Hierarchy].CurrentMember,1),1)
).item(0)'

Where:

[Date] = Dimension Name
[Date Hierarchy] = Your custom date hierarchy dimension

Important Considerations

You cannot manually set the CustomRollupColumn equation in Visual Studio editor. This field needs to provided by a column in your dimension dataset. While this seems a little strange, it actually provides you with a ton of flexibility.

To do this, create a custom column in your DSV (or include it in your dimension table). Here is an example:

Finally, once you've completed this step navigate to your date dimension and set your CustomRollupColumn parameter equal to this newly created field. Here is an example:

Make sure you do this for both your Quarter and Yearly dimension.

Final notes

I still need to conduct some tests, but if everything works I will come back and mark this as answered. So far, everything is working great. Partially completed quarters will reference the most recent provided month (data point). Same thing with the year dimensions.

Resources that were helpful in figuring this out:

Related Solutions

SSAS Modelling – many to many with one dimension

I would normalize the fact table into this form:

StudentId Level    Subject
34567     Major    Math  
34567     Minor 1  French
34567     Minor 2  Biology
etc.

and add the level as a dimension.

Calculated Measure to get only most current from one dimension on snapshot fact but keep other filters

You can try the following calculation:

Openings:=
CALCULATE( SUM( 'Requisition Counts'[NumberofOpeningsQT] )
         , CALCULATETABLE( LASTNONBLANK( 'Requisition Counts'[SnapshotDateKEY] , 1 )
                         , ALL( 'Requisition Status' )
                         )
         )

The important part is in setting the correct filter context for the outer CALCULATE.

With CALCULATETABLE, we define the specific rows which we want the outer CALCULATE to consider: the last non-blank snapshot date for all requisitions (or for a specific requisition, considering the context of each requisition on a pivot table), regardless of any filters in the requisition status, which is achieved using the ALL function.

The measure doesn't work exactly as you want; you say that you would expect the total for Approved to be 2. Using the 2013-11059 requisition from the Power Pivot model as an example, since there is 1 Approved status in 2013-07-01 and another in 2013-07-02, it just considers the last status for the total, resulting in 1.