It all began with Excel 2010. Microsoft offered PowerPivot (now known as Power Pivot) as one of the add-ons in the campaign to improve Excel’s Business Intelligence (BI) offering.
People who are new to Power Pivot are not really clear on what it does and how it relates to Pivot Tables. They often think of it as an alternative to Pivot Tables. This, technically, is not true.
What Power Pivot represents is another way of creating the data set used to generate Pivot Tables.
What does this mean? Pivot Tables can be based on data provided by Excel data sets, Excel tables or Power Pivot.
So which data set should you use? Let’s look at the pros and cons of each.
Pivot Tables based on Excel Data Sets – Pros and Cons
The benefit of using Excel data sets (or tables) is simplicity. You simply click within the data set and create the pivot table.
There are two problems with Excel data sets: size and performance. An Excel data set is limited to the size of an Excel worksheet: about 1,048,000 rows. Actually, it is limited to fewer rows because when you have more than 100,000 rows, your performance slows substantially. Your file becomes super slow and Excel’s automatic calculations start to go a little berserk. It is also difficult to use data from more than one data source.
Pivot Tables based on Power Pivot Data Model – Pros and Cons
After recognizing the limitations of Excel when it comes to analyzing Big Data, Microsoft developed Power Pivot as a solution.
Power Pivot has incredible advantages when it comes to big data. Users can import, merge, and prepare data from multiple data sources at once. Power Pivot features data sets that allow you to have sets of columns visible and usable in all pivot tables since they’re all just a click away.
In addition, large data sets are no problem at all. Power Pivot can handle millions of rows of data.
Another convincing reason for Power Pivot is convenience. Since Power Pivot works with all the data in RAM, even if you lose connectivity or can’t get to your SQL server, you won’t have any problem with data you want to access or present.
There is a significant downside to Power Pivot – the learning curve. Creating a data set using the Power Pivot tool is not as straightforward as a button click, particularly if you are merging data from multiple sources. Fortunately, with the right training mastery is possible.
So, which is better – Excel data sets or the Power Pivot data model?
Anyone who has compared the two options will tell you that Power Pivot is the superior choice.
There is a lot to learn with Power Pivot, but if you are wondering whether you should make the switch, the ability to work on millions of rows of data and with multiple sources all at once should give you enough reason.
