Forum Discussion
Data Model for Forecast System with Pre-aggregated Forecast Values
Hi Mahhin_Shahzad1 ,
Hey, really appreciate the thorough background and your clear outline of the issue. Mixed granularity in fact tables is a classic challenge (and pain!) in data modeling for Power BI or pretty much any BI tool.
1. **Direction & Handling:**
You’re definitely on the right path by considering splitting your data into separate fact tables, especially when you have a mix of transaction-level details and pre-aggregated values. Most Power BI pros would not try to force everything into a single “everything table” because it gets messy and hard to maintain, as you’ve seen. The “Galaxy Schema” (sometimes also just called a constellation schema) pattern is pretty standard for these cases.2. **Two Fact Table / Galaxy Schema:**
Yes, this is usually the recommended approach. You keep your detailed fact table (Fact_Transactions) purely for row-level data, and another fact table (Fact_FinancialSummary) for those pre-aggregated or “black box” values. Both connect to shared dimension tables. This way, your DAX and visuals can pick the right granularity as needed, and your model stays scalable and much easier to troubleshoot.3. **Using Pre-Aggregated Totals:**
A lot of organizations do use pre-aggregated numbers from external systems, especially for financial stuff where the official source-of-truth is outside Power BI. You do lose some flexibility (like drill-down, as you said), and you may not be able to recompute those numbers exactly if business logic changes. But it’s a fair tradeoff if you can’t or shouldn’t recreate the calculations in DAX. The main thing is to make sure you clearly document what each fact table contains and keep the relationships tidy, to avoid ambiguity in your visuals.4. **Extra Resources:**
If you want to go deeper, I’d suggest checking out “The Data Warehouse Toolkit” by Kimball (if you haven’t already) – the galaxy/constellation schema is explained really well there. Also, SQLBI’s articles and videos are gold for advanced Power BI modeling tips, especially around handling multiple fact tables and composite models.One last tip: sometimes using calculation groups (if you’re on SSAS Tabular or newer Power BI features) can help manage metrics that span both granular and summary data, but that’s a bit advanced.
If you need some sample model or DAX patterns for this, let me know!
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
Translation & text editing supported by AI
Hi Mahhin_Shahzad1 ,
Hey, really appreciate the thorough background and your clear outline of the issue. Mixed granularity in fact tables is a classic challenge (and pain!) in data modeling for Power BI or pretty much any BI tool.
1. **Direction & Handling:**
You’re definitely on the right path by considering splitting your data into separate fact tables, especially when you have a mix of transaction-level details and pre-aggregated values. Most Power BI pros would not try to force everything into a single “everything table” because it gets messy and hard to maintain, as you’ve seen. The “Galaxy Schema” (sometimes also just called a constellation schema) pattern is pretty standard for these cases.
2. **Two Fact Table / Galaxy Schema:**
Yes, this is usually the recommended approach. You keep your detailed fact table (Fact_Transactions) purely for row-level data, and another fact table (Fact_FinancialSummary) for those pre-aggregated or “black box” values. Both connect to shared dimension tables. This way, your DAX and visuals can pick the right granularity as needed, and your model stays scalable and much easier to troubleshoot.
3. **Using Pre-Aggregated Totals:**
A lot of organizations do use pre-aggregated numbers from external systems, especially for financial stuff where the official source-of-truth is outside Power BI. You do lose some flexibility (like drill-down, as you said), and you may not be able to recompute those numbers exactly if business logic changes. But it’s a fair tradeoff if you can’t or shouldn’t recreate the calculations in DAX. The main thing is to make sure you clearly document what each fact table contains and keep the relationships tidy, to avoid ambiguity in your visuals.
4. **Extra Resources:**
If you want to go deeper, I’d suggest checking out “The Data Warehouse Toolkit” by Kimball (if you haven’t already) – the galaxy/constellation schema is explained really well there. Also, SQLBI’s articles and videos are gold for advanced Power BI modeling tips, especially around handling multiple fact tables and composite models.
One last tip: sometimes using calculation groups (if you’re on SSAS Tabular or newer Power BI features) can help manage metrics that span both granular and summary data, but that’s a bit advanced.
If you need some sample model or DAX patterns for this, let me know!
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
Translation & text editing supported by AI