TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

MDX - Intrinsic Members

https://www.tm1forum.com/viewtopic.php?t=16224

Page 1 of 1

MDX - Intrinsic Members

Posted: Fri May 27, 2022 5:22 pm
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.

Re: MDX - Intrinsic Members

Posted: Mon May 30, 2022 7:41 am
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.

Re: MDX - Intrinsic Members

Posted: Mon May 30, 2022 9:21 am
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 16650 times

Re: MDX - Intrinsic Members

Posted: Wed Jun 01, 2022 4:47 pm
by PavoGa
Good stuff! Some of these I've looked for, especially the MEMBER_ORDINAL.

Re: MDX - Intrinsic Members

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

Re: MDX - Intrinsic Members

Posted: Tue Oct 03, 2023 9:43 pm
by Wim Gielis
Good question. After 30 min trying a few things I could not come up with a solution using MDX for subsets.

Re: MDX - Intrinsic Members

Posted: Wed Oct 18, 2023 6:54 pm
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?

Re: MDX - Intrinsic Members

Posted: Thu Oct 19, 2023 7:16 am
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.

Re: MDX - Intrinsic Members

Posted: Fri Oct 20, 2023 4:51 pm
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

Re: MDX - Intrinsic Members

Posted: Fri Oct 20, 2023 5:52 pm
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.

Re: MDX - Intrinsic Members

Posted: Tue Oct 24, 2023 1:46 pm
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.

Re: MDX - Intrinsic Members

Posted: Tue Oct 24, 2023 1:49 pm
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.

Re: MDX - Intrinsic Members

Posted: Tue Oct 24, 2023 1:53 pm
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.
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited