How to design Multiple related Dimension tables and Factless Facts

[Anonymous]

Hello Experts,

I am strugling for some days to design my model the right way following Star/Snowflake schema priniciples. See below image. I have been able to get my results by creating a calculated measure for each of the required highlighted fields (be it in dimension or fact) but I am not confident if this is the right Data Model design in the first place?

Read too many articles already but somehow not able to fathom what is this scenario that I am dealing with! - Snowflake? Many-to-Many dimension? Factless fact? I want to know what is it and what is the right way to design the model and relationships in this case.

Please see the sample illustrative data model diagram below. Can't post the original model image due to privacy reasons.

Looking for some robust funadamental guidance from data modeling experts.

Problem! -- Result table does not return anything when "columns" are added from multiple "many" side tables (Laptop ID from L1 and other colums from L2, L4, F1) and without any summarizable "measure" added to table. Solved this problem by creating corrsponding "calculated measures" for each of the required columns and adding them to result table (like CalculatedMeasure_Vendor). Is this right approach? Perhaps it is...not sure.

Note: I did enable bi-directional relationships between Tables L2-L3 and I2-I3 for my usage.

Thanks

Nirmit

[Anonymous]

Hi @v-yalanwu-msft 

Thanks much for working out the model and for your response. Problem with connecting L2-L4 is that the L2 may not always have all the laptop ids as not all laptops may have been tagged yet. L1 is the master list of laptop, hence L1 needs to be in center of all supplementary laptop tables.

Also I notice you have enabled bi-directional relationships for all, is that okay? Is it because these are all still dimension to dimension relationships? Normally I understand that it is advised to be not enabled unless crucial.

Also connecting L2-L4 introduced many-to-many join.

Lastly, any word on what is the scenario here - Snowflake? Many-to-Many dimension? Factless fact?

Looking forward to your advice and thoughts.

Thanks

Nirmit

[v-yalanwu-msft]

Hi, @Anonymous ;

In your descripition ,you could change the relationship about L2 and L4.

The final output is shown below:

You can merge some tables in Power Query or append to make the model simpler. If necessary, use measure to achieve the desired effect.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.