Support for parameters in the IBM Planning Analytics Workspace MDX editor

You can now parametrize MDX queries in IBM® Planning Analytics Workspace.

The default MDX generation uses a query state, which consists of the sets for each hierarchy, member selections for hierarchies on titles, and any applied operations (for example, expand, collapse, sort, etc). The query state generates an MDX query to retrieve the corresponding axis and cell data from the TM1® database.

A parameterized MDX involves the use of an MDX template. MDX templates allow you to override how the query state is used to generate the MDX. An MDX template contains the following:
  • Variables that can be substituted during MDX generation.
  • Variable declarations which define the members or sets in the query state which are substituted for the variables in the MDX template.

Supporting parameters allows for more control over the MDX, while allowing the view to respond to the specified member and set synchronization events based on how the template is defined.

Note: You can parameterize MDX queries only in books; this capability is not available on a modeling workbench.
The following variables can be defined in an MDX template:
Set variables
Set variables define a reference to a set for a hierarchy defined in the query state.
For example: 
{
      "Name": "<0>",
      "HierarchyID": "[plan_time].[plan_time]",
      "Type": "SetVariable"
    }
Slicer member variables
Slicer member variables define a reference to the selected member for the query state.
For example: 
{
      "Name": "<2>",
      "HierarchyID": "[plan_version].[plan_version]",
      "Type": "SlicerVariable"
    }
Set member variables
Set member variables define references to members of a set at a specified index.
For example: 
{
      "Name": "<1>",
      "HierarchyID": "[plan_chart_of_accounts].[plan_chart_of_accounts]",
      "Type": "SetMemberVariable",
      "Index": 0
    }
The following is an example of a complete MDX template:
{
  "MdxTemplate": "SELECT {<0>} ON 0, {<1>} ON 1 FROM [plan_BudgetPlan] WHERE (<2>, [plan_business_unit].[plan_business_unit].[10110])",
  "Variables": [
    {
      "Name": "<0>",
      "HierarchyID": "[plan_time].[plan_time]",
      "Type": "SetVariable"
    },
    {
      "Name": "<1>",
      "HierarchyID": "[plan_chart_of_accounts].[plan_chart_of_accounts]",
      "Type": "SetMemberVariable",
      "Index": 0
    },
    {
      "Name": "<2>",
      "HierarchyID": "[plan_version].[plan_version]",
      "Type": "SlicerVariable"
    }
  ]
}

How to parametrize an MDX query

To parametrize an MDX query, complete the following steps:
  1. In the cube viewer, click the edit MDX icon Edit MDX icon to access the MDX.
  2. Click the MDX template variables icon MDX template variables icon. The MDX parameters dialog box opens.
  3. To add a parameter, click the Add parameter button and set the following options:
    1. Name: The parameter id. The name can only consist of numeric values.
    2. Dimension: The dimension to define in the parameter.
    3. Hierarchy: The hierarchy to define in the parameter.
    4. Type: The type of variable to define in the parameter. You can choose a Set, Set member, or Slicer variable.
    5. Index
  4. To add the current parameter to the list and create another parameter, click Add. To add the current parameter to the MDX and close the MDX parameters dialog box, click Add and close.
  5. Click Done.

Known limitations

There are a few current known limitations with MDX templates:
  • Apart from the defined variables, MDX template views are read-only. They do not respond to events that change the structure of the query (for example, pivoting, expand, collapse, sort, etc).
  • MDX template views only respond to member and set synchronization events they are parameterized to respond to.
  • Metadata change recovery is only applied to sets and members referenced by variables.