Defining Non-Summable Measures in Cube

Hi all. I am new to SSAS 2005 and MDX in general. My team is building a data warehouse and thus far have the ETL (SSIS) part all done and am now moving on to the SSAS portion of the project. We have a cube built however, when browsing it via pivot tables, we noticed that the measures that are not additive, for example, "Average Log Length", show up as essentially sums over a given time period.

At first we thought that we needed to define the AggregateFunction for the specific measure to be AverageOfChildren. But that didn't work. The source data all has values of say 14.2, yet the average for a given day comes out to 2034.43! Clearly not an average.

This makes these non-additive measures completely useless in the cube.

Can anyone tell me what I am doing wrong? And how to correct it?

Any help would be much appreciated.

Thanks!

Jeff Ptak

Is the Type property of the time dimension specified as Time? The AggregateFunction semi-additive measure specification only applies to the Time dimension in your cube and will not work at all if no dimension is specified as a Time type.|||

This has come up before and the following is taken from this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=548837&SiteID=1

the AverageOfChildren is semi-additive, ie. it acts like a "sum" across all dimensions except time. For example, in Adventure Works if you add an AverageOfChildren measure on the OrderQuantity field of FactSalesSummary, its value matches that of [Measures].[Order Quantity] at the leaf Date level. But for July 2001 as a whole, [Measures].[Order Quantity] = 966, whereas the AverageOfChildren measure = 31 (which is 966 divided over 31 days of July).

Have you tried creating standard sum and count measures and then dividing the sum by the count? This should do the trick and you can hide the sum and count if they are not otherwise needed.

|||Yes. Set as Time.|||I have already read that post. I think I have it setup correctly, but the AverageOfChildren doesn't do what I want despite browsing by my Date dimension (which is configured as the "Time" type).|||

No, as that post explains, AverageOfChildren does not average across all dimensions, it sums across all other dimensions and only averages across the time dimension. It is not really well named, in fact in some of the wizards now it comes up as "Average over time".

Calculating a standard average of the values in the fact table can be done in a couple of easy steps.

1. Create a measure using a SUM aggregation

2. Create a second measure using a COUNT aggregation

3. Create a calculated measure that is (1) / (2)

|||

One last question. Are you deploying to Enterprise edition? Semi-additive measures are only supported in Enterprise.

http://www.microsoft.com/sql/editions/enterprise/comparison.mspx

|||

Yes. Deploying to Enterprise edition.

I'll try the sum/count thing. From what I have read, that seems to be common work-around to this problem.

Thanks!

|||It's not really a work-around, the averageOfChildren is meant to solve a different, more difficult class of calculation.|||

I figured this out. Simply set up a calculation for each non-summable measure that is:

Measure / Count

I made sure the AggregateFunction for the measure is set to SUM and I used the Count measure that the cube wizard inserted into my measure group (which has its AggregateFunction set to COUNT).

I would have thought that there was an easier way. I now have over 600 calculations in my my cube! And that's only for one process area of my facility.

Thanks for all your help.