One of the most powerful aspects of Analysis Services is the ability to easily view your facts over time. What’s not always so easily is writing those calculations that make time analysis possible. MDX can have a steep learning curve, which is where the BI xPress MDX Calculation Builder by Pragmatic Works steps in.

After installing BI xPress, you will very quickly and easily add calculations to your cube without having to write a lick of MDX. To access the MDX Calculation Builder, open BIDS and open your SSAS project. After opening the cube you would like create the calculations in, navigate to the Calculations tab of the cube designer. There you’ll see the Calculation Builder icon.

After you click the Calculation Builder icon, the Calculation Builder wizard will open. The first thing you’ll need to do is select a calculation.

You’ll have about a dozen different calculations to choose from, including Year to Date, Month to Date, and Year Over Year Growth, just to name a few. In this example, we’re going to create a Year Over Year Growth calculation. Next we’ll need to select our fact.

Then we’ll select our Year level attribute.

The third step is optional. We can apply conditional formatting to our calculation. In this example, if the measure is less than 0, the font will display red. Greater than zero, the font of the measure will display as green.

Lastly, we’ll give our measure a name, select the measure group we’d like the measure to be a part of, select the formatting, and preview the calculation that was written by the tool.

Then we’ll click Finish. The calculation has been added to our calculation script within our cube.

After processing the cube, we can test out our Year Over Year Growth.

And its that easy. In just four simple steps we’ve created our calculation without writing a bit of MDX.

The MDX Calculation Builder also allows you to build named sets just as easily. Of course, as you learn MDX, the MDX Calculation Builder won’t always be necessary. But it sure will accelerate your learning and make writing MDX calculations easier in the mean time.