top of page

Deviating from Star Schema Principles

Updated: Feb 13, 2023

A finance user case

The Star Schema methodology is the application of dimensional modelling techniques to a relational database. This includes not only simple star shaped models, but a wide range of techniques such as snowflaking or factless fact tables. These principles should (almost always) be adopted in Power BI (a relational-like database). This article discusses one of the exceptions; dealing with subtotals.

 

The Problem: Why is a Star Schema not able to produce Financial Statements

Suppose we wish to produce the following:


A typical Star Schema (with snowflaking) for this data would look like the below:

The sum of "Value" grouped by "P&L line" results in the below table:

This doesn't provide an adequate solution as the required subtotals "Total Gross Revenue" and "Gross Profit" are not present.

 

Solution

The advised approach is to create 2 calculations:

  • The first calculation sums the Ledger values following the path (1) & (4) as per a usual star schema

  • The second calculation sums the Ledger values following the path (2), (3) & (4)

This second calculation deviates from Star Schema methodology; relationship (3) is a many-to-many virtual relationship managed through the use of TREATAS. The below table shows the result of both calculations:


The 2 calculations are then added together to achieve the desired result:

 

Key Benefits of this Approach

  1. Native integration with Excel where Pivot tables and formulas can point at the Power BI API resulting in a single-source of the truth across various excel files and Power BI reports

  2. Other custom totals, like call out numbers at the bottom of financial statements, can be created - i.e., if below "Earnings Per Share" (EPS) the desire is to show "Full Time Equivalent" (FTE) staff costs this can be achieved

  3. Percentages can be created - i.e., Gross Profit percentage - from any combination of sum or subtotal and we can use calculation group formatting strings to format these appropriately in Power BI and any Excel pivot tables or formulas

  4. Multiple layouts can be handled - i.e., a senior (more consolidated) view of financial results

  5. Performant over large volumes of data - see 1 billion row example

  6. Reports can be amended by changing the inputs - no need to re-write DAX

  7. Can scale to work with 3-way Financial Reporting

 

Alternative Approaches

There are, of course, alternatives to this approach. The 4 main alternatives are:

  1. Duplicating records in the fact

  2. DAX for each subtotal

  3. Custom Visuals


Options 1 & 2 are included for completeness and are not advised; these violate redundancy principles and become cumbersome to manage, respectively.


Custom visuals are a valid solution and are vital in cases where the formatting options in native Power BI visuals are insufficient. However, a model is still required. Therefore, I would recommend the modelling approach outlined alongside a custom visual. This is because if you allow the custom visual to contain your logic for subtotals you surrender control to the visual (and whomever developed it) for your business logic. Generally, it's better to maintain your logic upstream in your database; this is in line with Roche's maxim of data transformation.


The many-to-many approach is also a valid solution. This also breaks Star Schema principles by creating a many-to-many bridge table. The main advantage of this is the DAX is very simple; a sum will work for subtotals and the sum of ledgers. If you don't have to worry about percentages (i.e., Gross Profit percentage), call out numbers (i.e., FTE) or custom calculations (i.e., EPS) this is a valid solution.

 

Conclusion

Despite breaking Star Schema principles, the model outlined is a valid and performant method for developing financial statements. To learn more and build out your financial solution, join over 1,000 people who have taken the free course on Power BI P&L fundamentals










1,661 views4 comments

Recent Posts

See All

4 Comments


Chris Barber
Chris Barber
Feb 12, 2023

Thanks Deron. I really appreciate the

Like

Deron Huskey
Deron Huskey
Feb 11, 2023

Chris, if I understand you correctly, you've represented the individual accounts multiple times (within relationship 3) according to how they are subtotaled within the P&L, is that correct?

Like
Deron Huskey
Deron Huskey
Feb 11, 2023
Replying to

That was going to be my follow up. I remember this design from the course, but it's been a few months. I think I need to re-watch it.


Thanks and keep doing what you're doing. Wonderful work and examples.

Like
bottom of page