With PowerPivot, Microsoft wants to give business users the power to create their own reporting models based on data from several available sources. Giving this power to business users will enable them to create reports on top of a model created by them, at the moment they need it. Of course a small warning is needed. We don’t want to go back to the era of Excel hell where users were collecting data themselves in Excel and created reports on top of that data. Enabling business users is what we all try to do with Business Intelligence, but in that process component as data quality, consistency and source validation are key. So PowerPivot is a great tool that can help the business user when he needs to combine data from the data warehouse with a set of external data, or as a starting point to look at data as a preparation for adding it as information to the data warehouse.
PowerPivot consists of 2 components:
- The PowerPivot client is an application that is closely linked to Excel, but it isn’t actually part of Excel, it’s an add-in to Excel. This has several advantages; not all users will automatically get the add-in when installing Excel. PowerPivot and Excel have their own life cycles, so PowerPivot can launch a new version without having to wait for a new version of Excel. There is however a small limitation for the first version of the add-in, it will need the new Excel 2010 to work.
- With the PowerPivot add-in you will actually create the report model by adding the necessary table and relationships, load all information and also create the reports. When you have loaded the information the data is loaded in the new in-memory Vertipaq engine. This will make it possible to create highly interactive and fast reports even if they are based on tables consisting of hundreds of thousands of rows.
PowerPivot server is an application that is added to the SharePoint 2010 environment. With the server component you are able to publish the model created in Excel with the PowerPivot client to SharePoint. This way, others can use the model to create their own reports. When centrally stored it is also possible to refresh the data automatically provided that the source data is available when the model author is not online.
Contact us for more information on PowerPivot.