PowerPivot and its coding language, Data Analysis Expression (DAX), are a middle ground between total automation and strung-together Excel sheets with enough VLOOKUPs, formatting and outside links to make it crash on open every time. Together, PowerPivot and DAX form the data-crunching mechanism in what has become known as Power BI. PowerPivot models data from and spreads logic over disparate data sources, which render results in pivot tables, visualizations and other types of reporting with significantly fewer formulas. Where we once relied on sophisticated array formulas, VBA, MDX and other specialized knowledge, PowerPivot brings business intelligence to the people in a way that simultaneously marches towards automation, but still gives us control.
PowerPivot is not only for business and finance purposes; it can be used for other quantitative and qualitative applications—imagination is the only limiting factor. PowerPivot does, however, play well with finance since it’s a numbers-based, data-driven science. The finance-related PowerPivot applications are numerous, from creating production-ready reports to calculating KPIs to building forecasting models and much more.
And this power is found in—yes, you guess it—Excel. It’s also in Power BI Desktop (PBI Desktop), but Excel is more versatile for many finance-related applications. First, most of us are used to Excel, which is an often-underestimated benefit. Second, Excel allows us to build front-end Excel input (or model) that can be digested in PowerPivot and mashed-up with other data sources. PowerPivot—in Excel and Power BI—communicates with its enterprise-level big sister SQL Server Analysis Services Tabular (SSAS Tabular). Both Excel and PBI Desktop are fine tools, but I prefer Excel for these reasons.
WHERE TO BEGIN: A MINDSHIFT
Using PowerPivot is not always easy, but the investment pays off. Using PowerPivot forces an understanding of the true nature of data, which is different than traditional report and analysis writing. Recognizing pivoted versus unpivoted data is crucial. Though pivoted data can be loaded into PowerPivot, it will force some bad habits. PowerPivot, in contrast, wants you to load unpivoted data to it. An example of pivoted data would be a column for every month, versus one “month” column, which is unpivoted. If a data source is pivoted, there are ways to handle that with the ETL tool Power Query, which is also built into Excel and Power BI Desktop. After creating an unpivoted data source, understanding fact and dimension data is essential.
Facts tables contain amounts, whereas dimension tables contain attributes about your facts. For example, if fact data has an account number, it’s often necessary to have further information about accounts (e.g. is it revenue or expense), which would be housed in the dimension table. This will become more important later in the data modeling and DAX writing example. If the revenue and expense attributes are buried in the same table as your facts, that means facts and dimensions need to be separated, creating multiple tables. In the SQL world, these are called views. After mastering unpivoted fact and dimensional data, relationships of those data are the next essential concept.
Data modeling in PowerPivot thrives on one-to-many relationships, where the “one” side is dimensional data and the “many” sides are facts. Dimensional data is a list of unique values, like account numbers. Account numbers (or an equivalent key) are also found in the fact table, but they are listed many times, maybe once per month over many months. Once this one-to-many relationship is created, then all the attributional data about the account becomes available for DAX calculations and slicing-and-dicing in pivot tables and visualizations. If the account is available in another fact table, a second set of facts can be modeled in. The payoff: Imagine a table with facts about monthly actuals and one with monthly budget both joined on account. DAX can now be utilized to write business logic across these two tables via the account dimension.
DAX’s syntax is comparable to Excel, making it easy for Excel users to learn. The learning curve is akin to that of learning a musical instrument—after developing some muscle memory, the creativity comes pouring out. DAX formulas are applied across large swaths of data using a combination of filtering and calculation. Because formulas are applied across a dataset, there are much fewer DAX formulas than you would have in the comparable Excel model. For example, if you have a report that spans 24 months, a calculation in Excel is likely repeated 24 times. In DAX, that formula can be written once and spread across 24 months via the date relationship created in the calendar table. Not only does this significantly decrease user error and maintenance, but it also makes the analysis replicable across a new set of 24 months relatively easily.
POWERPIVOT APPLICATIONS FOR FINANCE
PowerPivot and DAX can be used for everything from ad-hoc analysis to an enterprise-level business intelligence system. They can be used to create both exploratory and explanatory analysis through pivot tables, visualizations and even SSRS reports. Profit & loss statements, balance sheets, cash flow projections, KPI development, forecasting models, and sensitivity analysis are just examples of what is possible using PowerPivot and DAX.
The enterprise-level solution, SSAS Tabular, looks and feels the same way the interface does in Excel. This means that, assuming access to a SSAS Tabular instance, the server looks and feels the same. There is a lot more capability in SSAS Tabular than in Excel or Power BI Desktop, but the ability to understand data models and DAX is the same in each of these tools. Moreover, a solution can be developed in Excel and then upscaled to SSAS Tabular, which gives new meaning to working with an SQL expert. It’s a more synergistic relationships because PowerPivot and DAX “meet in the middle” on coding business logic. Once a solution is on SSAS Tabular, it can be accessed through a connection in Excel, Power BI Desktop, SSRS reports—a truly versatile enterprise-level solution.
Matthew Mowrey is senior director, finance, analytics, and business performance for DAI.
AFP 2019 has multiple sessions on Excel in the FP&A track. View the conference brochure here.