Search code examples
ssasbusiness-intelligence

Assign value to user hierarchy

Let's have a dimension "Category" defined with the following table :

ID | CategoryLevel1 | CategoryLevel2 | CategoryLevel3
---|----------------|----------------|---------------
 1 | C1             | C11            | C111
 2 | C1             | C11            | C112
 3 | C1             | C11            | C113
 4 | C1             | C12            | C121
 5 | C1             | C12            | C122
 6 | C1             | C12            | C123
 7 | C2             | C21            | C211
...| ...            | ...            | ...

and a fact defined with the table :

CategoryID | Value
-----------|---------
 1         | v1      
 2         | v2      
 3         | v3      
 4         | v4      
 5         | v5      
...        | ...

In SSAS we defined a user-hierarchy as :

CategoryLevel3 --> CategoryLevel2 --> CategoryLevel1

We notice that CategoryLevel3 determines the granularity of the fact table.

Suppose the fact table shouldn't be rolled up by summing (for example : number of customers for category at level 3) but instead, we have to set data for higher levels in hierarchy (CategoryLevel2 and CategoryLevel1).

Is it possible to do that in SSAS with a minimum of change in dimension table (because it's used with many other fact table).

Thanks,

Solution

  • It is possible to have multiple measure groups loaded at different granularity level of dimensions for the same type of facts. Then by using scope statements, you can retrieve data from corresponding measure groups.