arrow-icon-size3 Contact Us

Discovering MDX Intrinsic Members

Hi and thanks for reading my first blog entry!

With a view to hone my MDX skills and be ready for the next generation Planning Analytics, I came across a few obscure properties that may be useful. Some may assist in creating sets for users to use, some for administration and others may be purely academic.

The list below gives you the Property, an example and a brief explanation of the use case or result. I used the SData sample for most of these examples.
Explanations are given in non-technical terms. An internet search may give you more formal interpretations, if documented somewhere.

Hope you find them a useful addition to your toolbox!

 

ELEMENT_INDEX

				
					FILTER([model].Members,
StrToValue([model].CurrentMember.Properties("ELEMENT_INDEX"))<=10)

Filtering based on the element index per the properties window in the subset editor

ELEMENT_LEVEL

				
					FILTER([model].Members,
[model].CurrentMember.Properties("ELEMENT_LEVEL")="0")

Similar to:

				
					FILTER([model].Members,
ISLEAF([model].CurrentMember))
Find Level 0 members, or Leaf members based on Level 0 being N-Level or Leaf and counting up as you ascend the hierarchy. C Levels with no children are also seen as Level 0
Could also be used in an IIF() statement to return a parent if Level 0 otherwise return the specific C level member.

ELEMENT_TYPE

				
					FILTER([account1].Members,
[account1].currentmember.properties("ELEMENT_TYPE")="2")

Find all String Type elements in the dimension. 1=N, 2=S, 3=C

Sometimes useful when creating a set of elements to zero out. Could filter on the relevant type or exclude a certain type.

In some cases you need a subset that excludes all Leaves and returns only C levels. This could be done currently using TM1FilterByLevel(<set>,1,2,3,4,5,6) or by doing an Except of your set against a leaf level set. Filtering where the Element_Type is 3 may be easier.

MEMBER_CAPTION

				
					FILTER([model].Members,
INSTR(1, [model].CurrentMember.PROPERTIES("MEMBER_CAPTION"), "Sedan", 1)>0)

The Caption attribute can be an alias or a regular text attribute. When it is an alias, at most 1 element will be returned. When it is a text attribute, more than 1 element can be returned.

May be useful with multiple locales/languages to filter accordingly.

MEMBER_NAME

				
					FILTER([model].Members,
[model].CurrentMember.Properties("MEMBER_NAME")="T Series 2.8 L Sedan")

Appears to be the same as:

				
					FILTER([model].Members,
[model].CurrentMember.Name="T Series 2.8 L Sedan")

Could also use INSTR() to find similar members instead of using TM1FilterByPattern()

MEMBER_ORDINAL

				
					FILTER(
ORDER([model].Members,
StrToValue([model].CurrentMember.Properties("MEMBER_ORDINAL")),BASC),
StrToValue([model].CurrentMember.Properties("MEMBER_ORDINAL"))<=10)

Ordinal is the position number of the member in the set or context in which you are filtering. It is not the Index of the member in the dimension.
The example returns the first 10 members in the hierarchy (rollup).

MEMBER_WEIGHT

				
					FILTER(Hierarchize([account1].Members),
[account1].CurrentMember.properties("MEMBER_WEIGHT")<>"1.000000")

Finds elements based on their weighting within a consolidation.

or similarly, using a numeric comparison but for negative weightings:

				
					FILTER(TM1DrillDownMember([account1].Members,ALL,Recursive),
StrToValue([account1].CurrentMember.properties("MEMBER_WEIGHT"))=-1)

Remember that members can have multiple parents. You would need to be careful of the context you apply this to.

Note that numeric attributes are stored as text with 6 decimal places that need to be specified, otherwise convert to a value using StrToValue.

You could also use this to filter members that do not have any ancestors:

				
					{Filter( Filter([account1].Members, Count([account1].CurrentMember.Ancestors) > 0), StrToValue([account1].CurrentMember.Properties("MEMBER_WEIGHT"))<>1)}

LEVEL_NUMBER

				
					FILTER([model].Members,
[model].CurrentMember.Properties("LEVEL_NUMBER")="0")

Based on Level 0 being Root members and increasing for subsequent levels below Root members.

Same as:

				
					FILTER([model].Members,
[model].CurrentMember.Level.Ordinal=0)

LEVEL_NAME

				
					FILTER([model].Members,
[model].CurrentMember.Level.Name="Series")

Filter on named levels per the }HierarchyProperties cube. I named level000 Total, Level001 Series per the graphic below:

PARENT_LEVEL

				
					FILTER(
[model].Members,
[model].CurrentMember.Properties("PARENT_LEVEL")>"0")

Returns the Level Number of the member’s parent. Above example returns all Members without Root entries which would be Parent’s with a Level of 0.

CHILDREN_CARDINALITY

				
					FILTER(
FILTER(TM1DrillDownMember([account1].Members,ALL,Recursive),
[account1].CurrentMember.properties("CHILDREN_CARDINALITY")="0"),
[account1].CurrentMember.properties("ELEMENT_TYPE")="3"))

This example returns Consolidated Type members having no children.
Filtering on “0” returns Leaf members or C Level members without any children. Filtering again by C Levels may allow us to find C Level members no longer used in a rollup and could potentially be deleted.

Similarly, the below will return N and C level members with no children:

				
					FILTER([model].Members,
Count([model].CurrentMember.Children)=0)

DEFAULT_MEMBER

				
					{[Model].DefaultMember}

As defined in the }HierarchyProperties cube i.e.

Don’t forget to run RefreshMDXHierarchy after making any changes.
Also, if you have multiple rollups in the same hierarchy, you cannot name levels for these the above applies the all rollups in the dimension:hierarchy.

ALL_MEMBER / ALLMEMBER

				
					{[Model].All_Member}

The ALL_MEMBER is a property that was defined in the OLE-DB for OLAP standard. It is possibly part of the TM1 ODBO implementation.
This property is obsolete and will be deprecated, according on feedback I received. Avoid trying to use it.

Some useful information thanks to Hubert:
[Model].Members vs TM1SubsetAll([Model])
Always try to use [Model].Members in your MDX.
If you have a dimension with alternate rollups (have elements with multiple parents in the same hierarchy),  the [Model].Members takes the hierarchy of members represented and returns its members.
TM1SubsetAll takes the subset of all elements and, for each, returns the first member from the hierarchy that represents that element.
If you don’t have elements with multiple parents then the functions are the same but if you do have elements with multiple parents then the [Model].Members would give you all representations of that element whereas the TM1SubsetAll would give you only one, the first one!

I trust some of the above may be both new and useful. Hope you have fun with them.
There are quite a few more MDX functions that may be unknown or little used. I publish another article covering some of these in due course.

Let me know if you have some feedback on the above or if I have left some other properties out that you want to share with the community.
Please also let me know of any errors or ambiguities that you may find so that I can correct for future readers.

Edit: Thank you Hubert Heijkers for taking the time to read and give valuable and much appreciated feedback on the above. I have incorporated the feedback above to hopefully explain things more accurately.

By George Tonkin, Business Partner at MCi.

Further Reading

Tagged

One Response

  1. Pingback: Working with Time Series in MDX Views - Part 6 - MCi