Friday, December 20, 2013

Writing Advanced Snippets When Building Templates

Both Clarity and 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.

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
[Time].[Full Year].Children
Q1, Q2, Q3, Q4
IChildren
Show the elements directly below the one I selected, plus the one I selected
{[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(
[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(
[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(
[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.





No comments:

Post a Comment

Note: Only a member of this blog may post a comment.