Custom Semi-Additive Member using Analysis Service 2005 Standard Edition

[Edited For Clarity]

I’m using Analysis Service 2005 the standard edition:

I’m trying to create a custom semi-additive measure through MDX. My time dimension has month granularity with years on top of that. Basically, I’m trying to create an average the months while summing the averages across non time dimensions.

My best solution was using the following code:

avg(descendants([time].[hierarchy].currentmember,[time].[hierarchy].[month], self), sum(measures.[measure_to_aggregate]))

the problem with the above code is that it does not include empty months of non empty years. That is, if an attribute exists for a month in 2004, I want to see all the months of 2004 as part of the denominator. However, if an attribute does not exist for any months in 2004 I want to see Null.

I also tried the following code:

avg(descendants([time].[hierarchy].currentmember,[time].[hierarchy].[month], self), sum(measures.[measure_to_aggregate]), includeempty)

but the statement could not be parsed due to too many arguments in the avg function. Can anyone clarify what the problem is here?

Another way of doing this is using the count member. Basically I would count all the periods in the year and use that as a denominator to find the average:

sum([measures].[measure_to_aggregate]) / count (descendants ([time].[hierarchy].currentmember,[time].[hierarchy].[month], self))

The problem with this statement is that I can’t differentiate between empty years and non-empty years, so the code computes the average for all years in the dimension.I also tred using the nonempty function around descendants, but it excludes both months of empty and non-empty years.

Any ideas?

DNA,

You are saying that statement below is OK, and one problem is that it does not include empty cells.

avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(measures.[adjusted headcount]))

Try to use CoalesceEmpty:

avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(CoalesceEmpty(measures.[adjusted headcount],0)))

This will replace NULLs with 0.

Vidas Matelis

Edited: Added ,0

|||

Thanks for replying Vidas Matelis,

I have edited my post to make the previous post much more clear.

The issue with using Coalesce is that I have more years in my dimension than the cube itself.

Therefore I would not want to replace the null values of the addtional years in the dimension with zeros.

|||

Please note that includeempty is an option for Count(), but not for Avg(). How about using Count() with Filter(), like:

[measures].[measure_to_aggregate] / count(Filter(descendants(

[time].[hierarchy].currentmember, [time].[hierarchy].[month]),

Not IsEmpty(([measures].[measure_to_aggregate], [time].[hierarchy].Parent))))

|||

DNA,

I would assume that for years where you have no data, you would get result as 0. So what if you use IIF statement and replace 0 with NULL? Would that work?

IIF(avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(CoalesceEmpty(measures.[adjusted headcount],0))) = 0

, NULL

,avg(descendants([all year].[hierarchy].currentmember,[all year].[hierarchy].[period], self), sum(CoalesceEmpty(measures.[adjusted headcount],0)))

)

Vidas Matelis

|||

Thanks for the replies and ideas. For clarification:

To Deepak Puri,

I tried using filter but it excluded all empty values, meaning the empty values for periods for the years within the cube were exclude as well. The way I wanted to approach this is by excluding all empty values for the years that are not in the cube while including the empty periods for the years that are in the cube.

To: Vidas Matelis,

And an iif statement works very similiar to case statements and coalesceempty. When I used the coalesceempty I had to make a seperate calculated measure to use it because there was an error saying too many arguments in the avg() function. I could certainly try using the code you have provided but I would think that this code would also exclude all empty values instead of selectively excluding values for certain years.

|||

Ultimately, the code will have to know somehow that some years are considered "empty" and some are not. You are the only one who can tell what the criteria is. It could be as simple as hardcoding 2004 as the first non-empty year, or, perhaps you will have more complex criteria, i.e. year in which all months are empty for Root() of everything else, or something else. Let's assume that you created named set with all the months from the years that you consider "non-empty", i.e. something like

CREATE SET NonEmptyMonths AS Exists([Time].[Month].[Month], [Time].[Year].[2004] : NULL )

I.e. here I hardcoded 2004 as the first "non-empty" year. After that you can take any solution proposed above and use

Intersect(NonEmptyMonths, Descendants([Time].[Hierarchy].CurrentMember, [Time].[Hierarchy].Month))

Instead of using Descendants directly. You won't have to filter empty cells anymore, of course, because Intersect will take care of it.

|||

Would there be a way without hardcoding it?

The reason being is that I want it to be automated and to account for changes in the time dimension (ie. additional years and periods being added).

|||Please see my previous message - uou have to tell us the criteria - which years are considered to be "populated". Is it a member property of the Year attribute ? Is it year which has at least one non-empty month at the Root ? Is it something else ? Once we know you business logic we can translate it into MDX, but without knowing it - not much we can do.|||Mosha, sorry for the late reply, the years are considered to be populated if there is at least one non-empty months.|||

OK, then the CREATE SET statement can look like something around the following theme:

CREATE SET NonEmptyMonths AS Descendants( NonEmpty( [Time].[Hierarchy].[Year].MEMBERS ), [Time].[Hierarchy].[Month] )

|||

Something along the following lines:

CREATE SET NonEmptyMonths AS Descendants( NonEmpty([Time].[Hierarchy].[Year]), [Time].[Hierarchy].[Month] )

|||

CREATE SET NonEmptyMonths AS Descendants( NonEmpty([Time].[Hierarchy].[Year]), [Time].[Hierarchy].[Month] )