Dynamic what-if analysis in BI Office

Enabling user-driven advanced what-if calculations

One of the most demanding exercises in business analytics is a what-if analysis, as there are not many engines that can translate end-user input model into results without requiring a designer to write code or program an application. The goal seek or solver function in Excel, for example, can be very useful for such a task, but it lacks the capabilities to operate within a database environment – which is vastly more complex and larger than spreadsheets in scope and depth.

All too often, you’re required to pull out the software development kit (“sdk”) for your BI package to get this functionality – which means weeks and months of programming and development. Alternatively, you attempt to use some type of “write-back” functionality where users can ‘model’ outcomes using simplistic aggregates. The write-back generally only works on super-light data structures, because its dead slow if it gets big and complex. Not to mention it doesn’t work when there are complex data model calculations.

In this blog, I’ll show you how to build a simple, yet elegant what-if capability through a point-and-click interface which:

  • you can deliver in minutes
  • has an ultra-simple, yet effective end-user interface
  • produces super-fast performance regardless of the database size
  • delivers sophisticated mathematical modeling capabilities without programming or complex scripts and no write-back

The Scenario

Your sales team wants to know how sensitive your sales metrics are to changes in the underlying sales trends. The metrics here include margin and profit-per-item: all measures that are based in part on the underlying sales figures.

Dynamic What If example

[In the next blog on this topic, we’ll expand the complexity of this problem by only running changes to sales in a specific region and observing the impact on the most profitable products.]

The Problem(s)

Write Back

If you wanted to achieve the above scenario with a classic write-back approach, the end user would need to key in the alternative sales figures needed through the UI. Several problems might exist:

  • If the underlying cube/data model is very large, the write-back process would become incredibly slow (changes are typically written down to the lowest grain of the data model). So the model would need to remain small and tight and may not reflect the reality of your data. In our sample sales cube, there are more than 20 hierarchies, and the grain may represent millions of combinations and transactions if the changes were entered at a high level.
  • If the user wanted to change a calculated metric (like margin) instead of a base measure (like sales), the write-back methodology would either fail (you cannot overwrite calculated measures) or produce strange numerical results.

The net result: write back is rarely an effective solution.


If we were going to script the change, we’d need a tool that would give the developer a way to wire up a UI control for making value changes and then either: have that value injected into the query to produce a different outcome, or, worse, something to manipulate the query results in the client to show the adjusted values.

Either way: it sounds complicated!

The Solution

Pyramid Analytics’ BI Office Data Discovery toolset includes all the tools and widgets needed to accomplish this advanced task in a point-and-click interface that will be a breeze for BI designers to implement in a few minutes. End-users will have tight control over the what-if scenario modeling and at some point, they themselves will be able to generate their own modeling scenarios – a.k.a. “Self-service BI”.

To achieve this, BI Office exposes the concept of “Cell Overlays” and “Variables”.

  • Cell Overlays can be used to manipulate the cells of a data model with alternative values to achieve a particular outcome. This is the perfect vehicle for adjusting models to see what would happen if you changed something somewhere else: “what-if”
  • Variables are UI tools that will allow end-users to effectively inject static values into calculations, queries and equations by tweaking knobs, sliders and buttons. Variables are like having an algebraic formula where the user can tweak a knob and replace “x” with a new value in the formula.

By combining these two features, setting up a dynamic what-if analysis is a short exercise. All the while, the shared functions, logic and analytics can be managed centrally in a governed framework.


We want to manipulate the sales numbers in the data model behind the grid below to see the impact on net profit, net profit per item, net margin and margin for each period. Using this technique, we want to discover the lowest reduction in sales we can tolerate before our margins go negative.

Dynamic What If example

We start by clicking on the Advanced Calculation Designer (“ACD”) wizard button and choosing the Cell Overlay function.

Dynamic What If example

In the wizard we select the “Scope” tab. "Scope" represents the set of cells or values in the data model we want to change using the overlay equation. In this case, we want to change any cells that use the “sales” measure. So we pick sales as one of the targets from the measures hierarchy.

Dynamic What If example

(The scope can be made more focused by choosing more or different elements from the other hierarchies as well).

Next we want to define the formula for how to change the cells specified in the scope. We’ll go to the “Formulation” tab to create this equation. The formulation can be:

  • A static number to create a hard overwrite effect (like '1000' to make all sales figures set to 1000).
  • Or a logical formula that can generate intelligent values based on logic or other values in the data model.

In this case, we will set the value of our scoped cells to the sales value itself multiplied by an amount. We want the end user to tweak this amount from the reporting interface.

We start by adding a “data point” to inject the logic that will pluck values from the data model dynamically and put it into the equation. (A “data point” is another name for “cell” or a “tuple”).

So now we need to tell the data point function that it needs to be set to the “sales” value itself from the measures hierarchy.

Dynamic What If example

Next we amend the formulation to be the sales data point from above, multiplied by a numeric value. Because we want this be dynamically set by the end-user we use the variable capability to create a variable UI component and create a range of values that will be injected into this formula. So now we’ll add a variable to the equation box and then click "New Variable" to create the variable options.

Set the variable as a percentage slider with a default of "1", minimum of 0.5, maximum of 1.5 and increments of 0.1. This will allow the end users to tweak sales by a range of 50-150% in steps of 10%.

Dynamic What If example

Now we’ll give a name to our cell overlay – “Overhead Change” – and click OK to save it.

We are then presented with the list below. Here we’ll check the checkbox to activate our new cell overlay logic in the analysis. The same cell overlay can be reused over and over in any report written against this data model.

Dynamic What If example

Once applied, the user is presented with a slider that will adjust the value of a variable in the equation above. Using the slider, we can test different scenarios of sales performance.

• By shifting it to the right, we multiply sales by 100% + x%: thereby increasing the value. Because the other metrics are based on sales, they all increase accordingly.

Dynamic What If example

• By shifting it to the left, we multiply sales by 100% - x%: thereby decreasing the value. The other metrics all decrease accordingly.

Dynamic What If example

And apparently, once sales drop by around 30%, we start hitting negative margin territory.

Good to know!

What is your most common "What-if" scenario? Share below...