Forum Discussion
Power BI Modeling Performance
Hi, I would like to ask for advice on power bi modeling.
I have a table that stores by store, such as day of the week or time zone by month.
Currently, the columns keep increasing sideways according to classification like this,
and I don't know whether it would be better to increase the columns or increase the rows for use in power bi, so I'm asking.
The Pbix file size became too large at around 3 GB, with around 7 million rows, as the column was increased.
Hi ericjo
Hi,
Your current approach of using a very wide table with many columns (one for each time period or classification) introduces serious challenges:Poor performance due to high column count and low compression
Increased file size (as you’ve already noticed)
Difficulty writing flexible DAX calculations
Complicated and fragile data model
Recommended approach: Use a star schema.
In this structure:
You have one central Fact table with many rows and a narrow set of columns:
Store ID
Purchase Date
Product ID
Sales Amount / Quantity
Other metrics as needed
Around it, you define Dimension (Lookup) tables for:
Date (calendar)
Product
Customer
Store
Time zone, etc.
Important rule:
Each dimension table should contain one unique row per entity:One row per product in the Product table
One row per date in the Calendar table
One row per customer in the Customer table
This ensures clean relationships and optimized performance.This structure is the best practice for Power BI modeling, and will make your reports faster, smaller, and more maintainable.
For more guideness please relate :
https://learn.microsoft.com/en-us/power-bi/guidance/star-schemaIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
4 Replies
Hi ericjo
Hi,
Your current approach of using a very wide table with many columns (one for each time period or classification) introduces serious challenges:Poor performance due to high column count and low compression
Increased file size (as you’ve already noticed)
Difficulty writing flexible DAX calculations
Complicated and fragile data model
Recommended approach: Use a star schema.
In this structure:
You have one central Fact table with many rows and a narrow set of columns:
Store ID
Purchase Date
Product ID
Sales Amount / Quantity
Other metrics as needed
Around it, you define Dimension (Lookup) tables for:
Date (calendar)
Product
Customer
Store
Time zone, etc.
Important rule:
Each dimension table should contain one unique row per entity:One row per product in the Product table
One row per date in the Calendar table
One row per customer in the Customer table
This ensures clean relationships and optimized performance.This structure is the best practice for Power BI modeling, and will make your reports faster, smaller, and more maintainable.
For more guideness please relate :
https://learn.microsoft.com/en-us/power-bi/guidance/star-schemaIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Hi ericjo ,
Just wanted to check if you had a chance to review the suggestions provided and whether that helped resolve your query?
Thank You Ritaf1983 for your insight on the query.
Aditionally, you might want to check out a similar post-
https://community.fabric.microsoft.com/t5/Desktop/Performance-best-practice-with-wide-tables/m-p/280737
Hope this helps!
