Modelling issue for measures

Nishith Jain

Hi,

I am trying to work through a modelling scenario and I am not sure what the best way forward is. I have a logical fact F1 with a measure M1 with a default aggregation of Sum, a logical dimension D1 that joins to F1 and a snowflake dimension D2 that joins to D1. D1:D2 is a 1:many relationship. In my analysis, if I select a column from D1 and F1.M1, I get a certain amount, lets say $100. When I add a filter from D2 (not selecting any columns from D2). The F1.M1 amount multiplies by the number of rows that get pulled form D2. This is expected as the query generated joins all the 3 tables together and the SUM(F1.M1) with be the sum of amount for all the rows that come back in the dataset. Is there a way to prevent this multiplication of the amount using modelling techniques? If I were writing sql by hand, then I would write a select distinct key, F1.M1 from F1, D1, D2 first and then aggregate the resulting F1.M1. I am not sure how to model this so the RPD produces a similar sql.

The only solution I have found so far which is a bit undesirable for me is to treat D2 as a seperate fact table. D2 would become two logical objects F2 (with a dummy measure) and D2 with the attributes. D1 would be a conformed dimension between F1 and F2 and D2 would be a non-conformed dimension joined to F2. This generates a seperate sql for f1 and f2 and then stitches the results together. If I go this route, then I have to level all measures in F2 to D2 and also my users are forced to always pull in the dummy measure from F2 whenever they want to use a column form D2 otherwise the report throws a leveling error.

Any help on this topic will be greatly appreciated as it will set the tone for modeling a lot of our other content.

Best,

Nick    

Sherry George

May be this helps weighting factor in bridge table

Nishith Jain

Thanks Sherry. This does help for some other scenarios I was looking at.

asim cholas

Hello,

What is the use of joining as such? anyway data doesnt exist at the D1 level.? Provide some sample data

Thomas Dodds

Your issue is because you've logically snowflaked ... take your physical snowflake and reduce it so you don't have the logical snowflake (fold it into the D1 logical dimension - as an LTS)