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.
- You need Office 2010, with PowerPivot addin installed.
- What is PowerPivot?
- When you add new data from an existing datasource make sure you reuse your connection.
- When joining tables with multi-column keys use concatenate to create a new key and use that to create a relationship
- Hide the PowerPivot columns you don’t want to use, like keys.
- Know how to pimp your workbook so you can impress your manager
- Understand DAX and the Calculate DAX function
- 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
- When uploading to SharePoint use the file upload instead of the publishing function in Excel
- 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:
- Running PowerPivot on a 64 bits OS is recommended for larger data volumes – 1M or more rows. It increases performance a lot.
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.
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