Hierarchy sort and Hierarchy MDX

[Wim Gielis]

Hello,

When we record an MDX expression for the button "Hierarchy Sort", the generated function is {HIERARCHIZE( ... )}.
However, there is a difference in case elements occur more than once.
Pressing the "Hierarchy sort" shows all elements in all of their occurrences.
Using for example:

Code: Select all

{Hierarchize( { TM1SubsetAll( [dim] )} )}

gives as all elements but those instances where the same elements occur more than once with the same parent-child relation. An example will make it clear
I attach a very simple dimension (*.dim file).
On the screenshot we see the difference between MDX (left) and pressing the icon (right).

Question: what MDX statement/combination of MDX'es can give us the result on the right in this dimension setup ?

Thanks a lot !

dim.dim

(254 Bytes) Downloaded 565 times

wim.jpg (115.19 KiB) Viewed 15254 times

[declanr]

Is just wrapping an all recursive drill down member not sufficient? Or are you looking for just one function?

[Wim Gielis]

Great ! That does the job nicely Declan. Thank you.

Code: Select all

{TM1DrilldownMember( {Hierarchize( { TM1SubsetAll( [dim] )} )}, ALL, Recursive)}

[lotsaram]

According to the MDX documentation "The Hierarchize function organizes the members of specified set into hierarchical order. The function always retains duplicates."
From what I can tell this is true, including in TM1 MDX. The problem is with TM1SubsetAll as this doesn't return duplicates, it just shows all elements in index order.
{Hierarchize( { TM1SubsetAll( [dim] )} )} is therefore giving the correct result (even if not the expected one).
It would seem the issue with the consistency is that clicking the "hierarchy sort" button in subset editor is actually nesting TM1DRILLDOWNMEMBER and then HIERARCHIZE but the recorder only captures the HIERARCHIZE. Call this a feature/bug.

So the answer is as Declan provided. But I think to be nit-picky that the nesting should be the other way around
{Hierarchize( {TM1DrilldownMember( { TM1SubsetAll( [dim] )}, ALL, Recursive)} )}
Although the end result is of course the same.

[Wim Gielis]

Hello Lotsaram, Declan, others,

This is a good piece of information and I can confirm this in the Subset Editor.
However I am afraid that my query is not solved yet. Let me make the distinction between "icon Hierarchy sort" and "Hierarchize() MDX" clear with screenshots.

Here is a subset, just static, nothing fancy:

00.PNG (20.9 KiB) Viewed 15219 times

Then I click on the icon "Hierarchy sort", note that the consolidated element 30_90401000 shows up twice. This is the desired result I would like to obtain, starting from the existing static subset, with an MDX expression or combination of MDX functions (does not matter):

01.jpg (59.44 KiB) Viewed 15219 times

My attempt with "Hierarchy()" as this is generated with the expression recorder, note the missing consolidation 30_90401000:

02.jpg (53.98 KiB) Viewed 15219 times

Functions like TM1DrillDownMember are not the solution, because then I receive thousands of customers for free but I do not want these.
I have the correct elements, but not all instances of them. Of course, this is because of the double usage of the same consolidation.

The Intersect of the original set with the original set augmented with a TM1DrillDownMember does not give me the duplicates, which is logical though.

Unfortunately I cannot change the dimension structure such that parents are named in a unique way. In fact, I follow the customer who says that this structure is easier for navigation than extra uniquely named consolidations.

It just strikes me that the expression recorder does not generate an expression, such that the same result is obtained when playing back the recorded expression.

Thanks a lot.

Wim

[Wim Gielis]

For your information, in case it helps and it changes things, I should have added in the opening post the aim of this exercise.

In fact, I have a large customer dimension of about 10,000 customers. About 9,500 of them are regular customers, who enter the dimension from a database table.
The other 500 customers are customers for so called 'New Markets', meaning that they are not yet existing customers but we want to forecast turnover on these customers.
The new customers can be created by the user, ANYWHERE in the dimension, at any level. This is important.

Since real customers in the datasource can change, we need to unwind the dimension before recreating the dimension.
However, we would lose the information on the new customers if we do not store this in a certain way.
Hence the need to track which are the new one customers (which is easy with an attribute), including all of its parents/ancestors.
Like that we are able to fully rebuild the dimension structure after a dimension unwind.

To add to the complexity, the dimension has 4 parallel hierarchies, each customer rolls up in the 4 hierarchies.
But intermediate consolidated levels are not named uniquely, that is, customers roll up in customer groups and these groups appear in 2 parallel hierarchies.
Its the latter fact that causes TM1 to not show all instances of all ancestors of a given element. Customer groups have different parents in different hierarchies.
The are no double countings in any of the 4 hierarchies.

I could think of a boatload of attributes to capture all parent names in all 4 hierarchies using attributes on the customer dimension, but that does not seem like the best solution to me and would still need a way to capture the ancestor information at a certain time.

The problem is more of a general nature: how to track and recreate all ancestors (unique paths) to any element in any given dimension ?
I think that others have faced this challenge in the past too.

[PavoGa]

I could think of a boatload of attributes to capture all parent names in all 4 hierarchies using attributes on the customer dimension, but that does not seem like the best solution to me and would still need a way to capture the ancestor information at a certain time.

What if you had an attribute "Parents" for each element (leaf or consolidation) and store the values like this:

Parent1:Parent2:Parent3

Build that attribute before the unwind and use it to rebuild the hierarchy as desired.

[Wim Gielis]

Build that attribute before the unwind and use it to rebuild the hierarchy as desired.

Populating that attribute is the challenge...
The tricky part is that the dimension can contain whatever structure, number of consolidations, ...
Consolidated elements need not be uniquely named in all parallel hierarchies.
Dimension structures that lead to double counting if all weights were 1 (or, if weights do not cancel out each other) are possible too.

So finding the unique path to every element is the challenge

[PavoGa]

So weights can vary for each element depending on where it is in the hierarchy/hierarchies?

[Wim Gielis]

That is possible yes, although in the customer dimension example, all weights are 1.

But the tricky part is that consolidations can occur with the same name in different parallel hierarchies. Their names are not unique.
Then asking for the parents "skips" certain instances of the elements/parents.
If we press "Hierarchy sort" in the Subset Editor it's all fine, but the MDX and/or TI to get the same result is rather difficult.
Certainly in the completely dynamic case where any dimension structure that TM1 allows, with varying weights, is allowed.

[tomok]

I think PavoGa is on the right track. The best way I know of to be able to re-create a completely unstructured hierarchy will be to store all the parent-child relationships in attributes in a flat file and use that file to rebuild the dimension.

The tricky part is going to be populating those attributes. Instead of working your way up the tree you'll need to work your way down which we all know is more difficult because there may be multiple branches you'll have to keep track of while in the process of stepping down. So, what would basically be involved is looping through the dimension, checking to see if the element has a parent. If no parent is found then you know that element is the start of a tree. If it's the first one like that then it is the first tree. You then step down this tree looking for the first child (with ELCOMPN and ELCOMP) . The child is the element and the parent is the value. Write this out to the file. Now, using the child, see if it also has children the child is the element and you are looking to see if it has children. If it does then the first child is the element you store the parent attribute against the parent is the value. Keep going until no more children found and then you go back up to the innermost loop and look for it's next child and move down. Once you are at the end of all the branches for an element in the original loop then you look for the next element that has no parent. Once found that becomes your next tree and so forth.

You should be able to use this file to re-create your hierarchies.

I had a colleague who created VBA code a long time ago that does that step-down. I know it's not exactly what you need but it might be of help because with just a little bit of modification, and converting to TI code. it should be able to create the file I am talking about. Here posted is the entire VBA module

Code: Select all

Option Explicit
Public MyServer As String
Public MyDim As String

'******************************************************************************************
'***    Description:   These macros will create a list all children and their parents   ***
'***                   for a selected parent element from a tm1 dimension.              ***
'***                   The alternate long names as well as a path code are also         ***
'***                   provided.  These columns can be imported into another database.  ***
'******************************************************************************************
Sub DigDown()
Dim MyLevel As Integer
Dim MyStructure(15) As Integer
Dim TotChildren As Integer
Dim counter As Integer, Layer As Integer, MaxLayer As Integer
Dim MyChild As String, MyParent As String
Dim MyPad As String, LstChar As Integer
Dim MyRow As Long

MyServer = Range("MyServer").Value
MyDim = MyServer & ":" & Range("MyDimension").Value
MyParent = Range("MyRollup").Value

'*** Make sure user is connected to TM1 server
If CheckConnection(MyServer, MyDim) = False Then
    Exit Sub
End If

MyRow = 7  'If the starting row changes, then the one up calculation in DigParent needs to change.
'*** Erase previous results
Call Cleanup(MyRow)

MyLevel = Run("Ellev", MyDim, MyParent)
If MyLevel <> 0 Then
    Cells(MyRow, 1).Value = UCase(Trim(MyParent))                   'Child element
    Cells(MyRow, 2).Value = UCase(Trim(MyParent))                   'Parent element
    Cells(MyRow, 3).Value = LongName(MyServer, Range("MyDimension").Value, MyParent)     'Child long name
    Cells(MyRow, 4).Value = Cells(MyRow, 3).Value                   'Parent long name
    Cells(MyRow, 5).Value = MyLevel                                 'TM1 level
    Cells(MyRow, 6).Value = Run("Dimix", MyDim, MyParent)           'Child TM1 index
    Cells(MyRow, 7).Value = Cells(MyRow, 6).Value                   'Parent Tm1 index
    Cells(MyRow, 8).Value = 1                                       'One-up index
    Cells(MyRow, 9).NumberFormat = "@"
    Cells(MyRow, 9).Value = 1                                       'Code
    Cells(MyRow, 10).Value = 1                                      'Depth
    '*** Start drill down
    Call DigParent(MyParent, MyDim, MyRow)
Else
    MsgBox "This element has no children or does not exist."
End If
Cells(7, 9).Value = "1.1"   'Add a .1 to the code for the very top (first) node
Cells(7, 10).Value = 2
End Sub
Sub DigParent(MyParent, MyDim, MyRow)
Dim MyLevel As Integer
Dim TotChildren As Integer
Dim counter As Integer
Dim MyChild As String, ParentName As String, ChildName As String, Dimension As String
Dim ParentIndex As Long, ChildIndex As Long

Dimension = Range("MyDimension").Value
MyLevel = Run("Ellev", MyDim, MyParent)
If MyLevel <> 0 Then
    TotChildren = Run("Elcompn", MyDim, MyParent)               'Total number of children
    ParentName = LongName(MyServer, Dimension, MyParent)            'Long name of parent
    ParentIndex = Run("Dimix", MyDim, MyParent)                 'Tm1 index number of element
    For counter = 1 To TotChildren
        MyChild = Run("Elcomp", MyDim, MyParent, counter)       'Pull child elements
        MyLevel = Run("Ellev", MyDim, MyChild)                  'Get TM1 level of child
        ChildName = LongName(MyServer, Dimension, MyChild)          'Long name of child
        '*** If the child is a detail center add the center number to the name (except for the rapAccounts dim)
        If IsNumeric(MyChild) And UCase(Dimension) <> "RAPACCOUNTS" Then
            ChildName = MyChild & "-" & ChildName
        End If
        ChildIndex = Run("Dimix", MyDim, MyChild)       'Get the TM1 index number of the child
        '*** Go to the next row and write out the data for the child
        MyRow = MyRow + 1
        Cells(MyRow, 1).NumberFormat = "@"
        Cells(MyRow, 1).Value = MyChild         'Child element
        Cells(MyRow, 2).Value = MyParent        'Parent element
        Cells(MyRow, 3).Value = ChildName       'Long name of child
        Cells(MyRow, 4).Value = ParentName      'Long name of parent
        Cells(MyRow, 5).Value = MyLevel         'TM1 level of child
        Cells(MyRow, 6).Value = ChildIndex      'TM1 index number of child
        Cells(MyRow, 7).Value = ParentIndex     'TM1 index number of parent
        Cells(MyRow, 8).Value = MyRow - 6       'One-up # - The number subtracted must be one less than the starting row
        Cells(MyRow, 9).NumberFormat = "@"
        '*** Call routine to determine ID code and depth
        Call IDandDepth(MyRow)
        '*** Call this routine to get the children of the children
        Call DigParent(MyChild, MyDim, MyRow)
    Next counter
End If
End Sub
Sub Cleanup(StartRow)
Dim LastCell As String
    LastCell = Range("a1").SpecialCells(xlCellTypeLastCell).Offset(0, 1).Address
    Range("a" & StartRow & ":" & LastCell).Clear
End Sub
Sub IDandDepth(MyRow)
'***  This routine will find the parent's code and depth and determine the child's code and depth   ***
Dim LUVal As Long, LookRow As Long

LUVal = Cells(MyRow, 7)     'Grab TM1 index number of parent
LookRow = MyRow             'Separate the lookup row from the row you're actually on
'*** Keep looking up the child TM1 index column until you find the parent number
Do Until Cells(LookRow, 6) = LUVal Or LookRow = 0
    LookRow = LookRow - 1
Loop
'*** Once you find it...
Cells(MyRow, 9).Value = Cells(LookRow, 9) & "." & Cells(MyRow, 8)   'Get the path code of the parent and add this child's code
Cells(MyRow, 10).Value = Cells(LookRow, 10) + 1             'Get the depth of the parent and add 1

End Sub
Function CheckConnection(MyServer, MyDimension)
'******************************************************************************************************
'***        CheckConnection insures that a TM1 connection has been made by attempting to retrieve   ***
'***        some data.                                                                              ***
'***                                                                                                ***
'******************************************************************************************************
Dim x As Long, y As String

x = Run("DimSiz", MyDimension)
If x <> 0 Then
    CheckConnection = True
Else
    y = MsgBox("Error: You must connect to " & MyServer, vbOKOnly)
    CheckConnection = False
End If
End Function
Sub ExtractMe()
Dim ULFile As String        'Upload file path and name
Dim MyPath As String        'Path of this workbook
Dim MyRow As Long
Dim Child_Short As String
Dim Child_Long As String
Dim Parent_Short As String
Dim Parent_Long As String
Dim ChildNum As Long
Dim ChildLevel As Integer
Dim ChildDepth As Integer
Dim ChildCode As String
Dim Header As String

MyPath = Range("MyPath").Value
ULFile = Range("MyRollup").Value & ".txt"
ULFile = MyPath & "\" & ULFile
Open ULFile For Output As #1
MyRow = 7
Header = """Child_Short"", ""Child_Long"", ""Parent_Short"",""Parent_Long"", ""ChildNum"", ""ChildLevel"", ""ChildDepth"", ""ChildCode"""
Print #1, Header

Do Until Cells(MyRow, 1) = ""
    Child_Short = Cells(MyRow, 1)
    Child_Long = Cells(MyRow, 3)
    Parent_Short = Cells(MyRow, 2)
    Parent_Long = Cells(MyRow, 4)
    ChildLevel = Cells(MyRow, 5)
    ChildNum = Cells(MyRow, 8)
    ChildCode = Cells(MyRow, 9)
    ChildDepth = Cells(MyRow, 10)
    
    Write #1, Child_Short, Child_Long, Parent_Short, Parent_Long, ChildNum, ChildLevel, ChildDepth, ChildCode
    MyRow = MyRow + 1
Loop
Close #1
End Sub

Function LongName(Server, Dimension, Element)

Select Case UCase(Dimension)
    Case "RAPLOB"
        LongName = Run("DBRW", Server & ":rapAt_LOBGeog", Element, "LongName")
    Case "RAPGEOG"
        LongName = Run("DBRW", Server & ":rapAt_LOBGeog", Element, "LongName")
    Case "RAPACCOUNTS"
        LongName = Run("DBRW", Server & ":rapAt_Accts", Element, "Base", "LongName")
End Select

End Function

[Wim Gielis]

Thank you Tom, I will experiment with that code and in parallel I will write my own MDX/TI routines.

Wim

[tomok]

Just as a test I whittled down the VBA code to provide a flat file that you can run against a dimension that will create a flat file source that, when used as the data source in a TI process, will recreate the dimension EXACTLY as it currently exists. The issue is going to be translating this to TI because the drill down Sub in the VBA recursively calls itself, making the coding really easy. Since you can't do that in TI, in conjunction with ASCIIOutout, then you'll have to write a bunch of loops inside loops. As long as you have enough loops to cover the max number of levels in the dimension it should work.

Here is the VBA:

Code: Select all

Option Explicit
Public MyDim, MyFile As String
Public MyTreeNum As Integer

Sub FindTrees()

Dim dimCounter, MyDimSize, MyElementLevel As Integer
Dim MyElement, MyElementParent As String

MyDim = "Server:Dimension"
MyDimSize = Run("DIMSIZ", MyDim)  'Total size of dimension
MyFile = "PathAndFile"
Open MyFile For Output As #1

dimCounter = 1
While dimCounter < MyDimSize
    MyElement = Run("DIMNM", MyDim, dimCounter)
    MyElementParent = Run("ELPAR", MyDim, MyElement, 1)
    If MyElementParent = "" Then
        MyTreeNum = MyTreeNum + 1
        Call DigParent(MyElement)
    End If
    dimCounter = dimCounter + 1
Wend

Close #1

End Sub

Sub DigParent(MyParent)
Dim MyLevel As Integer
Dim TotChildren As Integer
Dim counter As Integer
Dim MyChild As String

MyLevel = Run("Ellev", MyDim, MyParent)
If MyLevel <> 0 Then
    TotChildren = Run("Elcompn", MyDim, MyParent)               'Total number of children
    For counter = 1 To TotChildren
        MyChild = Run("Elcomp", MyDim, MyParent, counter)       'Pull child elements
        Write #1, MyTreeNum, MyChild, MyParent                  'Write to file
        '*** Call this routine to get the children of the children
        Call DigParent(MyChild)
    Next counter
End If
End Sub

[Wim Gielis]

Thank you very much Tom. That should indeed work with nested loops but I wanted to avoid hardcoding the number of loops and spelling out the loops as such. I was actually more after a solution without further possibility to run into an issue later on. And I think I can do it using loops in (clever MDX-based) subsets, it's just that allowing any structure in the dimension poses a number of challenges, as you know. When I reach such a generic solution I will inform you. I will also take into account weights <> 1 because then non-standard dimensional structures make sense.

[BrianL]

Since you can't do that in TI, in conjunction with ASCIIOutout, then you'll have to write a bunch of loops inside loops.

This is one case where java extensions could provide useful as recursive functions and calls to AsciiOutput are trivial in java.

[Wim Gielis]

Since you can't do that in TI, in conjunction with ASCIIOutout, then you'll have to write a bunch of loops inside loops.

This is one case where java extensions could provide useful as recursive functions and calls to AsciiOutput are trivial in java.

Good point ! It's not part of my knowledge yet so I will that for later or for someone else

[PavoGa]

Wim,

Code: Select all

[tmp_ty].members

seems to return the subset you were looking for originally by the way.

[Wim Gielis]

Wim,

Code: Select all

[tmp_ty].members

seems to return the subset you were looking for originally by the way.

Hello Ty,

Can you apply Members on a subset ?
Because I am after a function or functions that can show the hierarchical representation of ALL ancestors of a given element.
Even if the element and certain ancestors occur multiple times in the dimension.

I am not missing something I do not see a way to apply Members on a set of ancestors, to hierarchize them.

[PavoGa]

So in my example, you would want to see the hierarchies for say, just Georgia and Alabama?

[PavoGa]

Sorry, Wim, I see now what you're wanting. Say, given "Coy", you want to see the hierarchies Coy is a member of represented fully from the top to Coy. Hmmmm, tricky...