We’re experiencing 30+ minute refresh cycles on our enterprise star schema model in Power BI Desktop (2019). Our model has 4 fact tables (Sales, Inventory, Orders, Returns) connected to 12 dimension tables. The relationships are configured as follows:
Sales[ProductKey] -> Product[ProductKey] (Many-to-One)
Sales[DateKey] -> Date[DateKey] (Many-to-One, bi-directional)
Inventory[LocationKey] -> Location[LocationKey] (Many-to-One)
We’ve noticed the Date dimension has bi-directional filtering enabled for time intelligence calculations, but I’m wondering if this is impacting our cardinality and causing the slow refresh. The Sales fact table has 50M rows, and query folding seems inconsistent across our data sources. What’s the best approach to optimize star schema relationships and reduce refresh time? Should we be looking at aggregation tables or rethinking our relationship configurations?
Your cardinality settings might be suboptimal. Make sure all relationships are explicitly set to the correct cardinality (Many-to-One for star schemas) rather than leaving them on Auto-detect. Auto-detect can sometimes get it wrong and create Many-to-Many relationships which are performance killers. Also check for duplicate keys in your dimension tables - if Product[ProductKey] has duplicates, it breaks the one-side of the relationship and forces expensive cross-filtering. Run DAX Studio and look at the VertiPaq Analyzer statistics to see actual cardinality and identify problem tables.
Bi-directional relationships are definitely a red flag for performance. They force the engine to evaluate filters in both directions, which multiplies the computational overhead especially with 50M rows. For time intelligence, you should avoid bi-directional filtering and use USERELATIONSHIP or TREATAS functions in your DAX measures instead. This gives you explicit control over filter propagation without the performance penalty. Also check if your Date dimension is marked as a date table - this enables query optimizations that aren’t available otherwise.
Don’t overlook the impact of calculated columns versus measures. If you have calculated columns in your fact tables, they’re computed during refresh and stored, bloating the model size and slowing compression. Move calculations to measures where possible - they’re computed at query time only when needed. Also, review your dimension tables for unnecessary columns. Each column consumes memory and processing time during refresh. Remove columns that aren’t used in reports or relationships. Use the Performance Analyzer in Power BI Desktop to identify which visuals are triggering expensive queries and optimize those specific DAX patterns.