Forum Discussion
Data Model - Consolidating Multiple Fact Tables
I am somewhat new to power BI and am building a data model that uses multiple fact tables and would like your best practices to maximize resource efficiency of the model.
The data model is composed of 15 companies' data which need to be consolidated - shown here and explained below (only included 5 companies, but the setup is the same for all 15).
Each company has a raw transactions fact table (Account # - Company # - Date - Description - Amount $) which runs through a unique power query to clean/standardize the data set, and is related to that company's unique Account mapping table (Account # - Line Item #). Each company's mapping table is different, since each company has a unique chart of accounts.
Each mapping table (Account # - Line Item #) is then related to a common line item dimension table (Line Item # - Line Item description - Line Item category) which is a table connected to all companies, and will be used to filter the consolidated data.
There is also a calendar dimension table (Date - Month - etc.) and a company dimension table (Company # - Company name - etc.) that are connected to each transactions fact table, and also used to filter the consolidated data.
The question is this: How do I consolidate the Amount column from all companies' transactions fact tables? Should I create a measure that adds up all extracts' Amount and use that as my "Values"? Should I append a mapped version of each transaction fact table and use that consolidated data set as a singular fact table? Any other better, more elegant solutions?
Each company's transaction extract varies from 50K-100K rows at year-end.
Thank you for your help!
PB76 , In such case we usually append all tables (Usually we do not append date table, but possible to do so).
All Tables need to have a company id. Now if you want to do a full exercise in power query, you need to add an index column in the dimension tables table, then merge and populate the ID back
You can refer: https://youtu.be/vHuhbvYCiNc
1 Reply
- amitchandakSuper User
PB76 , In such case we usually append all tables (Usually we do not append date table, but possible to do so).
All Tables need to have a company id. Now if you want to do a full exercise in power query, you need to add an index column in the dimension tables table, then merge and populate the ID back
You can refer: https://youtu.be/vHuhbvYCiNc