Business Analytics

Both IBM Clarity and IBM Cognos TM1 support the MultiDimension eXpression (MDX) query language through snippets. Snippets are single MDX phrases that can be evaluated as part of an MDX query. At first glance, it can seem intimidating, but starting with a few key functions, it’s easier to start using it to put together advanced snippets.
Let’s go through an example. Let’s say we have the following cube.

Cube-shot

Our dimensions are:

  • Year
  • Time
  • Entity
  • Account
  • Measures

When building a template in ClarityStudio, I can select Member, Children, IChildren, Descendants, IDescendants and BottomLevelDescendants by right-clicking on an element in a dimension hierarchy. Let’s start by explaining how these ones map to MDX expressions, using the Full Year member from the Time dimension.

Clarity Function Description MDX Function MDX Snippet Members Included
Member Show just the one element I picked from the list None [Time].[Full Year] Full Year
Children Show just the elements directly below the one I selected .Children [Time].[Full Year].Children Q1, Q2, Q3, Q4
IChildren Show the elements directly below the one I selected, plus the one I selected .Children {[Time].[Full Year].Children, [Time].[Full Year]} Q1, Q2, Q3, Q4, Full Year
Descendants Show all elements anywhere below the one I selected, regardless of distance Descendants Descendants([Time].[Full Year]) Q1, Q2, Q3, Q4, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
IDescendants Show all elements anywhere below the one I selected, regardless of distance, including the one I selected Descendants {Descendants([Time].[Full Year]) , [Time].[Full Year]} Q1, Q2, Q3, Q4, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Full Year
Bottom Level Descendants Show only the elements that have no children below the one I selected. These are also known as leaf elements. Descendants Descendants([Time].[Full Year] ,,LEAVES) Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
Now that you’ve seen how the ones you already know map to MDX expressions, you can play with other MDX expressions to help get exactly the sets you want for your template.
2017-03-22T18:39:17-04:00