Recently I have been playing around with the new features within SQL Server 2008 R2 on my BI demo machine. One of these have been the much touted PowerPivot to enable Microsoft’s much publicized strategy for enabling self-service business intelligence.
There are essentially two parts to PowerPivot the excel client which is the authoring tool to create PowerPivot applications and Powerpivot through SharePoint2010 which allows the PowerPivot application to be shared on a portal and the data set being consumed through other reporting tools such as PerformancePoint services and Reporting Services.
Here are some screenshots
Initially when I heard about the Self-service BI strategy from Microsoft I have to admit that I was a little skeptical in the strategy as most of us in the Data warehousing profession are striving to have a single source of truth and to capture important business logic within a central source and to remove this important business logic from documents such as Excel.
Having played around with it for a little while and also talking to some heavy excel users, it has dispelled my skepticism on the product and I really think that it is a powerful tool for business users when coupled with the rest of the Microsoft BI Suite of products.
So why the change of mind? Well there are a few reasons which I have experiened with my dabbles with the product:
- PowerPivot adds value to your existing initial BI investment: With hundreds of SSRS reports out there in the organization wilderness all interfacing cubes and the data warehouse a lot of users still ask ‘If only I can get information from report xyz and join it to abc report CEO’.Traditionally this would mean a new change request in which the IT division will need to write new reports to cater for this request adding to the hundreds of reports already available. Not only this costs money but more importantly wastes time and in my opinion not very efficient use of resources.With PowerPivot this can be addressed. Coupled with exposing SSRS reports through Atom feeds and assigning relationships on the data sets within the PowerPivot application users are able to address these queries themselves leaving IT to get on with new projects.
- POC to demonstrate and convince the power of BI: There are some organizations that are still not willing to invest heavily into BI, and are relying on spreadsheet technology within their teams to conduct performance monitoring on their organization. This might sound ridiculous but it happens. Thus PowerPivot enables the end-user within these organizations to build BI applications themselves to use and share with their colleagues but to also demonstrate the power of BI in terms of time saved in building accurate reports, and the value provided when information is provided from a single source.
- Vlookup nightmare: This is not a specific BI issues but having worked with many excel users in the past, one of the things they do a lot of is use Vlookup in order to match data across spreadsheets or datasets. Well with the use of data relationships a lot of these unnecessary vlookup’s are removed as they are able to ‘connect the dots’ through explicitly building relationships, and then filtering on the total data set.
As a wrap up PowerPivot is a very powerful tool which enables users to get access to information that they already know, in addition I really feel that it doesn’t take away the importance of a BI strategy that is already inplace, but actually adds to it if used in the correct mannar.
Thats my $0.02 let me know of your experiences.
Some good links on PowerPivot are the following:
Microsoft’s technical explanation of PowerPivot
PowerPivot Excel add-in download
PowerPivot – Sample data
PowerPivot – Creating your first PowerPivot application Tutorial