Where to store data | Dashboards & Charts | Excel Forum

Hi Scott,

There are lots of missing details regarding what you've tried so far. Here are some things to consider:

1. If the data you're loading into Power Pivot is coming from Tables in the same Excel file then you're essentially storing the data in that file twice; once in the worksheet and again in the Power Pivot model. Better to put the source data in a different Excel file.

2. If the data on these 10 sheets is transaction data e.g. one for each month or region etc. then it should be in one sheet/table. You can use Power Query to consolidate it. This will help Power Pivot and Access compress it more efficiently.

Power Pivot compression works at its best when there are lots of duplicates in the columns in your table. This is typical of transaction data where you might have multiple sales of the same products, multiple sales on the same date etc. If you have 10 tables with transaction data then it can't compress this as well as it could if it were all on one table. Not to mention, this is the correct way to structure your model.

If your 10 tables contain dimensions of an attribute, for example a customer:  customer number, customer name, customer address, customer phone number etc. then you only need the customer number on the transaction table and the name, address etc. can be moved to a dimension table.

There are a few ideas, but it's difficult to be any more specific without seeing a sample of the data you have on these 10 sheets and knowing more about where it is stored and how it is loaded into the model.

Mynda