Forum Discussion

selected_'s avatar
selected_
Helper IV
11 months ago
Solved

Data modelling with snowflake schema challenge

I am working with data fetched from an Azure database that has a complex table structure resembling a snowflake schema. Since the tables lacked primary keys, I created surrogate keys for the dimension tables. There were seven tables in total.

I identified the fact table and concluded that only two of the dimension tables could be connected directly to it; the others appear to be sub-dimensions.

The fact table is named Sales. The two main dimension tables are Projects and Clients, both of which I connected to the Sales table.

The sub-dimensions are Employees and CollClients, which I connected to the Clients dimension, and JobTypes and GroupProjects, which I connected to the Projects dimension.

So far, I have identified an issue with my current model: there is no direct relationship between Employees and Projects; the relationship only goes through Clients and that seems to show right data. Consequently, when I try to visualize with a matrix and drag column from the Projects table together with an employee name from the Employees table, it doesn't work correctly and shows mismatched results or dublicates. And I know thats maybe because it dosent have relationship betwen but I can only have one relationship at once. And I want the Employees table work for both projects and clients table or all the tables.

 

How to fix it?

 

Any other suggentions on my data modeling beyond this issue that you can see I could improve overall?

Need Help
Show and Tell
Tips and Tricks
  • v-prasare's avatar
    v-prasare
    10 months ago

    Hi selected_,

    In Data Engineering or Dataflow Gen2, you can reshape the data model so that Power BI receives a clean, star-style structure. Some recommended options:

    1. Flatten the Snowflake Schema

      • During data preparation, join sub-dimension tables (like Employees, CollClients, JobTypes, GroupProjects) into their parent dimensions (Clients and Projects). The output tables in your Lakehouse or Dataflow should be Projects, Clients, and Sales with clean surrogate keys.

    2. Create a Bridge Table in Fabric

      • If Employees need to relate to both Clients and Projects, build an intermediate mapping table (e.g., EmployeeProjectBridge) using Data Engineering or Power Query inside Dataflow Gen2. This table can later serve as a relationship hub in Power BI.

    3. Generate Surrogate Keys Upstream

      • Ensure that unique surrogate keys for dimensions are created in the dataflow or notebook, not just in Power BI, to simplify joins downstream.

    By restructuring relationships in Fabric, Power BI will automatically get a clean, filterable model  eliminating the duplicate or mismatched data issue seen in visuals.

     

     

     

    Thanks,

    Prashanth Are

5 Replies

  • samratpbi's avatar
    samratpbi
    Super User
    11 months ago

    Hi,

     

    You may create an inactive relationship between the Employees and Projects tables and make it work with the help of measures and USERELATIONSHIP.

    Thanks - Samrat

    • selected_'s avatar
      selected_
      Helper IV
      11 months ago

      I was asking for somehitng I could do in the Data Engineering or in the Dataflow gen2 inside Fabric.

      • v-prasare's avatar
        v-prasare
        Community Support
        10 months ago

        Hi selected_,

        In Data Engineering or Dataflow Gen2, you can reshape the data model so that Power BI receives a clean, star-style structure. Some recommended options:

        1. Flatten the Snowflake Schema

          • During data preparation, join sub-dimension tables (like Employees, CollClients, JobTypes, GroupProjects) into their parent dimensions (Clients and Projects). The output tables in your Lakehouse or Dataflow should be Projects, Clients, and Sales with clean surrogate keys.

        2. Create a Bridge Table in Fabric

          • If Employees need to relate to both Clients and Projects, build an intermediate mapping table (e.g., EmployeeProjectBridge) using Data Engineering or Power Query inside Dataflow Gen2. This table can later serve as a relationship hub in Power BI.

        3. Generate Surrogate Keys Upstream

          • Ensure that unique surrogate keys for dimensions are created in the dataflow or notebook, not just in Power BI, to simplify joins downstream.

        By restructuring relationships in Fabric, Power BI will automatically get a clean, filterable model  eliminating the duplicate or mismatched data issue seen in visuals.

         

         

         

        Thanks,

        Prashanth Are

  • v-prasare's avatar
    v-prasare
    Community Support
    10 months ago

    Hi selected_,

    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.


    samratpbi ,Thanks for your prompt response

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support

  • v-prasare's avatar
    v-prasare
    Community Support
    10 months ago

    Hi selected_ ,

    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support