Hi,
I've been playing around with file size and speed and can't figure out the most efficient way to store my data. Is there a consensus on where to store data for your dashboards? I have about 10 different spreadsheets of sales data with around 500K rows of data for each spreadsheet. Right now it seems like I'm getting better performance by storing data in Access and running the pivot tables / dashboards in Excel from there as opposed to Power Pivot. Are there any general rules as far as when we should use Access vs Excel.......as far as file size, rows of data, etc.? Any discussion here about how to most efficiently feed your dashboards would be welcome. Thanks in advance!
-Scott
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
