6

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:

Desired outcome of ending balance shown as non-additive measure along quarterly and yearly aggregation points.

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

  • Are you using a standard Date dimension here? You may want to create a Fiscal Hierarchy which defines the months that fall into your Fiscal Quarter, etc. Performing calculations using the Fiscal Hierarchy as the time field should help to yield the results your looking for. A great rundown of this entire process has already been written up by Dennes Torres and can be found here. – John Eisbrener Jul 19 '17 at 13:39
  • @JohnEisbrener Thanks for the response! I've read both articles linked and have followed the instructions by Dennes Torres. Unfortunately, I'm still unable to produce the desired results. I'm also having a hard time conceptualizing how to use the Fiscal Hierarchy to accomplish what I want. An example would go a really long way. Thanks again! – Christopher Aliotta Jul 19 '17 at 16:51
3

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:

Showing how to create the custom dimension equation

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:

Setting the CustomRollupColumn property for a Dimension Attribute

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.