Multidimensional Expressions (MDX) is a query syntax used specifically for querying and extracting data from OLAP cubes.
Creating a Business Function
The example that will be covered in this article will be creating a new Business Function that will allow a user to see an amount for a prior period. The period will be dynamic, so if the current period that is selected is July 2012 this Business Function would return the value for June 2012, and if the period selected is Q1 2012 this Business Function would show the amount for Q4 2011. This Business Function could be useful as a side-by-side comparison of the current period and the prior period.
To create a new Business Function, click on the Tools ribbon in the Jet Data Manager and click Create Business Function.
The Create Business Function window will appear.
The user will name the Business Function (in this example the name is Prior Period), type in a description of what the Business Function does so that other users will know what it is used for (the description is optional), and then type in the MDX formula for the Business Function.
The MDX used in this example:
Prior Period Business Function
([Date Hierarchy].CurrentMember.PrevMember, [Measure])
The user will then add parameters to be used in the Business Function by the users. In this example parameters will be added for [Date Hierarchy] and [Measure]. These parameters are dynamic and can be easily changed in each measure that will be created that will be based on this Business Function.
Highlight [Date Hierarchy] and click the Add Parameter button on the right. The [Date Hierarchy] is added as a parameter in the Parameters section below the formula. In this example the [Date Hierarchy] will represent a date hierarchy that the user will specify so the type should be changed to Hierarchy. A description can also be added to aid the users as to what they should select when using the Business Function.
Next, highlight [Measure] and click the Add Parameter button. This will be added to the Parameters section. In this example, [Measure] represents that measure that the user will select to be used with the Business Function. This could be a measure such as Sales Amount, Quantity, Ending Balance, etc. The Type will be set to Measure since this represents the measure to be used.
If folders are currently being used for Business Functions the user can select the Library Path which will determine the folder that the Business Function will be placed in. In this example the Prior Period Business Function will be added to the Time folder since it is related to time. The finished Business Function should look like the following:
Click OK to save the Business Function in the project repository.
Using the Business Function
To use the Business Function, expand the desired cube on the Cubes tab, right-click Measures, and mouse over Add Business Function. This will expand the Business Function folder list.
In this example we will navigate to the Time folder and click Prior Period. The measure that will be created in this example will be a measure that will return the Sales Amount for the prior period. The user will give the measure a name in the Name field.
In this example we will name is Sales Amount Prior Period. A format string and Associated Measure Group can optionally be added to control how the measure looks and where it exists in the user interface.
Under Parameters, the user will select the relevant value from the Value drop-down box. For the [Date Hierarchy] parameter the Date YQMD ([Posting Date]) hierarchy is selected. For the [Measure] parameter the Sales Amount measure is selected, since that is what this Business Function measure is meant to return.
Once the cube is deployed and executed the Sales Amount Prior Period measure will appear for the users to utilize.
In the example below it can be seen that the Sales Amount measure returns the sales for the current period and the Sales Amount Prior Period returns the sales amount for the previous period. In this example the Sales Amount Prior Period for May 2010 is the same value as the Sales Amount for April 2010.