Forum Discussion

lszymk97's avatar
lszymk97
Frequent Visitor
1 year ago

Inherited complex model with every relationship being many to many to sort out

 

 

Hey everyone,

 

I've inherited a very complex data model with numerous many-to-many relationships (it looks similar to the image attached). The person who originally built it is no longer around, and my task is to convert this into a more manageable tabular model. I’m aware that most of these many-to-many relationships aren’t ideal, but I’m unsure where to begin fixing them.

 

I’m also struggling to identify which tables are fact tables and which are dimension tables. I suspect there are multiple fact tables with varying levels of granularity, possibly covering different time periods.

 

Should I start by identifying the fact and dimension tables? The data comes mostly from SQL, with tables merged on SQL keys. However, some relationships have been created directly within Power BI using custom keys, which has resulted in many-to-many relationships.

 

How should I approach this?

 

 

 

Need Help

2 Replies

  • ajohnso2's avatar
    ajohnso2
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    I dont envy you 😕

     

    I would start by working out what tables you have and generally what they are doing. Then try to come up with a simple star schema.

     

    It may just be easier to burn it with fire and start over haha

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, lszymk97 

    I am glad to help you.

     

    First you need to identify the fact table and the dimension table. Fact tables are typically tables that contain transactional data, such as sales, orders, or events. These tables typically contain numeric metrics and foreign keys linked to dimension tables. Dimension tables are typically tables that contain descriptive attributes associated with an entity, such as customer, product, or time. They are usually smaller and contain text or categorized data.

     

    Then you need to modify the many-to-many relationships between the tables. You can create bridge or join tables to handle many-to-many relationships. These tables link the primary keys of related tables, converting a many-to-many relationship into a one-to-many relationship. Where possible, you can also convert a many-to-many relationship into a one-to-many relationship by aggregating or splitting data.

     

    Finally you also need to ensure that the relationships defined in SQL are correctly reflected in your model. Remove or adjust custom keys in Power BI that result in a many-to-many relationship. If you have multiple fact tables with different granularity, make sure that they are correctly linked to the appropriate dimension tables.

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.