10 things you should know when you use PowerPivot

I’ve got some colleagues I have infected with the PowerPivot virus, most of them aren’t BI or Excel user so i created a 10 things you should know list to get them started. Most items have a link to The Great PowerPivot FAQ with further information.

  1. You need Office 2010, with PowerPivot addin installed.
  2. What is PowerPivot? and Why PowerPivot
  3. When you add new data from an existing datasource make sure you reuse your connection.
  4. When joining tables with multi-column keys use concatenate to create a new key and use that to create a relationship and understand relationships in PowerPivot
  5. Hide the PowerPivot columns you don’t want to use, like keys.
  6. Know how to pimp your workbook so you can impress your manager
  7. Understand DAX and the Calculate DAX function
  8. When saving your Pivotsheet make sure you save it at the first cell A1 so the workbook won’t render at the wrong place when published to SharePoint  fixed in RTM

    PowerPivot loads your data into Excel, it is disconnected from the datasource. You need to manual refresh it or upload it to sharepoint where you can schedule the refresh

  9. When uploading to SharePoint use the file upload instead of the publishing function in Excel fixed in RTM

    Know the golden rules when working with Time Intelligent functions

  10. The last one is a bit philosophical and took me some time to figure out: Understand (as a Programmer / BI pro) the Excel mindset of the business analyst, think out of your relational/dimensional datawarehouse box, some redundant data is ok, some relational shortcuts are ok. Remember this is Ad-Hoc reporting you don’t have to make a design to solve all possible exceptions!

I would very much like your input on this, what did i forget or what do you think a user should know when he starts with PowePivot.

Update: i’ll add new questions here:

  1. Running PowerPivot on a 64 bits OS is recommended for larger data volumes – 1M or more rows. It increases performance a lot.

update: Check out my Getting started with PowerPivot blog post as well