combine multiple dimensions into one

Question

combine multiple dimensions into one

dk98681

SSC-Addicted

Points: 497
More actions
October 10, 2017 at 2:52 pm

#149376

I have a premium transaction fact table and each record in this fact table is associated with multiple limits. Therefore I have a FactID, FactValue, LimitTyp1ID, LimitType2ID, LimitType3ID etc. in SSAS cube I have created a dimension for each Limit Type that has the same members. Example Limit Type 1 dimension has members 1000, 2000, 3000, 5000. Limit Type 2 also has 1000, 2000, 3000, 5000. and same for Limit Type 3 and so on. Each Limit Type dimension points to the same Dim table in the data source.
When I browse I can browse the sum for Fact Value or unique Fact ID counts that have Limit Type 1 = 1000, and/ or Limit Type 2 = 1000 etc.
Can I create one Limit dimension instead of three as shown above, where I choose which Limit Type I want to look for and then choose 1000 or 2000 limit value. I think it is not a role playing dimension, as I want to combine multiple dimensions into one, not just reuse the same dim table.
If so, please point me to some documentation that shows how to do that.
Thanks in advance.

matak SSCrazy Points: 2674 More actions · Answer 1

If i understand correctly then all you really need to do is create one combined dimension that has all combinations of the current dimensions.
This would all depend on your business rules but you should be able to do something like the below.

Im not quite sure why you want to do this though.

Key    LT1        LT2        LT3
1 1000 1000    1000
2 1000 1000    2000
3 1000 1000    3000

dk98681 SSC-Addicted Points: 497 More actions · Answer 2

matak - Wednesday, October 11, 2017 9:09 PM
If i understand correctly then all you really need to do is create one combined dimension that has all combinations of the current dimensions.
This would all depend on your business rules but you should be able to do something like the below.
Im not quite sure why you want to do this though.
Key    LT1        LT2        LT3
1 1000 1000    1000
2 1000 1000    2000
3 1000 1000    3000

That would be an option if I only had a few (may be 3 or 4 limit types) but it is not practical for 15 limit types.
I am looking for a solution where I can choose the limit type first and then the value to aggregate my facts.

Thanks again.