Load Parent Child Hierarchies from ODBC into TM1

[sqzeezer]

Hello everybody,
I have the following problem:

I want to import a parent child hierarchy from a relational database to a TM1 dimension.
The relational database looks like this:

Code: Select all

Parent | Child
A          A
A          B
B          C
A          D
E          E
E          F
...

So the dimension I want to import is the following:

Code: Select all

A
|-B
| |-C
|-D
E
|-F
...

I tried the following:
In the turbo integrator I specified the parent variable as "Consolidation" and the Child as "Element" and a mapping Cons. Variable = Parent - Child Variable = Child. My problem now is, that when the dimension is imported, it is only imported for the first level of the relation. (In the example I gave, C would not be a child of B, but would reside on the same level as A (toplevel)) How can i implement a process to recursively import all levels of the dimension? What am I missing? Or is this possible at all?

Thanks for your help!

Marc

[Alan Kirk]

Hello everybody,
I have the following problem:

I want to import a parent child hierarchy from a relational database to a TM1 dimension.
The relational database looks like this:
{Snip}

I tried the following:
In the turbo integrator I specified the parent variable as "Consolidation" and the Child as "Element" and a mapping Cons. Variable = Parent - Child Variable = Child. My problem now is, that when the dimension is imported, it is only imported for the first level of the relation. (In the example I gave, C would not be a child of B, but would reside on the same level as A (toplevel)) How can i implement a process to recursively import all levels of the dimension? What am I missing? Or is this possible at all?

I suggest dumping the automatically generated code, for verily it is an abomination. Instead set all of your variables to Other (rather than Element or what have you), then craft the code yourself.

This should do what you want; it goes in the Metadata tab of the process:

Code: Select all

#****Begin: Generated Statements***
#****End: Generated Statements****

# In reality, better to define this in the Prolog
# Change to the name of your own dimension
s_Dim = 'zTestDim1';

l_Idx = 0;

# Check whether the child is already there.
l_Idx = DimIx( s_Dim, Child);

#Add it if it's not.
If( l_Idx = 0);

# Doesn't matter if this is a consolidation; it'll convert from
# N to C when you add a child to it.

    DimensionElementInsert(s_Dim, '', Child, 'N');

EndIf;

#Skip the rows where parent and child are the same
If (Parent @=Child);
    ItemSkip;
EndIf;

# Otherwise, add the child
DimensionElementComponentAdd( s_Dim, Parent, Child, 1);

[Alan Kirk]

This should do what you want; it goes in the Metadata tab of the process:

One point to note; the code assumes that the records are returned in hierarchy order as per your sample. That is, that a parent will always have been added as a "child" before an attempt is made to add any children of its own to it. If that's not the case, the code will require some slight modification to add the parent (if necessary) using DimensionElementInsert as well.

[sqzeezer]

This should do what you want; it goes in the Metadata tab of the process:

One point to note; the code assumes that the records are returned in hierarchy order as per your sample. That is, that a parent will always have been added as a "child" before an attempt is made to add any children of its own to it. If that's not the case, the code will require some slight modification to add the parent (if necessary) using DimensionElementInsert as well.

Hello Alan,

Thanks a lot for your fast answer! I have already tried your code and it works fine,except for the assumption oh hierarchy ordering you mentioned.
How can I use DimensionElementInsert to avoid this problem? How can i use this to take care of this problem at the different levels of the hierarchy?

I really appreciate your help!

Thanks,
Marc

[Alan Kirk]

This should do what you want; it goes in the Metadata tab of the process:

One point to note; the code assumes that the records are returned in hierarchy order as per your sample. That is, that a parent will always have been added as a "child" before an attempt is made to add any children of its own to it. If that's not the case, the code will require some slight modification to add the parent (if necessary) using DimensionElementInsert as well.

Thanks a lot for your fast answer! I have already tried your code and it works fine,except for the assumption oh hierarchy ordering you mentioned.
How can I use DimensionElementInsert to avoid this problem? How can i use this to take care of this problem at the different levels of the hierarchy?

As easily as this; just add another If block. It shouldn't matter what level the element is at. I tested this out with a modified version of your data and it worked fine:

Code: Select all

#****Begin: Generated Statements***
#****End: Generated Statements****

# In reality, better to define this in the Prolog
s_Dim = 'zTestDim1';

l_Idx = 0;

# Check whether the child is already there.
l_Idx = DimIx( s_Dim, Child);

#Add it if it's not.
If( l_Idx = 0);

# Doesn't matter if this is a consolidation; it'll convert from
# N to C when you add a child to it.

    DimensionElementInsert(s_Dim, '', Child, 'N');

EndIf;

#Skip the rows where parent and child are the same
If (Parent @=Child);
    ItemSkip;
EndIf;

#Check whether the Parent is there.
l_Idx = DimIx( s_Dim, Parent);

#Add it if it's not.
If( l_Idx = 0);

    DimensionElementInsert(s_Dim, '', Parent, 'C');

EndIf;

# Otherwise, add the child
DimensionElementComponentAdd( s_Dim, Parent, Child, 1);

[sqzeezer]

Works fine!!

Thanks a lot

[sivan307]

This is another excellent post. I was able to load the parent & the child elements from the Input template(Excel) to add new elements into the dimension. Alan, in case we have an input template that will try to insert 2 children & 2 parents at the same time, how can I do that. I am trying to load the new elements from the spreadsheet that the users try to create.

Thanks,
Kal

Environment: TM1 9.5.1, Excel 2007.

[lotsaram]

This is another excellent post. I was able to load the parent & the child elements from the Input template(Excel) to add new elements into the dimension. Alan, in case we have an input template that will try to insert 2 children & 2 parents at the same time, how can I do that. I am trying to load the new elements from the spreadsheet that the users try to create.

Thanks,
Kal

Kal I'm not sure what you 're getting at here as the example and code will work for any 2 column FLAT file that has parents in the first column and children in the second column. It will work for adding an unlimited number of parents and children in a single run.

If you mean you want to create multiple hierarchies then you can do this by modifying the flat file input, there is no need to modify any code.

For example if you want to create a rollup "A" with 3 children A1, A2 and A3 then the flat file would need to contain the following 3 records:
A,A1
A,A2
A,A3

If you wanted element A1 to roll up into 2 parents A and AA then the file would need to contain the following 2 records:
A,A1
AA,A1

Of course standard watchouts for multiple hierarchies apply. Make sure you don't end up double-counting your data.

[lotsaram]

#Add it if it's not.
If( l_Idx = 0);

# Doesn't matter if this is a consolidation; it'll convert from
# N to C when you add a child to it.
DimensionElementInsert(s_Dim, '', Child, 'N');

EndIf;

As Alan mentioned in his code TM1 will automatically convert a simple element into a consolidation the moment you add a child to it without asking. One tip that is worth mentioning is that although this is often useful it is a double-edged sword as it can also be quite dangerous in the instances where what was previously a posting level item in the source system becomes a parent due to a reclassification or change in chart of accounts or whatever. Your RDBMS source system may not care and quite comfortably hold entry level data against an code and sum up its children simultaneously quite happily but this is not the case with TM1. If an N level element in a TM1 dimension which holds cube data is converted to a consolidation in effect the N element has been destroyed and an new C element created and any leaf data (excluding attribute and string data) associated with the element will also consequently be destroyed.

You need to be careful to avoid this happening and where this is not possible reload historical data.

[sivan307]

lotsaram, really appreciate your help. what I am trying to do is not load the values from the flat file, but to load the values from an input template that the users use to input/create new parent,child elements in a dimension. I have an action button that will run the TI process to add these combinations to the dimension.

So when I re-used Alan's script, I can load a single parent-child combination. so In the Advanced --> Parameters tab, I have 2 parameters Parent & Child. This can handle a single combination. Now if I want the script to handle the second combination, I will need to add 2 more parameters and then add an if statement to add those combination. What I am looking at is, do we have another way of doing it.

To sum up, if I have to add 2 or more parent-child combinations from an input template that is not a flat file and the values are input by the users. Using an action button to run the TI process that will allow us to add 2 or more combination. How can I re-use Alan's script to achieve the said result..

This is what I have right now..
Advanced --> Parameters

Parent -- vp1,vp2
Child -- v1,v2

Prolog :

IF (v1@<>'');
DimensionElementInsert('Biological Objectives','',v1,'N');
IF (vp1@<>'');
DimensionElementComponentAdd('Biological Objectives',vp1,v1,1);
ENDIF;
ENDIF;

IF (v2@<>'');
DimensionElementInsert('Biological Objectives','',v2,'N');
IF (vp2@<>'');
DimensionElementComponentAdd('Biological Objectives',vp2,v2,1);
ENDIF;
ENDIF;

Sorry for the confusion. Once again Thanks for your replies.

Kal

[tomok]

To sum up, if I have to add 2 or more parent-child combinations from an input template that is not a flat file and the values are input by the users. Using an action button to run the TI process that will allow us to add 2 or more combination.

I'll be brutally honest with you here. This is not a good design choice. If you want to have the ability to create multiple child/parent relationships in the dimension, passing those choices as a parameter is not a good idea because you are going to be locked into a certain number of "possible" combinations. Basically you will need two parameters for each relationship. If you build it to handle 5 relationships at a time (10 parameters), what happens when you decide you want to have 6, or 7, or 15? Let's say you build it to handle 10, what if the user only wants to process 3? You've got to have logic to skip the other 7.

You need to process a data source so those records can come in one at a time and you can just have one set of logic in the Metadata tab that will do the same thing regardless of how many relationships you are handling. You have several options for doing this:

1. Have a share somewhere that both the users and the TM1 service have access to and put VBA code in the template that will save a portion of your template as a CSV file in that share. Then your action button can kick-ofd a TI process that loads that file.
2. Same idea as 1 but have a table in an ODBC database instead of a flat file.
3. Write your choices to a two dimensional cube, or three dimensional using }Clients dimension. Create view on that cube that can be data source for TI process. DBS formula in template writes values to cube. Action button kicks off TI process that loads data from view and processes dimension changes.

[sivan307]

Excellent suggestion Tomok. Even I thought this is not a good practice & was looking for a possible solution Right at this moment the client wants to load just the 2 combinations and so to get the template live, I did go for hard coding. But then I started thinking, what will happen if the client decided to add a few more combinations. Someone needs to modify the code again to fit the requirements.

I am already working on the 2nd solution of getting the values into the Sql Server database & have the script pull the values from the table. But how can we make sure every time some user enters values into the template & the table is loaded with new values, how can we make sure the TI process kicks in. I can schedule chores to run this process at a specified time. Do we have an option of running the chore as soon as we get the new values ?

I am really not good with the VBA code, so your 3rd option looks a perfect match for me right at this moment.

Thanks once again for your help.
Kal

[tomok]

But how can we make sure every time some user enters values into the template & the table is loaded with new values, how can we make sure the TI process kicks in. I can schedule chores to run this process at a specified time. Do we have an option of running the chore as soon as we get the new values ?

Just because you are putting the values in an SQL table doesn't mean you still can't kick off the TI with an action button. Have two buttons; one that writes the records into the table and another that kicks off the dimension update process. One note here, you will probably want to have a column in this table called "User" so that their records can be kept separate from the others. You can grab this value from the TM1USER function when adding the records. You'll also want to put a WHERE clause in your SQL query in the TI process to limit the records to just those for that user (you can pass that in as a parameter in the TI). You'll also want to have a column called "Processed" and put a 1 in there after the TI has finished (you can put SQL command in Epilog) or you could truncate the records, so you don't process then again.

[sivan307]

Tomok:

Excellent suggestion of having 2 action buttons one for loading the values into the table and another to run the TI process to update the dimension, not to mention having 2 columns one for Users & another for Processed records in the table. I really did not think in those terms.

Thanks once again Tomok & Lotsaram.
Kal

[braypa]

The code works fine if your hierarchy is in order. But if the parent child table is not in order, there's a problem.

If my hierarchy is universe-->solar system-->earth-->united states suppose my parent child table looks like

Parent | Child
Universe | Solar System
Earth | United States
Solar System | Earth

When I run the code sample above, Earth never gets assigned as a child of Solar System. What am I missing?

[Alan Kirk]

The code works fine if your hierarchy is in order. But if the parent child table is not in order, there's a problem.

If my hierarchy is universe-->solar system-->earth-->united states suppose my parent child table looks like

Parent | Child
Universe | Solar System
Earth | United States
Solar System | Earth

When I run the code sample above, Earth never gets assigned as a child of Solar System. What am I missing?

Which code? A bunch of people have posted different code for different purposes in this thread.

If it's the code in the last post that I made prior to this one, I assure you that it does work regardless of order:

Hierarchy.jpg (108.07 KiB) Viewed 15717 times

However that code doesn't sort the dimension (you'd need to add DimensionSortOrder for that) so make sure that you're expanding from Universe down to open up each individual node. If you just open the subset editor and hit the Select All Elements button then United States will probably appear to be sitting there on its own... but it isn't.

[paulsimon]

Another alternative is just

DimensionElementInsert(vDim,'',vChild,'n') ;

IF( vParent @<> vChild ) ;
DimensionElementInsert(vDim,'',vParent,'n') ;
DimensionElementComponentAdd(vDim, vParent, vChild, 1) ;
ENDIF l

Set vDim to the name of your dimension on the Prolog Tab

vParent and vChild are the name of your variables.

Regards


Paul Simon

[braypa]

Problem was not with the TM1 process but with an illegal character in the DB2 source. One of the text strings had hex'0X1A' which was interpreted as end of file and thus processing ended prematurely before the full hierarchy could be mapped.

[kingaj12]

I realize this is an old post. It is very useful and appears fairly high up on the Google search for TM1 hierarchy. Perhaps, hopefully, this post is still being monitored.

I have used Alan's code (thanks Alan!) and it works wonderfully. However you do have to set the Parent and Child variable content field to "Other".

Is there a way to do this using Turbo Integrator functions?

I am using TM1 10.2.

Thanks a lot everyone for posting this thread!

Alan King

[tomok]

I realize this is an old post. It is very useful and appears fairly high up on the Google search for TM1 hierarchy. Perhaps, hopefully, this post is still being monitored.

I have used Alan's code (thanks Alan!) and it works wonderfully. However you do have to set the Parent and Child variable content field to "Other".

Is there a way to do this using Turbo Integrator functions?

I am using TM1 10.2.

Thanks a lot everyone for posting this thread!

Alan King

The variable content types in TI only exist to help TI generate wizard code. If you are writing your own code, like this thread is showing, then all your variables would be set to "Other" already.