Online Analytical Processing (OLAP) is a technology that is used to organize large business databases and support business intelligence. More simply, in Excel you can convert your EBM cube to OLAP formulas in order to format tables in a way that's more customized than a standard pivot table and create a reference input for other sheets in your report.
This article contains the following topics:
- Why use OLAP formulas
- How to convert a cube to OLAP formulas
- Types of formulas
- What is Cubemember
- What is Cubevalue
- Use-case example
What are the advantages of OLAP formulas?
- The most common reason to use OLAP formulas in your reporting is to create a more finished, refined, or custom report than one could achieve with pivot tables alone. You would only use OLAP for reports that won't change in their general structure.
- For example, you wouldn't use this when reporting on something like Customers, because Customers will regularly change over time. Rather, you'd probably only use this for standard P&L and Balance Sheet reporting.
- The main benefit of using OLAP formulas is in overcoming the inherent challenges presented in pivot table formatting limitations. Because pivot tables have restrictions on how columns and rows are formatted and arranged, OLAP can become a useful tool when you want to break free from the traditional view.
How to convert to OLAP?
Converting a pivot table cube to OLAP formulas is easy, but you'll first want to build out your view in the pivot table first so that your formulas make sense once converted. A good example of this is creating a balance sheet or an income statement and arranging your fields and filters in a way that generally makes sense.
Here's a simple example:
Once you've built out your basic report in a pivot table, you're now ready to convert it to OLAP formulas.
- Create a copy of your pivot table, either in the same sheet or in a new sheet. As an option, you can make a copy of the PivotTable worksheet by pressing the Ctrl key while dragging the PivotTable's worksheet tab to create a copy of the PivotTable. This allows you to both retain your original PivotTable and create a formula-based version of that report from the PivotTable copy, as shown below.
- Click anywhere in the pivot table.
- Convert to OLAP formulas by toggling to PivotTable Analyze > OLAP Tools > Convert to Formulas.
Hot Tip: You can use the keyboard shortcut to quickly convert to OLAP formulas: ALT + JTOC
- Check the box to Convert Report Filters. Click Convert.
- Done! You now have formulas for each field of your pivot table. Now that you have formulas associated with each field or filter (e.g. 2020 Actuals, Cases, Gross Sales, etc.) you can then reference these elsewhere. You can now also reformat your table to customize it in anyway that helps you consume the data easier.
Here's what that same example looks like after being converted to OLAP formulas:
Once you convert your pivot table to formulas you'll notice there are two primary formula types present. Cubemember formulas and Cubevalue formulas.
When you see "CUBEMEMBER" in a formula, it means it is referencing either a Filter, Row, or Column field from the pivot table. Some examples, might be Scenario, Account Level, Company, Month, etc.
When you see "CUBEVALUE" in a formula, it means it is referencing a value field, such as Activity, Ending Balances, or Calculations. Think of these formulas as the actual numerical values for their respective cubemembers.
How do I put this into practice?
A common use-case for using OLAP formulas is in creating a reference or inputs tab in your workbook, which you can then reference in other sheets.
Building out an inputs tab (above) gives you the freedom to utilize all the formatting and formulaic tools that Excel offers, so you can create really dynamic reports that still refresh to the EBM data lake.