PowerPivot and BI architecture
Posted by thomasivarssonmalmo on January 31, 2010
PowerPivot is the new Excel 2010 add in and a server application in Sharepoint 2010. What will PowerPivot change in the existing BI architecture or in the everyday life of existing BI-solutions built on the MS-BI platform?
Amir Netz suggested the anology of the freedom of the Blogs compared to the newpapers and tv-channels views of the world for the role of PowerPivot in a BI solution, in a discussion on Linked In. I guess that he means that the end users will be able to build their own analytical subspaces of their organizations information stores. He also agreed on my assumption that PowerPivot will not replace the centralized data warehouse vision. My personal experience from working in a fairly large company with a centralized data warehouse is also that changes takes far to long time to implement. The time horizon from a requirement to an implementation of that requirement can take 6-8 months. At the same time the requirement for reporting will change much faster that that and put a lot of pressure on the end users.
Before I tested the first CTP of PowerPivot I only had a few demos on YouTube and some texts describing the main features of PowerPivot. One vision that I had at that time was that the end user should be able to import data from different data sources and then move data to appropriate PowerPivot tables. If you have a large text file with dimension information and measures you should be able to split that import table into different dimensions and a fact table but this will probably not be possible in the first release. Still you have some functionality in the first release if you import that information in to Excel spreadsheets and then build linked tables into PowerPivot. The problem is only that you will have to accept the one million record limit of an Excel spreadshet.
If you have several datasources like your transaction data in SAP, AS400/DB2, Axapta, your financial data in separate systems, your budgets and forecasts in Cognos planning, CRM data in CRM-systems and your organizational structures in a separate tailor made system, can PowerPivot be of any use in this scenario? I do not think so. If you have a small business and one main data source that can export text files PowerPivot will work. PowerPivot can also connect directly to the transactional data source but then it will assume some knowledge from the end user of what tables that are important. That is seldom the fact.
With many different data sources you will never be able to do any useful with only PowerPivot as the tool. With one data source with a lot of tables, you will need great knowledge of that data model to find the relevant tables.
My conclusion is that PowerPivot will be most useful as a client on top of a data warehouse or a datamart with cleaned data and centrally defined measures and dimensions. With PowerPivot end users, with knowledge of the starschema data model can bypass the development and change requests of SSAS cubes. But I think that this scenario assumes a skilled end user that have some knowledge of data models. It will also be possible to combine information from several data marts but this assumes an even more advanced end user.
Summary: I do not think that PowerPivot will be more than a new powerful client in a BI-perspective.
Finally, What will be the main benefit of PowerPivot on top of one or several data marts?
First: Combining leaf levels of several large dimensions will be really fast with large fact tables. This is the weak spot of the current version of SSAS 2008. Import data from the data mart source of an SSAS cube and avoid doing that directly from a SSAS cube. PowerPivot works with both SSAS 2008 and SSAS 2008 R2 cubes.
Second: Another great benefit is that the end users will have an off-line data store within Excel 2010 that is both fast and compress data from the data source.
Third: I have not tested this yet but I hope that PowerPivot will not have the limit of the current SSRS 2008 export of 65 thousand records to Excel 2007 will be gone with SQL Server 2008 R2 and Excel 2010 PowerPivot.
What is the main weakness of the first version: The sharing of calculations and business logic that you have in Analysis Services. You will have to add the same calculations over and over since they are limited by the PowerPivot store in each Excel sheet.
I will return with my analysis of the integration between the PowerPivot client and the Sharepoint 2010 server application part of PowerPivot.