Variance calculation using MDX calculated measure in SSAS

Actual vs. Expected Variance Analysis focuses on the difference between the projected or expected amounts and the actual amounts. These type of analysis are part of an important set of metrics for decision makers in all type of organizations and can be applied to Budgets, Production, Revenues, Profits, Efficiency and many other areas.

In SSAS OLAP cubes, these variances could be calculated through several ways. An easy and simple way to accomplish this is through calculated members using MDX in SSAS. There are several other ways to accomplish this calculation but for the scope of this post I chose a simple and quick way for beginner MDX’ers.

The Math
The basic formula for variance is:

Variance = Expected – Actual

The Requirements

You have been tasked to create an Actual Budget Variance cube in Analysis Services for the CEO of your company. In this cube the CEO is interested in viewing the Budget, Actual Budget, Expected Budget and Actual Budget Variance by Department and by Quarter.

Let’s assume we already have the Actual Budget Variance SSAS cube solution already created with a Department and a Quarter dimension as well as with the three Measures already loaded in our OLTP Data Warehouse (Budget, Budget Expected, Budget Actual):
(Click on image to expand)

We need to dynamically calculate the Actual Budget Variance as follows:

Actual Budget Variance = Budget Expected – Budget Actual

To create this calculation in SSAS follow these steps:

1) Open the Analysis Services Project Solution in Business Intelligence Development Studio (BIDS) and click on Calculations Tab and create a new calculated Member as follows:
(Click on image to expand)

2) Save the Solution and Process the cube.

3) Once processed, click on Browser Tab to browse  the cube. Expand the Measures group and you should see the new calculated member. Drag all measures and both dimensions into the browsing area. Group the Department Dimensions under the Quarter Dimension by moving Department column to the right. You can expand and collapse the Quarter Dimension to show/hide the details. It should look something like this:
(Click on image to expand)

From this example you can understand the basics of creating a calculated member using MDX. You can see the full script of the MDX calculated member by clicking on the Script View icon in toolbar under Calculation.
(Click on image to expand)

Advertisement