MDX - Intrinsic Members

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

MDX - Intrinsic Members

Post by gtonkin »

Intrinsic members are basically built-in properties that can be used in MDX for selection and filtering.
The below table lists a few of these with some examples based on the SData samples.
Types are Context Sensitive (CS) or Non Context Sensitive (NCS) - See this article for an explanation.

PropertyTypeExampleNotes
ELEMENT_INDEXNCSFILTER(
{TM1SUBSETALL( [model] )},
StrToValue([model].CurrentMember.Properties("ELEMENT_INDEX"))<=10)
Filtering based on the element index per the properties window in the subset editor
ELEMENT_LEVELNCSFILTER(
{TM1SUBSETALL( [model] )},
[model].CurrentMember.Properties("ELEMENT_LEVEL")="0")
Based on Level 0 being N-Level or Leaf and counting up as you ascend the hierarchy.
ELEMENT_TYPECSFILTER(TM1SubsetAll([account1]),
[account1].currentmember.properties("ELEMENT_TYPE")="2")
Will find all String Type elements in the dimension. 1=N, 2=S, 3=C
MEMBER_CAPTIONCSFILTER(
{TM1SUBSETALL( [model] )},
INSTR(1, [model].CurrentMember.PROPERTIES("MEMBER_CAPTION"), "Sedan", 1)>0)
Does not appear to be looking for the Caption attribute/alias
MEMBER_NAMENCSFILTER
({TM1SUBSETALL( [model] )},
[model].CurrentMember.Properties("MEMBER_NAME")="T Series 2.8 L Sedan")
Appears to be the same as:
FILTER
({TM1SUBSETALL( [model] )},
[model].CurrentMember.Name="T Series 2.8 L Sedan")
MEMBER_ORDINALCSFILTER(
ORDER
({TM1SUBSETALL( [model] )},
StrToValue([model].CurrentMember.Properties("MEMBER_ORDINAL")),BASC),
StrToValue([model].CurrentMember.Properties("MEMBER_ORDINAL"))<=10)
Ordinal appears to be the index number of the member when sorted by Hierarchy. The example returns the first 10 members in the hierarchy (rollup)
MEMBER_WEIGHTCSFILTER(TM1SubsetAll([account1]),
[account1].currentmember.properties("MEMBER_WEIGHT")<>"1.000000")

or

FILTER(TM1DrillDownMember(TM1SubsetAll([test]),ALL,Recursive),
StrToValue([test].currentmember.properties("MEMBER_WEIGHT"))=-1)
Find elements based on their weighting
LEVEL_NUMBERNCSFILTER(
{TM1SUBSETALL( [model] )},
[model].CurrentMember.Properties("LEVEL_NUMBER")="0")
Based on Level 0 being Root and counting up for subsequent levels below Root Members
Same as:
FILTER(
{TM1SUBSETALL( [model] )},
[model].CurrentMember.Level.Ordinal=0)
LEVEL_NAMENCSFILTER(
{TM1SUBSETALL( [model] )},
[model].CurrentMember.Level.Name="Series")
Filter on named levels per the }HierarchyProperties cube
PARENT_LEVELCSFILTER(
{TM1SUBSETALL( [model] )},
[model].CurrentMember.Properties("PARENT_LEVEL")>"0")
Returns all Members without Root entries which would be Parent's with a Level of 0
CHILDREN_CARDINALITYCSFILTER(
FILTER(TM1DrillDownMember(TM1SubsetAll([test]),ALL,Recursive),
[test].currentmember.properties("CHILDREN_CARDINALITY")="0"),
[test].currentmember.properties("ELEMENT_TYPE")="3"))
CHILDREN_CARDINALITY = Number of children for a member
Example returns C Level Members having exactly No Child elements.
Filtering on "0" returns Leaf members or C Level members without any children.
Below will return N and C with no children:
FILTER( {TM1SUBSETALL( [model] )},
Count([model].CurrentMember.Children)=0)
DEFAULT_MEMBERNCS{[Model].DefaultMember}As defined in the }HierarchyProperties cube
ALL_MEMBERNCS{[Model].AllMembers}Seems same as {[Model].Members}

The list below includes some other items that still need to be researched and may be relevant using the REST API for MDX and Cellsets:
MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, DIMENSION_UNIQUE_NAME, LEVEL_UNIQUE_NAME
DIMENSION_CARDINALITY, DIMENSION_NAME, DIMENSION_ORDINAL, DIMENSION_CAPTION, DIMENSION_TYPE
HIERARCHY_CARDINALITY, HIERARCHY_NAME, HIERARCHY_UNIQUE_NAME, HIERARCHY_CAPTION
LEVEL_TYPE, LEVEL_CARDINALITY
IS_PLACEHOLDER, CATALOG_NAME, CUBE_NAME, STRUCTURE
TM1UPDATEABLE, , UPDATEABLE, READONLY_STATUS, TM1RULEDERIVED, ANNOTATED, CONSOLIDATED,
LAST_SCHEMA_UPDATE, LAST_DATA_UPDATE, CELL_ORDINAL
FORMATTED_VALUE, FORMAT_STRING, TM1FORMAT_STRING

Let me know should there be some functions missed, errors that need correction or better examples if you have them.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: MDX - Intrinsic Members

Post by lotsaram »

Very useful stuff. Especially ELEMENT_TYPE and MEMBER_WEIGHT.

Can you see any advantage using MEMBER_ORDINAL to sort on versus wrapping a set expression wiuth HIERARCHIZE? Seems to me this would produce the same result.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: MDX - Intrinsic Members

Post by gtonkin »

lotsaram wrote: Mon May 30, 2022 7:41 am Very useful stuff. Especially ELEMENT_TYPE and MEMBER_WEIGHT.

Can you see any advantage using MEMBER_ORDINAL to sort on versus wrapping a set expression wiuth HIERARCHIZE? Seems to me this would produce the same result.
I would probably go with Hierarchize() being a built in function. Sorting based on a property would be slower, I would guess.
Unfortunately there are not always good use cases for the examples so some like the ordering by MEMBER_ORDINAL may be a bit silly and purely academic.

What may be a bit more practical to play with is to return some of these properties as a calculated member using an MDX view on the element attributes cube e.g.

Code: Select all

WITH 
MEMBER [}ElementAttributes_Scenario].[}ElementAttributes_Scenario].[Ordinal] 
  AS [Scenario].CURRENTMEMBER.PROPERTIES("Member_Ordinal")
MEMBER [}ElementAttributes_Scenario].[}ElementAttributes_Scenario].[Type] 
  AS [Scenario].CURRENTMEMBER.PROPERTIES("Element_Type"), 
SOLVE_ORDER = 1 
SELECT {[}ElementAttributes_Scenario].[}ElementAttributes_Scenario].[Ordinal],[}ElementAttributes_Scenario].[}ElementAttributes_Scenario].[Type]} ON 0, 
{[Scenario].MEMBERS} ON 1 
FROM [}ElementAttributes_Scenario]
Scenarios.png
Scenarios.png (7.19 KiB) Viewed 15752 times
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: MDX - Intrinsic Members

Post by PavoGa »

Good stuff! Some of these I've looked for, especially the MEMBER_ORDINAL.
Ty
Cleveland, TN
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: MDX - Intrinsic Members

Post by PavoGa »

Got a question. Can we use "ELEMENT_TYPE" to filter for Aliases?
Ty
Cleveland, TN
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX - Intrinsic Members

Post by Wim Gielis »

Good question. After 30 min trying a few things I could not come up with a solution using MDX for subsets.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
lav4you
Posts: 48
Joined: Fri Jan 02, 2009 1:20 pm

Re: MDX - Intrinsic Members

Post by lav4you »

PavoGa wrote: Tue Oct 03, 2023 1:42 pm Got a question. Can we use "ELEMENT_TYPE" to filter for Aliases?

Can you expand on how would you like to filter?
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX - Intrinsic Members

Post by Wim Gielis »

lav4you wrote: Wed Oct 18, 2023 6:54 pm
PavoGa wrote: Tue Oct 03, 2023 1:42 pm Got a question. Can we use "ELEMENT_TYPE" to filter for Aliases?

Can you expand on how would you like to filter?
Probably, which attributes are aliases, which are string attributes, which are numeric.
Something like the DType or ElementType functions.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
lav4you
Posts: 48
Joined: Fri Jan 02, 2009 1:20 pm

Re: MDX - Intrinsic Members

Post by lav4you »

Wim Gielis wrote: Thu Oct 19, 2023 7:16 am
lav4you wrote: Wed Oct 18, 2023 6:54 pm
PavoGa wrote: Tue Oct 03, 2023 1:42 pm Got a question. Can we use "ELEMENT_TYPE" to filter for Aliases?

Can you expand on how would you like to filter?
Probably, which attributes are aliases, which are string attributes, which are numeric.
Something like the DType or ElementType functions.
Hi,

I found one use case as follows

FILTER(
[DimensionName].MEMBERS , [DimensionName].CURRENTMEMBER.PROPERTIES("AttributeName") = IIF([}ElementAttributes_DimensionName].[AttributeName].PROPERTIES("Element_Type") = "2" , "SearchTerm1" , "SearchTerm2"))

Is this something you were looking for??


Regards,

Lav
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX - Intrinsic Members

Post by Wim Gielis »

lav4you wrote: Fri Oct 20, 2023 4:51 pm Hi,

I found one use case as follows

FILTER(
[DimensionName].MEMBERS , [DimensionName].CURRENTMEMBER.PROPERTIES("AttributeName") = IIF([}ElementAttributes_DimensionName].[AttributeName].PROPERTIES("Element_Type") = "2" , "SearchTerm1" , "SearchTerm2"))

Is this something you were looking for??


Regards,

Lav
Not sure I follow you. Is this supposed to be a solution for the type of filtering I suggested - thinking that PavoGa also wants to filter like that.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: MDX - Intrinsic Members

Post by PavoGa »

lav4you wrote: Wed Oct 18, 2023 6:54 pm
PavoGa wrote: Tue Oct 03, 2023 1:42 pm Got a question. Can we use "ELEMENT_TYPE" to filter for Aliases?

Can you expand on how would you like to filter?
Use the MDX filter function with ELEMENT_TYPE property to filter for aliases. i.e., is there an ELEMENT_TYPE value for aliases on an }ElementAttribute dimension.

As Wim referred to, we can loop through the dim elements with DTYPE or ELEMENTTYPE to do it, but looking for the elegant MDX solution.
Ty
Cleveland, TN
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: MDX - Intrinsic Members

Post by PavoGa »

lav4you wrote: Wed Oct 18, 2023 6:54 pm
PavoGa wrote: Tue Oct 03, 2023 1:42 pm Got a question. Can we use "ELEMENT_TYPE" to filter for Aliases?

Can you expand on how would you like to filter?

Use the MDX filter function with ELEMENT_TYPE property to filter for aliases. i.e., is there an ELEMENT_TYPE value for aliases on an }ElementAttribute dimension.

As Wim referred to, we can loop through the dim elements with DTYPE or ELEMENTTYPE to do it, but looking for the elegant MDX solution.
Ty
Cleveland, TN
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: MDX - Intrinsic Members

Post by PavoGa »

lav4you wrote: Fri Oct 20, 2023 4:51 pm
Wim Gielis wrote: Thu Oct 19, 2023 7:16 am
lav4you wrote: Wed Oct 18, 2023 6:54 pm


Can you expand on how would you like to filter?
Probably, which attributes are aliases, which are string attributes, which are numeric.
Something like the DType or ElementType functions.
Hi,

I found one use case as follows

FILTER(
[DimensionName].MEMBERS , [DimensionName].CURRENTMEMBER.PROPERTIES("AttributeName") = IIF([}ElementAttributes_DimensionName].[AttributeName].PROPERTIES("Element_Type") = "2" , "SearchTerm1" , "SearchTerm2"))

Is this something you were looking for??


Regards,

Lav
Don't think that would do it. Looking to return a list of alias attributes on a dimension when there is not something that readily identifies alias elements like an "a." prefix or something.
Ty
Cleveland, TN
Post Reply