Finance professionals can feel bombarded with new, expensive reporting and analysis solutions. Every few years, a new BI toolset is in vogue—Business Objects, Microstrategy, Tableau.
Meanwhile, under the radar, Microsoft has been building a powerful and flexible business intelligence toolset. And most of us already own it. In recognition of where data usually ends up being processed and analyzed, Microsoft has built those tools into Microsoft Excel.
PowerPivot, first made available as a free download for Excel 2010, puts the analytic engine developed for SQL Server into the hands of Excel power users. Finance leaders should take note, because these capabilities have the potential to significantly impact the reporting and analysis workstreams—from previously tedious template consolidation processes to data mining on the scale of millions of rows. With PowerPivot, basic BI solutions can be built by finance professionals, instead of waiting for some new system implementation or enhancement.
After being hands-on with PowerPivot and reflecting upon the range of different finance processes, clients, and specific reporting and analysis situations I’ve faced over the last decade, I’ve concluded that these capabilities need to be learned and deployed on a wider scale. The toughest quandaries I’ve seen, the most tedious processes, and the most annoying or complicated workarounds—almost all of them could have been much more easily dealt with if PowerPivot were in my toolbox.
In this article, I’ll discuss the principal benefits of PowerPivot and its related tools and the implications for common problems faced in FP&A and other analytic functions, along with the main reasons more people haven’t heard about PowerPivot and put it to use.
An overview of the benefits
The core capabilities of PowerPivot add database style capabilities to Excel. They can seem like tactical enhancements when reviewed individually, but when taken together, they significantly increase the power of the desktop application.
Connect & merge tables in the background
Anyone who’s ever had to merge and remap datasets in Excel can recognize the tedium involved in the process of pulling data, creating mapping tables, writing and troubleshooting complex formulas (VLOOKUP, INDEX/MATCH, SUMIFS, GETPIVOTDATA, etc.), developing output templates, and responding to last-minute requests for a different slice of the information.
With PowerPivot, all the complex mapping and merging is done in a background data model that lives within the Excel file. Tables are linked together in the same way they would be in a database. And the formulas that read out the data can be roughed in easily by building a pivot table based on that data, then converting the table to formulas.
Robust & easily refreshable data connections
PowerPivot has a wide range of built-in data connection capabilities. For new users who want to experiment, the ability to simply read data from other Excel files and text files is a good place to start.
Connections can also be made to relational databases such as SQL Server, Oracle, and Teradata as well as to SQL Service Analysis Services cubes and Reporting Services report definitions. External syndicated data from Microsoft’s Azure Marketplace can also be linked.
For more complex queries, users can turn to PowerPivot’s sibling component, PowerQuery (renamed to Get & Transform in Excel 2016). PowerQuery provides a step-by-step interface to create custom queries, including queries that stack together data from different sources.
All of these data connections are easily refreshable on demand, so if data changes or new data becomes available, it’s easy to update the file.
Massive dataset handling
For those who are handling multiple millions of rows of data, it’s previously been impossible to process and summarize within Excel—which is a particular problem in the age of big data and data mining.
PowerPivot comes to the rescue with its analytic engine, which (combined with the proper hardware configuration) can process hundreds of millions of rows. This is done without the amount of bloat you might expect in the size of the file, thanks to built-in compression algorithms.
Distinct counts and other formulas
If you’ve ever run an Excel analysis where you needed to derive a count of the unique items within various buckets (such as the unique SKUs sold in various product categories), you’ve probably bumped into a longstanding Excel limitation with no simple workaround: Excel just hasn’t been able to calculate this metric.
But PowerPivot can. In fact, PowerPivot has a wide range of formulas that go well beyond the typical capabilities of Excel. Need to calculate cumulative YTD numbers without adding up individual cells? Need to show just the top 10 products in a Pivot table but have the grand total include everything that’s filtered out of view? Power Pivot can handle these situations.
Dashboard reporting & more
We’ve only covered some of the most obvious, impactful benefits of PowerPivot above. But there is plenty more to discover, including the ability to develop interactive dashboard reports that can be published to SharePoint.
For the analyst, the analytic engine also addresses common annoyances with the standard PivotTable, such as predefining sort orders based on fields that aren’t going to be displayed, or setting default number formats that will be used when a measure is added to a report.
Broader implications for finance leaders & managers
For many finance departments, the Holy Grail of operating conditions is one that minimizes the time spent wrangling data and maximizes the time spent providing value-added analysis and insight.
That dream is often subverted by the slow process of deploying data warehouses and business intelligence applications, which always seem to take longer and deliver less than expected, especially in the early phases.
PowerPivot is not necessarily a substitute for those efforts. But in many cases, it can bridge the gap between the current state and the future vision.
Streamlining manual consolidation processes
Despite the hype and hoopla surrounding planning and consolidation systems, manual consolidation of data remains very common, even as datasets grow larger to accommodate the required levels of detail and various summary slices.
The complications from this are familiar to most experienced finance professionals: each cycle or iteration requires a tedious roll-forward process including re-linking of files. Numbers change unexpectedly because there are live links to the submission files, and someone changed their submission without giving a heads-up. Layers and layers of worksheets and workbooks may be interconnected, making it tedious to change the layout when a new line of business or product category is added to the mix. And so on.
With PowerPivot and PowerQuery, it’s possible to create a consolidation process that is easy to maintain and refresh, along with a data model that is resilient to change. All of the various summary cuts can be pointed at the same, centralized data set; as long as the right attributes are included, these data slices are easy to create.
Achieving holistic summaries across source systems
Similarly, PowerPivot and PowerQuery can work together to create unified metrics for data that is sourced from different systems.
Perhaps North American operational data is in one data warehouse, and International data is in another. Or the portion of your sales that is commissioned lives in Salesforce, while other sales forecasts come in through manual templates. The two different systems may not attach the same attributes and hierarchies, necessitating a remapping of data.
PowerQuery can integrate data from those different sources and then combine them into a single table for unified, holistic analysis. And using PowerPivot, the data can be linked to mapping tables that are easily maintained and updated.
Improving reporting package quality and turnaround time
In situations where all of the data needed for Excel-based reporting can’t be retrieved in a single report or query, with no need to attach different attributes or remap data, finance organizations often end up building their own databases within one or more Excel workbooks.
Making matters even more complicated, it’s easy to end up with a smorgasbord of different tabs and workbooks that are interconnected. Sometimes the worksheets pick up data points from the source Excel “database,” but other tabs may pick up data indirectly from summaries. As a result, we end up with inconsistent numbers in different files, if they aren’t all refreshed in the correct order every time there’s a change.
Using the PowerPivot and PowerQuery capabilities already mentioned above, in combination with the ability to use DAX formulas to easily pull the correct datapoints from a single, centralized data model, it’s possible to ensure that a wide range of summary views are directly updated and consistent. Once tested and confirmed, the significant time and tedium involved in rechecking and crosschecking worksheets can be virtually eliminated.
Building inexpensive proof of concept and interim BI solutions
In many cases, PowerPivot won’t be the ultimate solution for an open reporting or analysis requirement. You may be certain that you’re faced with spending millions of dollars to deploy a solution that is months or years away. One of the most common issues with BI implementations is that requirements are misunderstood, or missed completely, early in the project. This can lead to underwhelming results when the deployment is complete. Sometimes, the problem extends back to the software selection phase, because the specifications given to vendors don’t take certain key use cases into account.
PowerPivot can be leveraged to build out complete proof of concept solutions and processes. Before any capital project spending is even requested, finance and IT can figure out how data should be linked and mapped, what hierarchies are desired, what reports are needed, what types of ad hoc questions must be answered, and more.
In fact, if this work is done well enough, the result can stand in for the ultimate solution.
The hurdles to wider adoption
If the potential business value from this toolset is so great, it’s reasonable to ask why more people aren’t already aware of it. There are a few reasons PowerPivot hasn’t yet taken off like wildfire.
Ongoing rebranding
Microsoft hasn’t done itself any favors with its ongoing rebranding of the toolset. Even now, the PowerPivot name is beginning to be repurposed for a desktop dashboard report building application (PowerBI), while the core analytic engine is now being referred to as the DAX Engine—named for its query language. In Excel 2016, PowerQuery is no longer a free add-on but instead lives on the Data tab of the Ribbon under the much more generic Get & Transform heading.
And while the dataset that lives in the background of Excel is only accessible through the PowerPivot add-in, it is mostly referred to in Excel’s front end simply as the Excel Data Model.
That said, Microsoft is serious about further developing analytic capabilities that are in the hands of end users. Names might change, but PowerPivot’s functionality seems to be here to stay.
SKU configurations & delivery
Knowing whether you’re entitled to use PowerPivot, whether it’s already installed, and if not, how to get it are questions with answers that are confusing to navigate.
PowerPivot is currently included with Excel versions typically licensed to enterprise customers, as well as certain standalone SKUs. If you’re a small business operating on an Office 365 subscription, you would likely need to upgrade users to a difference license tier before the features will be enabled.
Demarcations between finance & IT
It’s not uncommon for early adopters to bump into political issues involving the ownership of data, analysis and reporting technologies. The idea of opening up direct access to a database from Excel, instead of through other reporting tools, can raise legitimate concerns—especially in cases where data security is achieved through the reporting system and not at the database level.
But the core benefits of PowerPivot can be realized just as easily working with exported report data. Direct database connections can be viewed as the second stage of development for a solution.
Learning curve
If you’re lucky enough to have a deeply experienced power analyst on your team with time to spare, that person may be able to pick up PowerPivot with relative ease—but you should allow plenty of time for the learning curve, accompanied by trial and error.
All of those powerful new formulas require learning to write a formula in the DAX language, which has its nuances. Fortunately, the body of books and other support and learning literature is growing as the toolset matures.
But because these capabilities are still relatively new, techniques and best practices are still coming to light, usually as experienced PowerPivot users deploy the software in new ways, continuing to push its boundaries. For any mission-critical application, it would be advisable to leverage the expertise of resources or consultants who’ve already been hands-on with PowerPivot solutions.
Habits & attitudes
Those who’ve been using Excel for years or decades know its core functionality inside and out. We go to it to do certain things and have our workarounds for the gaps, and so it’s easy to notice something new in the menu or documentation and then assume it is a feature that works like VBA macros: potentially powerful but tricky to learn, and even harder to maintain.
When topics such as reporting, analytics, data integration, tables and cubes come up, we naturally assume the answers are in a separate application. We assume a solution will be extremely expensive and delivered in the distant future, and we go back to our way of getting around the gaps using the core functionality that’s been available with Excel for years. The idea that Excel itself could be the solution seems too good to be true. But …
It’s not too good to be true
There’s a good chance you already own a version of Excel that includes these powerful new capabilities. And you may very well have a nagging problem or longstanding inefficiency that PowerPivot can either mostly resolve or completely eliminate. Should your organization take its Excel skills to the next level?
Take the next step
Want to know more about the advanced capabilities of Excel and how they can be leveraged to make an immediate positive impact on your organization? 8020 Consulting can help. Call us at (855) 367-8020, or click the Contact Us button below.
