MDX for 0 level elements with no level 1 parent

[ramjam]

Hi all,

I'm new to TM1 and MDX, so please bear with me if I'm not phrasing this correctly, but I'm trying to create a subset using MDX that will return all 0 level elements that don't have their parent at level 1 - anyone know how to do this? I've spent the better part of the day online looking for this, but I think my lack of MDX experience is making it difficult to "see" the solution. Thanks

[EvgenyT]

In reverse, children of an empty parent :

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [DimName] )}, 0)}, [DimName].CurrentMember.Parent.Name = '')}

[ramjam]

OK, I think I see how/why that's working, but it only returns level 0 elements that have no parent at all, correct? What about level 0's that have no level 1 parent but do have a parent at level 2,3,4, etc.? Am I missing something? It seems like there would be a way to identify the level of the element's parent (something like currentmember.parent.level.ordinal <>1 or something like that, but that's not working for me (again, I'm a total newb with TM1, so it could be I'm just not understanding the syntax or something along those lines). Thanks again

[tomok]

OK, I think I see how/why that's working, but it only returns level 0 elements that have no parent at all, correct? What about level 0's that have no level 1 parent but do have a parent at level 2,3,4, etc.? Am I missing something? It seems like there would be a way to identify the level of the element's parent (something like currentmember.parent.level.ordinal <>1 or something like that, but that's not working for me (again, I'm a total newb with TM1, so it could be I'm just not understanding the syntax or something along those lines). Thanks again

TM1 has ragged hierarchies, what you are looking for is structured hierarchies. You would have to add dummy parents to fill out the missing levels.

[ramjam]

Yes, sorry the part about ragged hierarchies wasn't clear in my initial post - I did not realize that was relevant. So the solution is to add a "dummy" parent to the elements that don't have a natural parent - that's what I was originally thinking I'd like to do, but that makes it a much bigger project than I was hoping. Thanks for the replies Evegney and tomok!!

[paulsimon]

Hi Ramjam

What you are asking seems odd.

If you have a ragged hierarchy then by definition any level 0 element, ie the bottom of the hierarchy, that does not have a level 1 parent, also does not have a level 2 parent. Putting in dummy parents to fill out the levels is not going to help because there won't be any parents for these elements.

What are you actually trying to do? For example in my dimension build routines I typically link any base level elements that have no parent up to an Orphan consolidation and this then links to the All Hierarchies consolidation so that at least the All Hierarchies consolidation will include every element in the dimension. Therefore selecting the All Hierarchies consolidation means that I get the total of everything in the dimension. It also means that I can easily identify elements without a parent. They are the children of the Orphan consolidation. I can then go back to the business and ask what the parent should be and make the appropriate changes. That is a lot easier than having a hierarchy that does not add up with the risk of incorrect results being produced, and needing to write a complex MDX statement to identify the orphaned elements. So perhaps the real answer is to address this at the time that you build the dimension, which is probably going to be infrequent, and it therefore doesn't really need a dynamic MDX statement.

If my guess as to the reason why you want to do this is way off, and you do need to identify elements according to a level tag of some sort, then an MDX approach would be like this.

Select elements that have the tag you want, eg if you add an Attribute called Level - Just record a Filter by Attribute statement, eg filter by attribute level = 1. Save that as a subset Level1

Next write an MDX statement based on that subset {TM!FILTERBYLEVEL( { TM1DRILLDOWNDIMENSION( { [dim].[Level1] } , ALL , RECURSIVE ) } , 0 ) }

Which will get you all base level elements below the Level1 elements. Save as subset Level1_Base

Then record a simple Show All and filter by level 0 and save as a subset called Base.

Then write MDX as

{EXCEPT( { [dim].[Base] } , { [dim].[Level1_Base] } )

The EXCEPT statement gives the difference between two sets ie what is in Set A that is not in set B.

As illustrated above when building more complex MDX it can be useful to write the MDX in parts and save each part as a named subset that you can test independently and possibly re-use as a building block for other expressions.Referencing the subset in later MDX statements just needs { [Dim Name].[Subset Name] }

Regards

Paul Simon

[EvgenyT]

What about level 0's that have no level 1 parent but do have a parent at level 2,3,4, etc.?

Merely tried to demonstrate how you can achieve it with MDX, i.e If you don't have 1st parent then by default you have no further parents. Does it make sense?

On the other hand if you are trying to establish child-parent relationships based on a level of the parent, you would have to go down a different avenue, as suggested by others here already.

Curious, what is the purpose of this requirement?

Thanks

ET

[qml]

Merely tried to demonstrate how you can achieve it with MDX, i.e If you don't have 1st parent then by default you have no further parents. Does it make sense?

Due to TM1's ragged hierarchies and the way in which it numbers its levels, it is entirely possible for an N element (which is always level 0) to have a parent that is on level 2 or 3, but have no parent on level 1. Take this hierarchy example:

C3
..C2
....NA
....C1
......NB

NA and NB are both leaf elements, C1, C2, C3 are consolidations. According to TM1's 'classic' (i.e. pre-PM) level numbering method C3 is on level 3, C2 is on level 2, C1 is on level 1. NA and NB are both on level 0. NB has its only parent on level 1. NA has its only parent on level 2.

Due to the lack of exposure of that definition of 'level' in MDX I would suggest the best method of using it in MDX (if one really must) would be to do as Paul suggests - add an attribute that would store the level and populate it via TI so that you can reference it directly in MDX.