Multidimensional Expressions (MDX) is a query syntax used specifically for querying and extracting data from OLAP cubes.
Creating an OLAP Snippet
The example that will be covered in this article will be creating a new OLAP Snippet 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 2020 this OLAP Snippet would return the value for June 2020, and if the period selected is Q1 2020 this OLAP Snippet would show the amount for Q4 2019. This OLAP Snippet could be useful as a side-by-side comparison of the current period and the prior period.
To create a new OLAP Snippet, click on the Tools tab in the Jet Data Manager and click Snippets.
Then click Add and SSAS Multidimensional Snippet
The Create Snippet window will appear.
The user will name the OLAP Snippet (in this example the name is Prior Period), type in a description of what the OLAP Snippet 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 OLAP Snippet.
The MDX used in this example:
Prior Period OLAP Snippet
([Date Hierarchy].CurrentMember.PrevMember, [Measure])
The user will then add parameters to be used in the OLAP Snippet 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 OLAP Snippet.
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 OLAP Snippet.
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 OLAP Snippet. 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 OLAP Snippets the user can select the Library Path which will determine the folder that the OLAP Snippet will be placed in. In this example the Prior Period OLAP Snippet will be added to the Time folder since it is related to time. The finished OLAP Snippet should look like the following:
Click OK to save the OLAP Snippet in the project repository.
Using the OLAP Snippet
To use the OLAP Snippet, expand the desired cube on the Cubes tab, right-click Measures, and mouse over Add OLAP Snippet. This will expand the OLAP Snippet 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 OLAP Snippet 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.