SQL Server 2008 | SQL Server 2008R2 | SQL Server 2012

A few years ago, Alex Whittles wrote a very detailed explanation about semi additive measures in a SQL Server Standard Edition. For my post today I’m concentrating on some other ideas, especially on dynamic sets in SQL Server 2008. Semi additive measures are measures that have a different way of aggregation over time (or other specific dimensions). For example, thinking of stock values you want to sum them up by product, location, country etc. but not by time (here you may want to see the last value, the average, the minimum/maximum or whatever).

For SQL Server SSAS (multidimensional) semi additive measures are a feature that is only available in the Enterprise Edition of the server. The following post shows methods how to implement such calculations on a Standard Edition. However, the implementation in the Enterprise Edition performs better than the custom calculation shown here.

For my example, I’m using pretty much the same source data as for my DAX post about semi additive measures.

This is how the sample data looks like when using the measure StockLevel:

The time dimension is at day granularity. The stock values are associated with the last day of the month (in order to properly show the effect of the ‘Last Value’ semi additive function).

Since we use Sum as the aggregation function here, the grand total for each row is correct while the sub totals and and the grand total for the columns make no sense.

This is a job for semi additive measures! In order to see different effects, I created three of them named

- EP Average
- EP LastValue
- EP LastNonEmpty

The prefix EP stands for Enterprise Edition. For example, this is the definition of “EP LastNonEmpty”:

The following screen shot shows all of those measures side by side (for all products):

The difference between ‘last value’ and ‘last non empty’ is about the total. Last non empty really takes the last non empty value *within the current context* – not within the full date dimension. So for the measure “EP LastNonEmpty” we see a total for 2011 but no total for April (as there is no value for April at all). The measure “EP LastValue” on the other hand gives no total for 2011 as there is no value for December 31, 2011.

Now let’s try to rebuild those measures.

__Average over time (version 1)__

First we start with the average over time. If you have (or can create) a distinct count measure for the date key of the fact table it is quite easy to compute the average over time. Let’s assume our distinct count measure is named [Date Key Distinct Count], then the calculation for the average looks like this:

CREATE MEMBER CURRENTCUBE.[Measures].[STD Average]

AS Measures.StockLevel / [Measures].[Date Key Distinct Count];

We can check the results be putting both measures side by side:

Of course, the calculation also works if you filter for a specific product.

__Average over time (version 2)__

I just wanted to present a different approach in case you do not want to create a distinct measure (separate measure group). We still need to find out the number of days in a given date range. We can use a dynamic set in order to do so:

CREATE DYNAMIC SET CURRENTCUBE.[CurrentDateRange]

AS [Date].[Day].[Day], DISPLAY_FOLDER = 'Calculations' ;

CREATE MEMBER CURRENTCUBE.[Measures].[STD Average 2]

AS [Measures].[Stocklevel]

/count(nonempty(existing CurrentDateRange, [Measures].[Stocklevel])) ;

The set needs to be dynamic in order to reflect a sub cube context (for example if Microsoft Excel 2007 and later is used to query the cube). Also, we have to filter out the dates with values as the average needs to count only existing date rows.

Comparing the three measures (Enterprise method and the two methods for the average) side by side shows that they are the same:

__Last value__

For the last value, I’m also using the approach with the dynamic set here. We use the same set, so the calculation is simply:

CREATE MEMBER CURRENTCUBE.[Measures].[STD LastValue]

AS ([Measures].[Stocklevel], tail(existing CurrentDateRange,1).item(0)) ;

The expression tail (…, 1) gives the last item of the set. We evaluate our measure on this last item.

Before comparing the results with the enterprise measure, let’s quickly add the last one:

__Last Non Empty__

This formula is almost identical to the one I used before, except that we wrap the set in a nonempty(…) function. Again we take the last item (in this case this is the last item with data), so the function looks like this:

CREATE MEMBER CURRENTCUBE.[Measures].[STD LastNonEmpty]

AS ([Measures].[Stocklevel],

tail(nonempty(existing CurrentDateRange, [Measures].[Stocklevel]),1).item(0)) ;

Here are our two last measures together with their corresponding Enterprise measures side by side:

Of course you can use the calculation above also for other kind of aggregates. For example a minimum over time (sum over all other dimensions) would look like this:

CREATE MEMBER CURRENTCUBE.[Measures].[STD Min]

AS Min(existing CurrentDateRange, [Measures].[Stocklevel]);

And you can also use this kind of calculation for dimensions other than the time dimension.

Thank you very much for this post! I am curious if could give some examples on how to deal with Averages over other then time dimensions? Average of Children, for example?

ReplyDeleteActually the formulas above don't use any of the MDX time functions or rely on the dimension being a time dimension (for example DateKey is just a normal column here with no special meaning for SSAS). You can do just the same (for example distinct counts, existing member sets, min, max etc.) with any other dimension. Calculating the average of children in a hierarchy could be done in the frontend (for example in Excel), but also using a calculation in SSAS using a calculation which is similar to Avg(Products.ProductCategories.currentmember.children, Measures.[Internet Sales Amount]). Hope this helps.

ReplyDeleteThank you very much

ReplyDeletei am using the 2014 Standard edition where i can put expression CREATE MEMBER CURRENTCUBE in the SQL Server Data Tools for SQL Server 2013

ReplyDeleteThe create member statement refers to the cube script which you find on the "Calculations" tab of your SSAS project. In this tab switch to script view (not form view) and add the code below the calculate statement. By using create member the calculation becomes part of the cube which makes it available to all users (assuming they have the proper rights).

ReplyDelete