Semi Additive Measures in SQL Server Standard Edition

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:

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:

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:

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:

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:

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