SSAS OLAP Semi-Additive Measure over Time Dimension

Question

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

score 3 · Accepted Answer · edited Jun 15 '20 at 09:05

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: