Append multiple fact tables into one or keep them seperated?

[Anonymous]

Hey guys,

So I come from a Qlik background and when we do regular E-T-L there we usually in the Load part combine different fact tables into one large fact table.

This is for the following reasons:

1. Get all the fact tables into one
2. We do a Left Keep towards the final fact table to remove unecessary values in the dimensional table. This is similar to a Inner Join.
3. We generate the date table based on the Min Max date found in the fact table so we only get a date table with relevant time.

In Power BI we are fetching from BigQuery and we have DWH with fact tables and dimensions and if I want to make an app that looks at Orders and Sales we would have to do it in the DWH with another layer and load that into PBI because PBI can't handle it. Takes too long.

Are there any pros or cons in PBI to keeping the fact tables seperated vs. putting them in one?

From our own point of view if we don't gain anything by keeping them in one, we would just keep them seperated and connect them to look up tables...maybe I am thinking too much with my Qlik hat on...

Any thoughts or replies are greatly appreciated.

Best,

Ali A

[Anonymous]

a discussion on various ways of doing this see https://maxpowerbi.pro/facts-facts-facts-five-ways-of-combining-several-fact-tables-in-a-powerbi-rep...

[Anonymous]

For a discussion on several different ways of doing this, see https://maxpowerbi.pro/facts-facts-facts-five-ways-of-combining-several-fact-tables-in-a-powerbi-rep...

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To get all the fact tables, we will get a huge table. The size of the file will be bigger. Also, in that way, be wary of DAX functions, which need to test every row in a table – for example, RANKX – in the worst case, these functions can exponentially increase run-time and memory requirements given linear increases in table size. 

So keeping them seperated is prefered. We can use dimension tables to link to the fact tables. DAX will calculate the measures against the common dimensions and then let you compare the results for the different fact tables in the same analysis. Please refer to the article here.

https://powerpivotpro.com/2012/01/salesbudget-integrating-data-of-different-grains/