Forum Discussion
Best practice normalising my data model with multiple facts tables?
Hello,
I have set up a dashboard with a filter on the front page that allows the user to select a string of text from the dimension table (This will be areas in our factory such as "Production Line 1", "Warehouse", "Engineering", etc...).
It is currently setup so that both fact tables will correctly filter based on what the user selects. Also, each of these fact tables are several thousand lines long (one is nearly at 10,000 rows) and increasing hourly.
I believe in the data model it will be better for storage purposes to index my dimension table and replace the string value in each of the facts tables with their respective index value, but due to the way the data is stored I believe I will have to do this as a merge and expand for each of the facts tables in Power Query.
Is this still better practice or should I ignore having an integer index and just join on the text value to avoid 2 additional merges?
It may also be worth adding that this dashboard is set to refresh every hour.
Thank you.
Hi TheChocolateGuy ，
According to experience, I prefer to use the dimension table to connect directly to the fact table instead of creating additional surrogate keys.
Understand star schema and the importance for Power BI
Since I don't have actual models to test, optimization is a combination of practice and theory. You may consider using the performance analyzer in both models to further test report performance.
Use Performance Analyzer to examine report element performance in Power BI Desktop
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
1 Reply
Hi TheChocolateGuy ，
According to experience, I prefer to use the dimension table to connect directly to the fact table instead of creating additional surrogate keys.
Understand star schema and the importance for Power BI
Since I don't have actual models to test, optimization is a combination of practice and theory. You may consider using the performance analyzer in both models to further test report performance.
Use Performance Analyzer to examine report element performance in Power BI Desktop
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum