PowerPivot and DAX papers,posts,FAQs,webcasts,samples

A remark I hear a lot on my PowerPivot crusade is why should we build a DWH or SSAS cubes when you have PowerPivot.

I would like to use this forum to discuss the why with you guys and hear your thoughts.

My answer currently is (not in order of importance although it maybe should be):

PowerPivot doesn't support security, it's only access yes or no. Use SSAS to give your uses only the data they have access to.

The world is a difficult place, some data isn't that easy to join. A key isn't always available that lets you join data from different sources

Using dwh / cubes you can maintain dataquality, you are in control of your single version of the truth. Using ETL you have a lot of option to join them.

PowerPivot runs in memory, when you use PowerPivot for all your enterprise BI you would need a huge machine

Using your PowerPivot workbooks as datasource for reports / dashboard would give you enormous version management issues.

SSAS lets you tune your cube, make it fast by looking at the usage

Use PowerPivot to answer a ad-hoc question or to play with your data

I'm very sure there are many more reasons to keep using a DWH / traditional bi instead of PowerPivot, post them here!

Kasper,

This is very interesting. I make my money from being BI consultant and I have to keep track where market is going and decide if I should ignore new technology or learn it as fast as I can. I spend a lot of times thinking about this.
When I heard about PowerPivot - I was scared that this is going to replace SSAS. When I tried PowerPivot with CTP2, I was scared even more - it worked fast and it was pleasure to use. When I found that in SQL Server 2008 R2 there will be no changes to SSAS, I understood that Microsoft committed all SSAS team to PowerPivot, so I wondered what is the future for SSAS?

But after I had some more time to play with PowerPivot, I think SSAS and PowerPivot will be used side by side for at least quite some time. I now believe that PowerPivot cannot replace SSAS at this point. Maybe PowerPivot 3.0+ will be able to do all these things that SSAS does, but at this point there are just to many limitations in PowerPivot to build Enterprise Level data warehouses.

I agree with your list.I would like to add some issues:

- Bigger companies has lots of data. Transferring that data to workstations for analysis would might clog networks. Especially when there is limited network connectivity between servers and workstations. Even worse when data is stored somewhere outside (somewhere in the cloud). It takes time to load data from server to workstation. So far my experience with PowerPivot was pleasant as it works very fast. But loading data from any source to PowerPivot is clearly a limitation - it takes sometimes long time. So in the real life this will limit that PowerPivot will be used with reasonably small amounts of data.

- IT looses control of data as soon as it is saved on workstation. There might be huge security risks for that. SharePoint implementation does not really help here, as user first have to download data to workstation and then publish to SharePoint.

- PowerPivot for SharePoint requires EE license and SQL Server 2008R2 EE license. That cost a lot of money. This will slow down PowerPivot+SharePoint implementations. PowerPivot without SharePoint is even bigger Excel Hell and now it will be "Excel + Data Hell"

- Without SharePoint there are many limits how PowerPivots can be shared between company employees. What is somebody has 8GB of RAM and was able to build bigger PowerPivot application. Then somebody with 4GB of RAM might not be able to open that application. Without SharePoint how would you refresh data for application that you received from somebody else. I wish Microsoft would easy PowerPivot+SharePoint requirement and would allow to use cheap SharePoint version with certain limitations.

- It is nice that SSAS has cube scripts where you can put your company calculations rules into a single place. Without that, there will be multiple places where you will have to create calculations. For SSAS calculations are reviewed and approved by business people. Who will review/approve multiple calculations in PowerPivot applications?

- Data refresh is too complicated in current PowerPivot release. And clearly there is a need to implement "Data Add" type of refresh, as transferring all data all the time is just to expensive.

So I think that PowerPivot is great for certain applications, but for any Enterprise level data warehouse implementation SSAS is the way to go. I do think that this might change in the future though. I think that years from know PowerPivot can become tool for building any data warehouse.

These are the things I like about PowerPivot:
- DAX language is much easier to learn that MDX.
- I like this design approach when you are dealing with real data and not with data structures. That is in PowerPivot during design you see actual data not just field names. You see results as soon as you add any calculations. You are also "profiling" data at design time and I found it to be very useful when working on database structures that you are not very familiar with.
- I like the speed of working with data AFTER it is loaded into PowerPivot.


I'll try to think more things about PowerPivot and will post them here.

Hi Kasper and Vidas

I think that you have covered this topic well. I completely agree with what you have both said.

Some additional reasons why you would potentially need a DWH could be that in some instances the data is not in a format that could be read using PowerPivot.

I have had one such example where the data used for a BI project was mostly retrieved directly from the telecommunication switches that one of the telecoms providers used and required some custom .Net in SSIS to be pulled into the system.

I agree that most data needs to be cleaned properly and could also require considerable amount of business rules applied to it in some circumstances. I would definitely prefer that all business rules and logic is maintained in a central place as Vidas have said. Without that you would end up with the exact same excel chaos that most organizations find themselves in and you would lose the “one version of the truth” and the credibility of your system.

The last think I was thinking was regarding the amount of memory that would be required if say a 1TB data warehouse would be rebuilt using power pivot. I know that you would not follow the same design principles and that the system would be much more disparate but in the end you would still be trying to accommodate a huge amount of data. I posed this question to another vendor of an in-memory analytics tool who seemed to not realize that some large enterprise DWHs could grow by many terabytes in a year. The implications that this would have to the hardware costs could well be e deterrent to moving fully to in-memory analytics at the present time.

I think that PowerPivot is a brilliant tool to allow users to do further analysis and discovery of the data that is available in the DWH and add to this data that was not originally included or fell outside of the scope of the DWH project. I can see it being used side by side with SSAS as Vidas have said and possibly in the future start to evolve into a tool that could replace the DWH and SSAS cubes.

Christo - very good points. I totally agree with reason when data is not in a format that could be used by PowerPivot. Here is another simple example for that. I found data online about all stock price history. Problem was - that these stock prices where split into files based on stock ticker symbol first letter. In total there were 220 files. There was no way for me to load that data directly into PowerPivot. I had to write SSIS package to read all files, load them into SQL Server and then load that table into PowerPivot. So there is no way I could have loaded this into PowerPivot.
I tried to find workaround for that and I did - but it looked like "hack", so I would not consider this for any reasonable implementation. I loaded each source table into separate table and then created calculation where stock price was sum of all stock prices from all tables. As one stock could be just in one table, this kind of worked, but introduced to many other issues, so I just decided not to use this.

Clearly - PowerPivot needs partitioning strategy that would allow to do faster data refresh and for this situation would allow to load data into the same table from different sources.

You all made very good points.
In my opinion (and in my experience) the big challenge is data quality. Which is not only having good data without errors (that is just an utopia, anyway...) but also having data in a good shape for analysis.
ETL is fundamental to apply trasformation logic. Something that PowerPivot does not have.

In my opinion, PowerPivot is a very advanced reporting tool, making able to create simple models that relates tables coming from different sources, once you already have trusted sources of data.

Marco