SSAS: AverageOfChildern and Semi-additive measure requires a time dimension

In SSAS (SQL Server Analysis Services), if you have a measure group, with some measures as AggregateFunction of Sum and some of AverageOfChildern, you will see a red squiggly line on the measure group and if you highlight it (or build the cube), you will get this error:

Semi-additive measure requires a time dimension.

The problem starts with the AggregateFunction property of your measures, where some are Additive, some are Semi-Additive. You can see which are which by looking at MSDN here. What I found will get rid of the error, but still might not help you is this: if you go to the properties of your Time dimension, and check its type. By default dimensions are created with type “Regular”. If you change your Time dim to type “Time”, then rebuild, you won’t get any errors, and you can deploy.

Now, this might be all good, but in the case I ran into, it still didn’t work for me. AverageOfChildern only applies to the Time dim. Not what you would expect from looking at it. Say you have a measure, that you want the Average (AVG) instead of Sum, you would think that picking AverageOfChildren as a AggregateFunction property would do the trick, well it doesn’t. If you do this, deploy, and browse your cube, you will still see that the values are Sum. WTF right? Well like it sparsely says in the docs, it only applies to the Time dim.

In order to get the Average of a measure, here is the best way to go about it that I have found.

1) Create a Measure from the field for Sum
2) Create a Measure from the field for Count
3) Create a Calculated Member (Measure) for the field, taking Sum/Count from step 1 and 2.
3a) you can check for 0’s in the count by using IIF()
3b) for best performance, check your NON_EMPTY_BEHAVIOR and if it applies to you, it should speed up your results tremendously. Usually you can just choose the fields you are using in your calculation and it should be fine from what I have seen (I am no expert though)
4) make sure to hide your Sum and Count measures if you don’t want your clients to use them by setting the Visible property to false.
5) Build, Deploy, Process, Report

 

Advertisements

One thought on “SSAS: AverageOfChildern and Semi-additive measure requires a time dimension”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s