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

  • Maurijn

    Hi Kasper,

    Nice post.
    I’m just wondering though, what type of audience are you targeting?

    On the one hand, I think this blog will be read predominantly by the IT-pro (that’s us I guess:) who will probably be introducing and supporting PowerPivot to the business. I wonder of these guys will actually be doing a lot of pimping or creating DAX calculations for instance.

    On the other, some items in your list seem to be adressing the business analyst who will be working with PowerPivot most of the time. For these users, some of your tips are probably less relevant.

    Maybe each type of user should have their own top-10 don’t you think?

    Anyway, like the list already. Maybe you could add an item about the why of 32 vs 64-bit installation.

    • Kasper de Jonge

      Hi Maurijn,

      Thx for the reply. I think this list is applicable for both the it-pro and the business user (except the last item). I can’t think of building a serious workbook with PowerPivot without these items. DAX is my number one item to solve any problem, and I like to make sure my sheets look great, it’s done in about 5 seconds and user appreciate it a lot. Although PowerPivot is a reasonable user friendly application some technical skill will still be required from the Business user (like keys and datasource best pratice), mostly the Excel Pro, these users are already very familiar with formulas and working with tables. I understand your remark but in my opinion is PowerPivot currently positioned right in the middle of the IT and Excel pro you need to have a little of both to really be able to use PowerPivot.

      Kasper

  • Hi Kasper,
    Thx for all the information. Was and is very useful. The questions i have are:
    1) whether Powerpivot can be used for day-to-day (P&L) financial reports or is it more to be used like a ad-hock analitical tool?
    2) The company i am working for is concidering on buying a reporting tool (like JetReport). So we like to know whether Powerpivot can do the job for us?

    Eric (work in finance department)