Just last week I attended a Microsoft presentation with a subject: “SQL Server 2008 R2 for Developers”. The presenter went through most of the new features in R2 and in SQL Server 2008 but the largest chunk of the presentation was dedicated to PowerPivot. Unsurprisingly, the 50+ developers in the room had not heard anything about it and did not seem very interested in what the presenter had so say and show. I was not surprised not because the feature is insignificant, but mostly because .NET developers are naturally not the best audience for database presentations, let alone for new BI functionality which is tightly coupled with Excel.
However, my eyes and ears were wide open for what the Technology Specialist had to say and he did alright – showed us how we can use Web Slices from SSRS 2008 R2 as a data source for PowerPivot, how we can publish reports through Excel Services, sorting and filtering 101 million rows in less than a second, etc. It was quite impressive.
After the presentation a senior developer approached me (since I am the BI go-to guy) and asked me, among other easy questions, the following: “Who will be using all this?” and “How will users upload 101 million rows spreadsheet to SharePoint?”
Since I had no straight answer for him, I thought it might be a good idea to ask the Microsoft national technology specialist. The replies (not literal quotes) we got were:
“How will users upload 101 million rows spreadsheet to SharePoint?”
Because PowerPivot uses column compression, 101 million rows actually take between 10 and 15 Mb, so uploading them over a network connection to SharePoint is not a problem.
“Who will be using all this?”
I understand that the potential use scenarios for PowerPivot are quite limited, but we (Microsoft) believe that there will be some cases where power-users will need and want this sort of functionality.
Fair enough. However, both of the answers were not convincing. The problems with them are:
1. What happens when we have a large number (say 1-2 million) dimension values, and a large number of facts as a source for PowerPivot? Obviously, column compression will not work that well in this case, which could be quite common.
2. If PowerPivot is mainly for power-users, how powerful do they need to be to take advantage of it? It seems like only a tiny subset of power-users with some expert Excel knowledge will be able to operate this new Excel functionality. I will be quite surprised to see users managing live feeds, analysis services sources, DAX, etc.
3. If BI developers first have to build a PowerPivot environment for the end-users, this does not seem like self-service BI at all, and I would be reluctant to do that if I can achieve the same and more though Analysis Services.
What I liked and I think would be quite useful are the numerous improvements to Excel 2010 as a data analysis and report generation tool. The product seems quite improved and I believe that it will get adopted quickly by heavy Excel users. However, I am somewhat perplexed by the buzz around self-service BI and PowerPivot in particular. Especially since we (BI developers) still need to get some extra functionality implemented to make our and our users’ everyday experience smoother and easier.
Please feel free to let me know if you have better answers to my questions. For the time being I consider myself instead of an opponent, an unconvinced and cautious observer of the recent developments in the BI space, and from this point of view self-service BI seems a bit overhyped. Hopefully it’s me – not the product, what needs refinement.
Boyan Penev on Microsoft BI 
Actually loading data into PowerPivot is expensive task. Problem is that compression is happening on the client – so you have to first load all the records from server to your machine and just then it will be compressed. I did my tests and found that I was able to load about 4mln records per 1 minute.Of course it will depend on your network, how many columns, etc. My tests were on narrow table. So it took me 5min to load 20mln rows. Also when you work with large data sets it takes time to save spreadsheet – it might take 1 min just to save your data. But performance even on my laptop was amazing. PowerPivots are not just for Power users. Maybe power users will create report, but then all users can use it.
LikeLike
Thank you for sharing your experience Vidas. Well, 4mil/min is quite good actually. I should probably test soon with a cube where I have 3-4 million members and a few measures. My concern is that if my cube/database is 200Gb, the PowerPivot file would also be quite large.As for the users – even now the situation is similar – a power user creates reports from a cube and then everyone uses them. If the users need to be even more powerful to create the report, then on the knowledge side they would have to be somewhere between current power-users (who are administering the solutions and using Report Builder with no in-depth knowledge of Analysis Services or relational databases) and me (with such knowledge). Even Report Builder proves to be too hard for some, let alone tackling the complexities around data and extra spicy Excel formulas.I have been thinking on this topic for a while and I can definitely see myself using it for all sorts of things – in example demos, sample reports, and maybe even showing users how changes in their cubes could work (before I actually implement them). Basically, I can imagine myself using PowerPivot where I could not use Excel because of its limitations. However, whether non-IT users would be able to handle it is still an open question for me. I should probably just test it with some and see what they think 🙂
LikeLike