Average Aggregation in Analysis Services

In SSAS we do not have a measure Average aggregation type. We do have AverageOfChildren (or Average over time), however it is semi-additive and works only along a Time dimension. Fortunately, we have Sum and Count, and since Average = Sum / Count, we can build our own Average aggregation when we need one. 

To do that: 

  1. Create a measure using the Sum aggregation type (which is also the default). In our example, let’s call it Amount.
  2. Create a Count of Non-Empty Values (or Count of Rows) measure. In example – [Measure Count].
  3. Create the actual calculation – [Measures].[Amount]/[Measures].[Measure Count]

We can either create a calculated measure, which performs the MDX calculation above: 

CREATE MEMBER CURRENTCUBE.[Measures].[Average Amount]
[Measures].[Amount]/[Measures].[Measure Count]
,NON_EMPTY_BEHAVIOR = {[Measures].[Measure Count]}

, or if we really do not need the Sum base measure, we can set it to be replaced by the calculation with a SCOPE statement: 

  This = [Measures].[Amount]/[Measures].[Measure Count];
NON_EMPTY_BEHAVIOR(This) = [Measures].[Measure Count];

Voila! We have created a measure simulating an Average aggregation type. Then, we can hide the Count helper measure and from user point of view there is no evidence of our effort. 

Since the count will never be 0, we do not have to say “If not 0, divide, else – do not” and the NON_EMPTY_BEHAVIOR query hint may in fact improve performance, since the calculation will not be performed when the Count measure is NULL (instead of resulting in NULL/NULL=NULL). 

Mosha has previously blogged about NON_EMPTY_BEHAVIOR and division by zero and I strongly recommend reading his post. 

Another important consideration, which depends on the business scenario is the type of the Count aggregate. It could be Count of rows (Row Bound) or Count of non-empty values (Column Bound). The difference is whether we want to include or exclude the empty values from our aggregate. Either way, the described technique will work equally well. 

I realise that this is a well-known approach, but since it is hard to find the solution online I thought it may be interesting for some less-experienced developers.

Reporting Services 2008 Layout Properties Glitch

Today while working with a basic sample dashboard in SSRS I noticed that for some reason one of my subreports disappeared: 

While it got rendered on its own in Report Manager, when I placed it within a subreport (with no other containers around it) it just refused to show up. One of the properties I tried was the Layout menu item on the subreport:

To my surprise, selecting the “Bring To Front” option solved my problem: 


This one was also hard to understand since it rendered correctly in BIDS, leading me to think that there is something wrong with my browser or report server setup. It seems like report items can be sent behind the report background, which at a first glance seems like a confusing and redundant bit of functionality. I also unsucessfully tried to reproduce this in SQL 2005, so it must be a new “feature”. Also, it happened without any action on my part – I did not edit any layout options prior to its disappearance. 

EDIT (15/01/2010)
The issue appears only with a subreport containing a Map. Interestingly, the Map actions – I have a Go To URL on the states still work, but the actual image gets lost somehow. 

EDIT (21/01/2010)
Microsoft replied with the following: 

Posted by Microsoft on 20/01/2010 at 11:25 AM
Thank you for reporting this issue. It only happens if the Map is the first report item on the Subreport and there are no Data Sets specified. You can work around this problem by placing the Map into a Rectangle report item.

OLAP Browser Feature on Connect

As there has been a considerable stir around a few blogs (including mine) lately in regards to the lack of a good OLAP browser/client in the Microsoft BI space, I just created a feature suggestion on Microsoft Connect. Please support me with some more ideas about how Microsoft can fill this apparent gap in the current BI stack. The link is here:


Other posts about this issue: