Using Calculations across cubes (calculated measures in virtual cubes) and freehand mdx

As promised in a previous post I will provide examples on how to use the new features of Business Analytics Basic. I will be running on SQL 2005 as these features are only available when using SQL 2005.

First of let's look at BA Db. Overview form where i choose to create a few new measures/expressions in the Virtual Cube Customer Ledger.

I mark the cube and click Card to get access to detailed information on the cube. I then choose Expressions to get to the Editor


The Expression form opens and the name, expression and format of the expression can be entered.

By clicking the assist-butten the measures that are already in the cube appears and can be choosen to create calculations based upon them.

Using this approach (by picking measures this way) simple operations are supported (plus, minus etc.)

What is also possible is to write a freehand expression. All that is needed is to name the expression and the start typing away. The caveat to this approach is that the statement/expression is not validated before the configuration is implemented. If the there are errors in the expression and Analysis Services cannot validate the MDX the entire configuration will fail.  

The use of this type of expression will enable scenarios where a virtual cube based on one cube containing some measures that are based on actual figures and one cube based on budget figures. Expressions calculating budget deviation are possible. Including forecast numbers gives even a wider window.

Using MDX numbers like YTD, Rolling average and measures like that can easily be created.

After implementing the configuration the two new measures are available (here in the Advanced client)