Custom DV What-If Analysis Using Essbase Data

I got a LinkedIn message from someone last week asking if what-if analyses can be done in OAC’s DV using the Standard Edition (another way: Can you do what-if analyses in DV using EssCS in OAC Standard Edition, meaning no BI)? The answer is yes! To show an example of how this might work, I have chosen a simple budgeting example using personnel management locality and base pay as an example.

My source is a cloud Essbase cube (on-premises would work just fine, too) that has actual and budget data for FY2016-2018.

Dimensions:

Measures:

Where TotalPay = BasePay + LocalityPay

I have entered my Budget Version increases for both base pay (General Schedule Increase) and locality pay (Locality Pay Increase) over the previous year’s Actual->Final pay.

If I build a visualization off the different Versions, I can see how they rank compared to each other. Notice that I have used 2 different Y-axes to show the 2 different types of pay. I wanted to compare them, but the disparity between the two didn’t allow for good visual increases/decreases.

But what if you wanted to create a new version, on the fly, for what-if analyses without touching Essbase?

Create a calculation!

To create a new what-if Version in this case, I created a new Base Pay Increase, Locality Pay Increase, and a total of the two. Here are the calculations:

Base Pay Increase – OTF (Final)

Locality Pay Increase – OTF (Final)

You can see that I filtered out just the Final version for calculation using a CASE(If) statement.

When I add these two items to the graph (and adjust the visualization properties to add the new Locality Pay Increase calculation to the 2nd Y-axis), I get the following visualization:

I’ve, essentially, created a new Version in my Budget scenario without touching the Essbase cube.

Note that I filtered out to only Budget by putting the Scenario Name in the visualization filters and chose “Budget”.

Now, if I want to adjust the numbers behind my new Version, it’s as simple as adjusting the calculation, not the Essbase cube!

You can also add more dimensions to your CASE statement to get a true intersection of Essbase data.

Result:

If I choose the total for the year, I can quickly compare the original Budget numbers with my new “Version”.

So, there you go! No need for OAC Enterprise Edition to do simple what-if analyses in DV using Essbase data. Just use custom calculations!