MDX Sum distinct across specific dimensions

Clayton DuVall

I have a measure group that has a few measures that are not addittive across all dimensions. I need to be able to get the sum distinct across specific dimensions and am having problems getting it to work. I tried specifying that the aggregate function should be semiaddittive, but it complains that it is missing a time dimension. I have tried many variations and combinations of MDX functions including sum, aggregate, crossjoin, filter, distinct, etc. but am struggling with the right combination.

Here is a simplified example of what I am trying to do:

GroupID Title Role ConditionCount

11682 Director Operations Licensing Manager 2
11683 Director Operations Licensing Manager 3
11683 Director Operations Product Manager 3

When I group on role I need to see this:

Licensing Manager 5

Production Manager 3

When I group on Title, I need to see this:

Director Operations 5

Basically I want to do a sum distinct for the GroupID. I only want to add the ConditionCount once for each distinct GroupID since the value will be the same for all instances of an individual groupID. Is there a way to do this in MDX

Thanks in advance for your suggestions!

Clayton

Deepak Puri

Could you create another fact table and measure group for ConditionCount For example, if the original fact table has the fields above, a named query could be created like:

select GroupID, Avg(ConditionCount) as ConditionCount from OriginalFact group by GroupID

Then, a "sum" [Measure].[ConditionCount] measure created on this measure group, which only relates to the Group dimension, would work as above.

Clayton DuVall

I don't quite understand your suggestion. I do have a table where GroupID is the primary key, but I can't figure out how to get it to count values once for each distinct iteration of Group ID. The rollups and sum distincts across specific dimensions works with Oracle analytic functions, but I can't figure out how to get it to work with MDX. Thanks for your response.

Deepak Puri

Here's a more detailed description of the suggested model, which uses many-many dimensions:

Suppose this is the "TitleRole" fact table and measure group, with dimensions Group, Title and Role:

GroupID Title Role ConditionCount

11682 Director Operations Licensing Manager 2
11683 Director Operations Licensing Manager 3
11683 Director Operations Product Manager 3

There is a 2nd "Group" fact table and measure group (as suggested above), with [ConditionCount] "sum" measure - this could be the Group dimension table:

GroupID ConditionCount

11682 2
11683 3

This measure group relates directly to the "Group" dimension, but has a many-many relation with the Title and Role dimensions, via the intermediate "TitleRole" measure group. Now, if the Title: "Director Operations" is selected, [Measures].[ConditionCount] in the "Group" measure group should be 5. And if the Role: "Product Manager" is selected, [Measures].[ConditionCount] in the "Group" measure group should be 3.

Clayton DuVall

Ah. I understand now. Let me give that I try when I get back to it in a few days and I'll respond. I already have that table set up in the data warehouse, but didn't think about trying a many-to-many relationship. Thanks again!