PowerPivot is everything PivotTable is, and everything PivotTable wanted to be. Robust, fast, big data cruncher and Business Inteligence tool.
When one of the most authoritative persons in the world of Excel (Bill Jelen, or MrExcel) says that PowerPivot is the best new feature to happen to Excel in 20 years, you know there is more to it.
PivotTable is with us since 1994 (Excel 5) and it didn’t change much. Well, it didn’t have to, since it was from the beginning one of the most powerful features of Excel (why fixing if it isn’t broken). For the period of 20 years, it was strong enough.
It had an ability to summarize and rearrange inputs, allowing you to choose what and how to view your data. 1 million rows of data (~64k before Excel 2007) was, in most cases, enough space to store tables and input them in PivotTable for further analysis.
But with the technology evolution and exponential growth of data, PivotTable started to show its weakness. One Million rows of data might seem like a lot, but actually, that is a one-day sales records from a single small retailer (if we are to extract store level data per invoice). Besides that, today we do not want to analyze just sales in isolation, we want to see how sales compare to website traffic, air temperature and rainfall, consumer segmentation etc… Maybe you could create that kind of a data model using lots of PivotTables and Index/Match formulas, however, your model would be slow, hard to maintain and less flexible (since you will have to sacrifice functionality to fit tables in 1mil rows).
With the data expansion, the biggest shortcomings of PivotTable became:
- 1 million rows limit
- Table linking is done through Vlookup/Index-Match, which tends to become slow on big models and hard to maintain/adjust
- PivotTable himself starts to crawl when processing large tables (100k rows of data and more)
Also, when doing more advanced modeling, PivotTable functions are quite limited in terms of creating custom calculated fields or measures.
Because of the reasons above, PivotTable started to lose significance within the data population and was used mainly for smaller tasks and lighter data models. Fortunately, Microsoft also saw this, so they brought a new tool to fight against other robust solutions. And with PowerPivot in place, they are ready to combat any BI tool on market.
PowerPivot can connect to almost any kind of data source directly or through PowerQuery. Imported data is no longer limited to 1 mil rows. It is only limited by the available RAM memory of your computer.
When installing PowerPivot (in Excel versions 2010/2013) or newer Excel versions, if you will be dealing with a large amount of data or large files (above 2GB), make sure you install 64bit versions of both Office and PowerPivot to get the maximum performance.
Another big advantage of PowerPivot is speed. With the introduction of PowerPivot, Microsoft also introduced DAX (Data Analysis Expressions) language which is the brain of PowerPivot. DAX uses completely different syntax compared to PivotTable, which is significantly faster, more flexible and much, much stronger in terms of writing custom formulas which can be used across the whole data model. With DAX as a backbone, you can work on tables with over 20mil rows and feel almost instant results of advanced calculations while Slicing, drilling or manipulating your data model.
Connections between tables are done through relationships, which is, again, considerably faster and easier method than using Index-Match formulas across multiple spreadsheets.
Data stored in PowerPivot is highly compressed so beside speed improvement, you also get smaller file size.
With a combination of PowerPivot and PowerView/PowerBI you can create complex and easy to maintain data models, which combine multiple resources into a single dashboard with key KPIs and trends presented (like in pictures below). Implementation is fast and only at a fraction of the cost of other robust BI solutions. And regarding those robust BI solutions, do you know which key is the third most pressed button in any BI solution (after OK and Cancel)? It’s Export to Excel :).
Now you have an opportunity to build powerful data models in Excel/PowerBI, and to evolve your solutions as company needs expand. You have all the tools needed already inside Excel, just explore their possibilities and great ideas and outcomes will come.
Wrap up:
PowerPivot is the main machine driving BI evolution inside Excel/Power BI tools. Power Pivot “evolved” from PivotTable and is everything PivotTable needs to become next-generation BI tool.
The biggest benefits of PowerPivot are:
- Unlimited data size for importing
- Wide range of data sources to connect, either directly or through PowerQuery
- Super-fast and flexible DAX language as a brain of PowerPivot
- Ability to build complex Data Models using relationships
In my opinion, the biggest advantage of using PowerPivot to introduce BI to your company is the fact that it is not so hard to learn and implement. You don’t need an IT company to build a complex solution and teach few lucky ones how to use it. Everyone who understands PivotTables and has a logical approach to solving problems can learn PowerPivot/DAX/PowerBI in short amount of time while having fun doing it.
If you would like to create complex BI solutions based on PowerPivot, you should get more familiar with:
- PowerQuery – Excellent for data transformation before loading to PowerPivot.
- DAX language – I won’t lie to you, DAX is complex and it takes time to digest the main concepts used to write formulas. But it is like learning to drive a bike, once you learn it, it is there to stay.
- PowerView/PowerBI ReportView – presentation layers of PowerPivot. While PowerView is an Excel feature, PowerBI is a standalone product which allows you sharing dashboards/reports online and is optimized for tablets and mobile devices. They both work in the same way, so you can import PowerView into PowerBI and everything will work properly.
If you have any questions, please post them below! And if you enjoyed reading the article, please like and share!
