Boyan Penev on Microsoft BI

OK, my doubts aside, this is my first post about PowerPivot. Obviously the demand is high and the perspectives bright – so here we go…

There are a number of posts about using PowerPivot as a datasource in PerformancePoint and Reporting Services. You can find two links below (both at TechNet) and I will not repeat the content there:

In example, today I was working on a small demo of PowerPivot and I created a PowerPivot report integrating a relational data source and an OLAP data source from two completely different systems, which then got related to each other by State/Province . After that, I created a DAX ratio measure, which showed some relation between the Sales measures from each database. After publishing the workbook to SharePoint, I opened Report Builder 3.0 and to my surprise (well, I expected to see only physical measures for some reason), I was able to pull the DAX measure which I had just created and beautify the map of the USA based on its value.

The integration between the various components in the Microsoft BI stack is continually improving and with the latest few versions of the various components (labelled 2010 or R2), we are being empowered with richer and more powerful tools, covering a larger array of users – proving that the toolset is the best out there for both enterprise-level and relatively smaller customers.

PowerPivot DAX, PowerPivot

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.

PowerPivot PowerPivot, self-service BI