If you’ve developed a Power Pivot data model, and you need to reduce the size of data model (either because your experiencing performance issues OR the Excel workbook that contains the data model breaches other limits such as those that apply to SharePoint) then here are some tips for doing just that!
- Remove columns from the model that aren’t required for reporting, particularly columns that contain a high number of unique values.
- Where possible round number columns to reduce or eliminate decimal places.
- Replace Fact columns with Dax calculated columns or Dax calculated Measures. This would include removing columns where the value for a column can be calculated from other columns; for example don’t bring in Date, Month and Year if Month and Year can be calculated from Date).
- Modify Date Time columns so that only the required elements are shown – just the Date and not the Time for example.
- Filter rows so that only those required for reporting are imported into the data model.
- Finally try using the Excel Workbook Optimiser to identify other possible optimisations; however this tool primary looks for columns that contain a high number of unique values which it suggests you remove (as per 1.)
Please note: 1 to 5 should be applied to the source or to the data import. Applying these changes to the data model after the data import will make little or no difference to the size of your data model.