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 dimensio...
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

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