Earlier this year a colleague of mine and I were called to a Portuguese Insurance Company to develop a POC (Proof of Concept) with PowerPivot.
The challenge was to prove that PowerPivot could do the same or even better job than Access and Excel combined.
The scenario was simple: an Access Database and an Excel sheet as sources, an excel workbook as destination, powered with VBA to show information about the economic welfare and accident rate of the company. The main purpose of this project was to integrate these reports on SharePoint. The big problem here was the impossibility of integrating VBA in SharePoint, so to maintain the ease of use and low cost of development, the solution was to migrate to PowerPivot.
As you can integrate data from almost every source available in the market using PowerPivot, we choose to retrieve the data from the existing corporate data warehouse deployed in SQL Server 2008 instead of using the Access Database as a source. The excel sheet as a source continued to exist.
After connecting to the data sources and bringing the data to PowerPivot, the next step was the creation of a data model to supply all the measures and descriptions to the reports (Pivot Tables) by creating the necessary relationships between tables. We used some DAX formulas to create the needed measures, most of them were straightforward like the accident count and some rates, but we also had to include some Time Intelligence functions to provide, among others, the ability to calculate the year to date and previous year results of several other measures.
In order to maintain the same layout as the original reports, we also used Cube Functions (CUBERANKEDMEMBER, CUBEVALUE, CUBESET…) to retrieve the value of selected slicers (filters) to include in the title of the report or to retrieve the values of the pivot table to create a table with the original predefined layout. These functions were very important in some reports which had more complexity.
The two weeks POC was a success and our client was very happy with the final result. We were able to prove that PowerPivot is indeed a very powerful tool and an excellent option for the migration of old reports with little effort. PowerPivot is also a powerful prototyping tool which you can use to create simple (or not so simple) models to provide your clients with an overview of what is intended with your BI project.
