PowerPivot data model with multiple fact tables

[PB76]

Excel Version: Office 365 desktop

I am somewhat new to power query 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.

Each company has a raw transactions extract (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 extract, and also used to filter the consolidated data.

The question is this: How do I consolidate the Amount column from all companies' transaction extracts? Should I create a measure that adds up all extracts' Amount and use that in my "Values" powerpivot? Should I append all the mapped transaction extracts and use that data set as a singular fact table? Any other better solutions?

Each company's transaction extract varies from 50K-150K rows at year-end.

Thank you for your help!

[Anonymous · Accepted solution]

Hi @PB76 ,

A good model is the beginning of everything.

Understand star schema and the importance for Power BI

-- Should I append all the mapped transaction extracts and use that data set as a singular fact table?

If possible, this is the preferred solution. PowerPivot is optimized for handling large, flat tables. A single fact table can improve performance compared to multiple fact tables. And it simplifies the creation of metrics and calculations because you only need to reference one table.

-- Each company's transaction extract varies from 50K-150K rows at year-end.

Considering the amount of data you are working with and the increase in the amount of data each year, it may be worth considering a platform other than Excel. While Excel is powerful, it can be slow and cumbersome when working with large datasets. One example is Power BI.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum