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.

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.

default values for database fields yes or no??

Building the database I have come across different databases some that add a default value for every field and some that don't. I feel it is a hassle to add a default value, keep track if it is added.

I guess with a default value there would be no "NULL" values in the database but one could also make sure in the C# code that all the fields have a value when inputed and on the way out check for nulls.

What is the right way??

Pros and cons.......

Newbie

Like a table that holds car information. If you have a field that holds the number of wheels, I would make that a default of 4.

Sort of like, if you don't mention how many wheels the car has, I'm going to assume 4. Now if the car has more/less than 4, you can tell me about it, and I'll remember.

It's not really what is "right" and what is "wrong". You can also say there is no default, and you don't tell me the number of wheels of every car, I'm not going to accept it. It forces you to make a choice for every record. Or you can have each record allow nulls, in which case if you don't mention it, we'll still accept it, but we won't make any assumption on the number of wheels.

My rule of thumb is, if it's necessary field, then no default value, and does not accept nulls.

If you can assume a value if one isn't specified (It rarely isn't a particular value), then I'll make it does not accept nulls, with a default value.

If I really don't care about the field at all, and it's a fluff field that I won't use, then I'll accept nulls and no default.