Power Pivot data model optimisation

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!

powerpivot_datamodel

  1. Remove columns from the model that aren’t required for reporting, particularly columns that contain a high number of unique values.
  2. Where possible round number columns to reduce or eliminate decimal places.
  3. 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).
  4. Modify Date Time columns so that only the required elements are shown – just the Date and not the Time for example.
  5. Filter rows so that only those required for reporting are imported into the data model.
  6. 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.

We hope you found this useful and/or interesting. If so please like, share or comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s