SSAS Tabular and Excel PowerPivot

I don’t know why it took so long before making the connection between PowerPivot models and SSAS Tabular models, but in the last couple of days I came across several blogs and videos tying the two together. With that in mind, I took the plunge into PowerPivot and built a quick model off of some SQL queries to test out a concept.

I started with the basics of mocking up a fact table. I stripped out decorator text, “renamed” a couple of columns in the query, filtered the data so I wouldn’t pull back a crazy amount into my local workstation, verified the counts and look of the data in a SQL query, and added that. Excel quickly loaded 1.2M rows and didn’t blink.

After that, I knew I needed a couple of Dimension-like tables. I wrote queries for those in a similar fashion and added the data again. Each addition went smoothly. I was able to add the proper relationships quickly to tie everything together.

To finalize the model, I created a couple of hierarchies to make querying easier. After messing around with these several times in SSAS Multi-Dimensional, I was shocked at how easy it was to create a hierarchy for dates and even certain domain-specific entries in our data: Right-click, create new hierarchy, drag and drop columns. Done.

After the model was finished, I dropped the relevant fact data into a Pivot Table and could easily analyze the data in all sorts of interesting ways. I worked around this with OLAP for quite some time to come up with a similar concept. With PowerPivot, I wrote a couple of queries, linked the tables, and had something workable within an hour. As an added benefit, I know I can scale that up to an SSAS server without too much trouble and have a similar experience.

Hat tip to the folks @ www.powerpivotpro.com and www.sqlbi.com for the many articles and pointers to get me started and over that initial hump.

Installing SQL Server, Sharepoint, PowerPivot on a single server

I’ve been trying to set up a single virtual machine for a proof of concept using SQL Server Denali CTP3, Sharepoint 2010, and PowerPivot. I want to get Project “Crescent” running through this as well to prove out some report concepts. I’ve had a lot of trouble finding the right steps to get everything installed and working correctly on a single box, especially since my Sharepoint knowledge right now is practically non-existent when it comes to BI. Every time I tried to get the Sharepoint / PowerPivot integration working, I hit a roadblock or managed to corrupt my Sharepoint install.

I had the opportunity to speak with Brian Knight ( blog | twitter ) and he pointed me to PowerPivotPro.com which, in turn, led me to PowerPivotGeek.com. They had a page of instructions for single-server installs with a pointer to an MS whitepaper and a private cached file of the whitepaper just in case. This gave me the information needed to get PowerPivot working on my VM.

A couple of notes from my personal experience:

  • Install Sharepoint SP1 right after installing Sharepoint 2010. SP1 is required when you’re using Denali
  • Do NOT configure Sharepoint until the directions tell you to do so.
  • Use a Named Instance in order to get PowerPivot working.
  • If you plan to use Crescent, make sure that you set up SSAS with the “Tabular Data” option (or whatever the final name ends up being).

Once again, thanks to Brian for pointing me towards the right solution. Thanks to PowerPivotGeek for hosting these files. I imagine the instructions would work for Virtual Server, Virtual PC, VMWare, VirtualBox, or similar Virtual Machine apps. I hope this is helpful to anyone else trying to set up their own VM for testing out the Microsoft BI stack.