SQL PASS: PowerPivot Server Best Practises

Just sat in a fascinating session with Dave Wickert and Denny Lee, chatting about the deployment considerations for PowerPivot Server.

Very entertaining, I got my first session swag, and had a lot of laughs.

So, they ran through a large number of points, and I’m quite sure that they will produce full whitepapers with all the points… here are my personal top 6 lessons

Client

1. PowerPivot plugin within Excel client is an instance of the Vertipaq OLAP engine.
   1. This engine defies some of the basic laws of traditional SSAS, primarily  aggregations simply don’t exist in PowerPivot.
2. To minimise the Excel file sizes, check this cool trick…
   1. While this runs in memory, it also has a disk based version of the data, which can be found in User Appdata Local Temp IMBIXXX
   2. Look out for the DICTIONARY files in this folder, these files store the “dimensional attributes”
      1. If you have excessively large DICTIONARY files on attributes that are not used, simply drop those attributes

Server

1. Read the manual when installing! There are a number of server components involved in PowerPivot, so there are many dependancies, including
2. The Powerpivot Service can be located in all sort of places within the Sharepoint Server farm
   1. and you don’t necessarily need kerberos!! Sharepoint 2010 introduces Claims Based Authentication, which keeps the user token within the Sharepoint farm.
   2. Recommended to use the New Farm option when beginning, all configuration is handled for you.
3. Most important aspect of capacity planning is RAM.

General (and possibly most important…)

1. Powerpivot does not replace SSAS
   1. Although Powerpivot happens to use SSAS as it’s internal engine, this was done for calculation abilities, and not as a replacement roadmap for ‘traditional’ SSAS. Yes, all you BI professionals out there, we still have our jobs!