PowerPivot: Poor Man’s BI Solution or Analyst’s Wonder Tool?
September 17, 2012 Leave a comment
What if Microsoft built something really cool and forgot to tell anyone? Well, in late 2009 they released a beta for an Excel add-in called PowerPivot. Back then, I was facing a particularly challenging data problem trying to divine useful intelligence out of millions of lines of cryptic website click data in the world’s most ubiquitous analysis tool, Excel. It was working, but, it was painful. One of the Business Intelligence experts in the group (that seemingly knew everything about everything related BI) pointed me towards the PowerPivot beta with the caution that he had “no idea what it did”. It turned 10 hours of work into 10 minutes.
You’ve probably used a PivotTable report in Excel to aggregate, slice and dice data both locally and connected to databases and cubes. PivotTables are one of Excel’s most powerful features. After working with PowerPivot for over 2 years now, I’d say it’s badly named. PowerPivot isn’t an enhancement to the PivotTable functionality. Don’t think of PowerPivot as being like a supercharger that increases a car’s engine power but fundamentally leaves the car unchanged. Think of PowerPivot as something more like a Transformers’ Autobot. PowerPivot converts Excel into a powerful BI tool that a sophisticated Excel user can easily learn to use and solve complex data problems that previously took database expertise.
Take Data from Anywhere and Everywhere
For me, Excel now has three components. In addition to being a spreadsheet, PowerPivot turns Excel into a data warehouse. While you have always been able to connect Excel to databases, you could only really work with one set of data at a time. (With vlookups and other functionality, you could force Excel to act like a database but it still wasn’t really one.) With PowerPivot, wherever the data resides in servers around the world, locally on your machine or in the cloud, and whatever the technology, databases from Microsoft and competitors such as Oracle, SAP, or IBM, Access, other Excel files, text files or even SharePoint lists, they can all be connected to PowerPivot. You can easily combine data stored on an Oracle database in Asia with data from Access on local servers, Excel spreadsheets on your local machine and data stored in SQL Azure, Microsoft’s cloud based database. While the spreadsheet of Excel has a 1 million row limitation, PowerPivot has no pre-set limitation. I’ve seen millions and millions of rows of data brought into PowerPivot, and it worked. It also has no fixed limit on the number of connections or distinct data sets you can import. PowerPivot comes with a powerful data compression engine and can handle more data than you can believe and rather than breaking it tends to slow down.
Once the data is in the “warehouse” the powers of PowerPivot reveal themselves. The same way a SQL user joins two tables in a database, similar columns in different data sets allow a relationship to be formed and Excel treats the data as a single data set. Imagine combining data from any of the systems in your company without months of development time. How fast is PowerPivot? In the time it has taken you to read this blog I could easily connect to two data sources, form a relationship between the data sets and start working with the data.
Combine it Together in a Cube on Your Desktop
The third component of Excel is a multidimensional database or cube that comes into existence once the relationships are formed. A full explanation of multidimensional databases is beyond this blog, but for clarification they allow non-technical users to access data consolidated from many different data sources without having to learn a computer language or rely on IT to provide reporting.
Change, Modify and Manipulate it with a Computer Language you don’t need a CS Degree to Use
When your IT team works with data they use SQL, the Structure Query Language to combine and manipulate data, sort and filter, perform calculations and other tasks. For PowerPivot to be a really powerful tool, it needed a way for less technical users to perform similar tasks. Microsoft made life easy by building the new Data Analysis Expressions language, (known as DAX) on a foundation of the functions used in Excel. If you’ve ever written an IF statement, a vlookup or similar statement in Excel, DAX will look very familiar, but it’s much more powerful. To do nearly anything that can be done with SQL, DAX expands the language substantially but it’s still accessible to non-technical users.
Become of Member of your Own BI Team
I’ve lost countless hours and plenty of hair, both of which I can never recover, trying to sleuth my way through layers of hidden sheets and columns, VB script, macros, vlookups, getpivot, embedded IF statements and other complex formulas trying to understand what some complex Excel spreadsheet was actually doing so I could trust the results. I love the transparency of PowerPivot, you can see the data, you can easily follow through the calculations and quickly become comfortable with the answers even a complex spreadsheet produces. For me, PowerPivot is an amazingly powerful wonder tool…and did I mention that it’s a free add-in? Microsoft recently released V2 and you can download it here. If you are a strong Excel user with some limited database skills it will turn you into a creator of business intelligence very quickly.
The downside to PowerPivot is that data and databases are much more complicated than a novice can imagine. So be prepared to start learning more about the data you work with. PowerPivot was one of those bridges that took me from being a sophisticated user of data to a member of Slalom’s Business Intelligence team. I don’t think I would recommend a PowerPivot as a permanent solution to a global enterprise reporting need, there are more robust tools for that. But PowerPivot is fantastic for replacing complex models that combine data from many sources and use macros and vlookups and all manner of complex formulas in Excel. PowerPivot will do it better.
Not Just a Solution but a Tool to Help you Build Other Complex Solutions
PowerPivot is also perfect for building reporting or business decision proof of concepts. In a fraction of time it takes to build an enterprise level solution, months instead of years, you can acquire data from many source systems, create complex solutions to test drive the future solution and then, you can rely the PowerPivot solution until the full solution launches. And maybe the biggest benefit, PowerPivot is so dynamic, it easily becomes part of the business requirements gathering process. Instead of lengthy interviews to capture what people think they want only to find out you misunderstood them, you can begin building a model, show them what you heard and iterate. PowerPivot shouldn’t be thought of as a replacement for more robust BI systems or your IT department but can be a powerful bridge to enhance your reporting capabilities.