Dimesion Design question using Distinct Count Calculated Member 1

[dhaney]

I have a dim design and data as follows.

Dim

Group_1
Tier_1
Child_1
Child_22
Tier_2
Child_1
Child_45


What I need is to be able to do a distinct count at the Teir or Group level of the Child level members. I am currently using
a Calculated Member with the formula as follows:

DistinctCount({[Dim].[Tier].Members}).


This works fine when getting the distinct count at the Tier level. But when getting the distinct count
at the Group level I double count Child_1. It does a distinct count at each tier then aggregates.
I really need to only count Child_1 one time.

Any suggestion on design of dim or use of Calculated Members / Cells would be much appreciated.

Thanks,

JD

 

[MDXer]

The key to working with distinct anything in Analysis service is good dimensional design the maintains Uniqueness at as many levels possible in both name and Keys.

Dim

Group_1
    Tier_1
        Child_1
        Child_22
    Tier_2   
        Child_1
        Child_45

for the above I would concatinate the Teir Name and Child name to give you Distinct Names, hopefully the keys are already distinct. The result would look soemthing similiar to the following.

Dim

Group_1
    Tier_1
        Child_1 - Tier_1
        Child_22 - Tier_1
    Tier_2   
        Child_1 - Tier_1
        Child_45 - Tier_1

THis doesn't so much affect the results of any measures unless the keys aren't distinct as it makes the dimension more user friendly when a front end tool is being used, especially if the reporting tool doesn't display the heirarchy of dimension elements.

The key to getting distinct values is going to be in your cube design. If you dimension keys are unique you can create a cube with a singl measure from your fact table based upon you Dimension key. THe easiest way to do this is to:

1) Right Click on the Cube that needs the distinct measure.
2) Select Copy
3) Paste this cube back into you Database
4) You will be asked for a new name, I generally use the same name only append "Dist" and the Measure that is distinct. Example in your case would be "MyCube Dist Children"
5) Open the new cube in the cube editor and delete all measures that currently exist.
6) Create a New measure from the column you want the distinct count from and ue the distinct count Aggregation.
7) Save this cube. You now have 2 cubes with the same structure except for the measures contained.
8) Create a New virtual cube
9) Import the 2 cubes, all dimensions, and all measures.
10) Save and process the virtal.

You can import any Calculated measures into the virtual as well. This technique all allows you have multiple Distinct Count measures. There are other low level performance issues that come from having a structure like this, the most obvious in this case would be that your MDX is calculated on the fly placing it into a cube structured like this you could do a high degree of aggregation which will improve query times, making any Calculations based off this value perform better as well.

Hope this helps.


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck