So my schedule has finally allowed me some free time again to get back to what I really enjoy lately in business intelligence and that is PowerPivot.
I mentioned way back in April how I had purchased the book “Microsoft PowerPivot for Excel 2010: Give Your Data Meaning” by Marco Russo & Alberto Ferrari for my Kindle and I really liked the book but I needed to buy the paper version to be a better resource that I could keep next to my keyboard as I was exploring PowerPivot. So I also bought the paper version and started digging in.
This is an excellent book and a terrific dive-right-in resource – can’t recommend it enough. I have been putting this book to good use and have been really using all of the PowerPivot features as I learn of them. Reminds me of the quote “Can’t just read about swimming – you are going to have to get wet” – well, same thing with PowerPivot – you just have to get wet. Last spring I was just wading in the shallow end with PowerPivot – right now I am in the deep end with all of the new DAX syntax (more on that in a later post)
First up was a PowerPivot spreadsheet from some SQL Server Analysis Service datamarts I had created over the last few years, and PowerPivot performed like a champ. I imported over 8 million rows in less than 2 minutes on my workstation. I did run overflow Excel a few times while trying to size the data correctly and in the end chose to load my main fact table with a query that limits the data to the last 3 years – this way I can refresh the data anytime I want and I have no hard-coded constraints when I point my PowerPivot spreadsheet to another database.
(The 32-bit version of Excel 2010 (which is what I run with) has a limit of 1 GB – however, the built-in compression of the Vertipaq storage in the PowerPivot database gets about 10-to-1 compression so you actually have the ability to store (depending on many factors of course) 5-10 GB of data (and close to 10 million rows) into PowerPivot spreadsheets depending on the cardinality of each of the columns involved. The 64-bit version of Excel 2010 can handle much more.)
Then this week, a team was looking for a dashboard to monitor client issues and after seeing my PowerPivot demo last week wondered if PowerPivot might be able to do something for them as well – only took about a day and a half (most of that spent gathering the data) and now we have a very powerful and rich dashboard capable of some very nice slicing and dicing of client information. The fact that PowerPivot is a free add-in for Excel 2010 users is awesome. And this tool does turn heads.
One of the silliest things that took me a few minutes (and I am embarrassed to say a post on the MSDN Forums) to find was : How to tell which version of PowerPivot you are running? I am so used to checking each applications “About” menu but Excel 2010 (and PowerPivot have no “About” screen). Some kind soul took pity on me and showed me the Settings button on the PowerPivot tab as seen below highlighted – doh! Felt dumb but that’s not a completely rare feeling for me so now I know.
Using this button revealed my version was 10.50.2500.0 which is the current SQL Server 2008 R2 version of PowerPivot.
Downloadable from here – http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=7609
But the recent excitement in the SQL world revolves around the next version of SQL Server codenamed “Denali” which has been releasing CTP versions of its bits including a updated version of PowerPivot (version 11.0.1440.19) which has many features users are anxious to take advantage of – more on that in a upcoming post.
Worth noting that if you install the 32-bit version of Excel, you must use the 32-bit version of PowerPivot and if you install the 64-bit version of Excel, you must use the 64-bit version of PowerPivot. If you have any problems with PowerPivot, you have to make sure that the version of Excel you are running is compatible with your PowerPivot version. There are 2 ways to check Excel 2010 version:
- Option 1 – Start Excel, Choose menu item “File”, then “Help”. In the screen you will see message that looks like: “Version: 14.0.4760.1000 (32bit)
- Option 2 – to to folder where Excel 2010 is installed. By default this will be “C:\Program Files\Microsoft Office\Office14”, then select file “Excel.exe”, right mouse click and go to properties and “Details” tab. There you will see property “File version”. In my case value was “14.0.4756.1000”.
One problem I did encounter with a new PowerPivot spreadsheet I tried to create early on was I started to get this error “Initialization of the data source failed. “
Fortunately a quick Bing located this post from a forum http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/11dbc33e-de00-4a78-b0c5-b62cae1c9b8c
Where the workaround was revealed to be :
1) Un-install the PowerPivot add-in
2) Rename the folder c:\Program files\Microsoft Analysis Services
3) Reboot the box
4) Re-install the PowerPivot add-in
which fixed my problem and I have not seen it since
Many of my upcoming posts will be PowerPivot-related as I have been spending so much of my extra time getting to know this powerful tool and I want to log those things I have found helpful for me and others – so far it’s been time well spent.