Power BI Modelling — Using a Multi-Fact Model
You know you need a multi- fact table model when…you have a similar but different type of facts

As an example, you may have one table that deals with allocations of resources while another is expenditures. You may have different accounts; one is savings account and the other is a different account. You may have a budget table and you also have an expenses table.

These tables contain similar facts, they both deal with money, but the tracking is different. One table contains columns that are only relevant to budgeting while the other expenses. Also, the budget table aggregates at a monthly level, while the expenses table aggregates at a daily level.

Sometimes you may not be so lucky, sometimes you may get a fact table that is somehow flattened into one, like the one below, and you will soon see some issues with it.

Image by author

It’s hard to navigate the table like this, in scrolling through, you may think that the monthly budget may be the daily budget since there are different levels reflected in this table. The monthly budget is by month and the other by days. Too good to be an example? No, this is a real example I’ve seen.

There is a column that only pertains to the budget table (Donor_Name) and it has no relevance to the expenses. This can become a major problem if new columns need to be added. How about the donor’s dimensions? Perhaps we want to add in the donor’s address and time of donation? These fields, since they pertain to the budget part of this table, will need to have new columns. Adding columns can get out of hand quickly if this fact table is not separated properly.

You know you need a multi-fact table model when…when you have fact tables that naturally fit into an existing model

You can see, however, that some of the columns in this table naturally fit into the dimensions in the data model. It has the necessary dates and region. (The themes are existing)

Image by author

If I want to compare the budget and expenses by regions, the common dimensions (Date and Regions) should be existing in both fact tables.

You know you need a multi-fact table model when… when the fact tables need to be aggregated for comparison

Finally, the reason why you have to separate this fact table is for comparison purposes. We know having one big fact table is confusing, however, we will still need to make aggregated comparisons using it.

Something like this below very basic, but it’s only possible if you have a star schema already set up.

Image by author

To get to a comparison table like will require us to split up the fact table into two and make the relevant joins from there.
['Peter Hui']
['Technology', 'Data Modeling', 'Data Science', 'Power Bi', 'Data Visualization']