Data Model for Forecast System with Pre-aggregated Forecast Values

[Mahhin_Shahzad1]

TL;DR: My single fact table from our forecasting tool is full of mixed data (details, monthly/yearly totals, percentages). I wish for simpler DAX later , What is the industry-standard pattern to transform this into a clean, maintainable, and performant Power BI model?

Hello Data modeling experts,

I'm hoping to get some strategic advice on a data modeling problem. We have a forecasting engine that is an alternative to Excel-based budgeting, and it was previously used only for reporting with an older, cell-based evaluation tool (Exago).

I believe the old system had no data modeling capabilities, so all the logic, groupings, and drill-downs were built directly into the fact tables themselves. Now, as we move to Power BI to build proper dashboards, I am new to Data Modeling and want to create a model that follows best practices and is easy to update for future reports.

My first step was to combine our multiple source tables (P&L, Balance Sheet, etc.) into a single fact table, then comes 2nd Problem

The Situation: My Everything-in-One Fact Table

This combined table is a classic case of "mixed granularity." For any given month, it contains:

• Detailed Transactions: Individual, summable rows like specific sales or expense lines.

• Pre-Calculated Subtotals: Rows for things like 'Total COGS' or 'Total Assets' on a monthly basis.

• Pre-Calculated Yearly Totals: Rows like 'Retained Income' that have no specific month.

• Ratios & Percentages: Rows for metrics like 'Gross Profit %'.

• Complex "Black Box" Totals: Rows for values like 'Corporation Tax' and 'Income Tax', which are generated by a complex external engine and must be used as-is.

I have already done a lot of work in Power Query to remove the obvious totals (like monthly 'Total COGS') and have recreated them as DAX measures. However, I am left with the complex, pre-calculated values that I cannot easily replicate, nor do I want to, as I don't need to drill down into them and they are just Yearly values.

So What is the Right Architecture?

My goal is a scalable, easy-to-maintain model that gives me the choice of when to use my own DAX calculations and when to use the pre-aggregated totals from the source system.

I've been suggested by ChatGPT to use a Galaxy Schema by splitting my data into a Fact_Transactions table (for the clean details) and a Fact_FinancialSummary table (for the pre-calculated totals), with both sharing my dimension tables. I just want to know if that is the ideal and only solution, or if there is a cleaner alternative.

Current Data Model

My Major Constraint: It takes about 30 seconds for a dashboard to load in the app. Each time a person views a dashboard, the capacity gets activated first, and then the refresh happens always via API. The whole appending query of things has already increased refresh time as compared to when working with multiple fact tables.

Given this situation:

1. Am I Going in the right direction and how an expert handled such Situation.

2. Is the "Two Fact Table" (Galaxy Schema) approach the right way to handle this?

3. How common is it to use pre-aggregated totals from a source system like this instead of redoing all the formulas on your own, and what are the obvious features I’ll lose if I use this approach? I only know about drill-down.

4. If you have nothing else to say, then most importantly, please recommend if there are any other advanced modeling techniques or resources (books, articles) you would recommend for someone looking to improve their financial data modeling skills in Power BI.

Any advice would be incredibly appreciated. Thank you!

Upvote1Downvote0Go to commentsShare

[v-pagayam-msft]

Hi @Mahhin_Shahzad1 ,
Thank you for the helpful responses @burakkaragoz  and @DataNinja777 !

The provided answer is accurate and may help you to resolve the query.Have you got an opportunity to review the information provided by community members?If it resolved, consider accepting their answer as solution.If still facing any issue, feel free to reachout.
Thank you.

Regards,
Pallavi.

[burakkaragoz · Accepted solution]

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

[DataNinja777]

Hi @Mahhin_Shahzad1 ,

Yes, you are absolutely on the right track. The two-fact-table approach you've designed is an excellent and standard industry practice for handling mixed-granularity financial data. This model correctly separates your data based on its grain, leading to a system that is clearer, more performant, and easier to maintain.

Your proposed architecture, with a Fact_Transactions table for detailed, additive data and a Fact_FinancialSummary table for pre-calculated or non-additive figures, is the ideal solution. It avoids the complexity and error-prone nature of a single large table. This separation prevents accidental double-counting and allows DAX measures to be simpler and faster, as they can be targeted at the correct table without needing complex conditional logic to differentiate data types.

It is very common in financial analysis to use pre-aggregated totals from a source system, especially for complex, "black-box" calculations like taxes, which are considered the single source of truth. The trade-offs, as you've noted, are a loss of certain features. The most significant limitations are the inability to drill-down from a summary total into its constituent details and the loss of dynamic slicing. For example, a pre-calculated yearly value like 'Retained Income' cannot be correctly sliced by month; the value will simply repeat, which can be misleading if not handled carefully in your reports. You also lose calculation flexibility, as any changes must be made in the source engine.

To deepen your financial modeling skills, it's highly recommended to explore resources from industry leaders. For books, start with "The Definitive Guide to DAX" and "Analyzing Data with Power BI and Power Pivot for Excel," both by Marco Russo and Alberto Ferrari. For ongoing learning and specific patterns, their website, SQLBI.com, is the most critical resource you can find. You should also explore RADACAD.com for clear, practical articles on a wide range of Power BI topics.

Best regards,