Page 1 of 1

How to Create Ragged/Unbalanced Hierarchy in TM1 Architect

Posted: Fri Sep 15, 2017 4:11 am
by ptownbro
I've seen some resources on how to do this in Performance Modeler, but need to know how to do this in TM1 Architect using an ODBC database source (e.g. not using manual editing or cut/copy and pates). Can someone direct me to how to do this?

Simple database source example. Either using Parent-Child hierarchy or Levels hierarchy table setups (either way).

IDNameParentIDTreeTypeLevel1Level2Level3Level4
1Revenues8CTotal P&LGross ProfitRevenuesNull
2COGS8CTotal P&LGross ProfitCOGSNull
3Labor10CTotal P&LTotal ExpensesTotal Labor & BenfitsLabor
4Benefits10CTotal P&LTotal ExpensesTotal Labor & BenfitsBenefits
5Expenses9CTotal P&LTotal ExpensesExpensesNull
6Other7CTotal P&LOtherNullNull
7Total P&LNullPNullNullNullNull
8Gross Profit7PNullNullNullNull
9Total Expenses7PNullNullNullNull
10Total Labor & Benfits9PNullNullNullNull

Re: How to Create Ragged/Unbalanced Hierarchy in TM1 Architect

Posted: Fri Sep 15, 2017 5:29 am
by BariAbdul
Please go through below post:

http://www.tm1forum.com/viewtopic.php?t=11139 Thanks

Re: How to Create Ragged/Unbalanced Hierarchy in TM1 Architect

Posted: Sat Sep 16, 2017 3:31 pm
by ptownbro
I went through the post and reached the documentation. Thank you for that.

Are you saying the only way to create this type of hierarchy is to do it through code on the Advanced tab? In other words, TM1 Architect cannot naturally handle it by, for example, method of importing/loading data and through it's TI processes?

Re: How to Create Ragged/Unbalanced Hierarchy in TM1 Architect

Posted: Sat Sep 16, 2017 4:55 pm
by Wim Gielis
Use the Advanced > Metadata tab with functions like:
DimensionElementInsert
DimensionElementComponentAdd
If-Else-EndIf

Nothing more than that.

Re: How to Create Ragged/Unbalanced Hierarchy in TM1 Architect

Posted: Sat Sep 16, 2017 8:38 pm
by lotsaram
ptownbro wrote: Sat Sep 16, 2017 3:31 pm I went through the post and reached the documentation. Thank you for that.

Are you saying the only way to create this type of hierarchy is to do it through code on the Advanced tab? In other words, TM1 Architect cannot naturally handle it by, for example, method of importing/loading data and through it's TI processes?
Depends on the datasource.
If the dimension table is pivoted so that you only have 2 columns
Element | Parent
... then you can build whatever hierarchy you like. This is really the preferred format if the source system supports it (and in most cases shouldn't be too much trouble to prepare a SQL query to do this).

With this format you can build whatever hierarchy you like in TM1, however many levels, whether ragged or balanced. And all with one line of DimensionElementComponentAdd code on the Metadata tab (which the wizard will write for you).

But the TI wizard will never do error handling, parametization, variable data source & target, case checking, code branching, data cleansing, etc. Ultimately you need to do at least one of these activities 99% of the time which is why it is better to just ignore the wizard and write good quality code.

Re: How to Create Ragged/Unbalanced Hierarchy in TM1 Architect

Posted: Sun Sep 17, 2017 3:24 pm
by ptownbro
Thanks everyone. I'll give it a shot.

Re: How to Create Ragged/Unbalanced Hierarchy in TM1 Architect

Posted: Mon Sep 18, 2017 4:15 am
by ptownbro
I've fully gone through and tested the script in the documentation. After going through it several times. It's not working as expected.

It's creating the hierarchy well enough, but a lot of the elements and parents show up more than once. For example:

G1
--Total
----North
------TK1
--------G1
--------G2
[snip]

It appears that the combination of the 'DIMENSIONELEMENTINSERT' and 'DIMENSIONELEMENTCOMPONENTADD' adds the element more than once. I tried it on a basic example and the results ended up with duplication as well

In the following basic example:

Code: Select all

DIMENSIONDESTROY('MyDim');
DIMENSIONCREATE('MyDim');
DIMENSIONELEMENTINSERT('MyDim','','MyChild','n');
DIMENSIONELEMENTINSERT('MyDim','','MyParent','c');
DIMENSIONELEMENTCOMPONENTADD('MyDim','MyParent','MyChild',1.000000);
I got the a result where the 'MyChild' element repeated twice:
MyChild
--MyParent
----MyChild

Is there some setting I need to change or...?

Here's the code I used from the user guide:

Prolog

Code: Select all

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

DIMENSIONDESTROY('Unbalanced');
DIMENSIONCREATE('Unbalanced');
Metadata

Code: Select all

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

IF (G1@<>'');
    DIMENSIONELEMENTINSERT('Unbalanced','',G1,'n');
    DIMENSIONELEMENTINSERT('Unbalanced','',TOTAL,'c');
    DIMENSIONELEMENTINSERT('Unbalanced','',NORTH,'c');
    DIMENSIONELEMENTINSERT('Unbalanced','',TK1,'c');
    DIMENSIONELEMENTCOMPONENTADD('Unbalanced',TOTAL,NORTH,1.000000);
    DIMENSIONELEMENTCOMPONENTADD('Unbalanced',NORTH,TK1,1.000000);
    DIMENSIONELEMENTCOMPONENTADD('Unbalanced',TK1,G1,1.000000);
ELSEIF (TK1@<>'');
    DIMENSIONELEMENTINSERT('Unbalanced','',TOTAL,'c');
    DIMENSIONELEMENTINSERT('Unbalanced','',NORTH,'c');
    DIMENSIONELEMENTINSERT('Unbalanced','',TK1,'n');
    DIMENSIONELEMENTCOMPONENTADD('Unbalanced',TOTAL,NORTH,1.000000);
    DIMENSIONELEMENTCOMPONENTADD('Unbalanced',NORTH,TK1,1.000000);
ELSE;
    DIMENSIONELEMENTINSERT('Unbalanced','',TOTAL,'c');
    DIMENSIONELEMENTINSERT('Unbalanced','',NORTH,'n');
    DIMENSIONELEMENTCOMPONENTADD('Unbalanced',TOTAL,NORTH,1.000000);
ENDIF;

Re: How to Create Ragged/Unbalanced Hierarchy in TM1 Architect

Posted: Mon Sep 18, 2017 7:10 am
by Edward Stuart
When you open the subset editor to view your dimension. It is good practice to select All elements and then to select Hierarchy Sort.

This ensures you are viewing the dimension in it's entirety and in the defined order.

To resolve the initial view of the dimension you can employ DIMENSIONSORTORDER in your TI, set a default subset or use the Dimension Element Set Order button

The various guides which come with a TM1 installation will give you more information as to why this is occuring.

Re: How to Create Ragged/Unbalanced Hierarchy in TM1 Architect

Posted: Wed Sep 20, 2017 3:10 am
by ptownbro
Thanks Edward. That did it.

The adjusted code is below.

Prolog

Code: Select all

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

DIMENSIONDESTROY('unbalanced');
DIMENSIONCREATE('unbalanced');
DIMENSIONSORTORDER('unbalanced','ByInput','ASCENDING','ByHierarchy ','ASCENDING');''
Metadata

Code: Select all

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

IF (G1@<>'');

DIMENSIONELEMENTINSERT('unbalanced','',G1,'n');
DIMENSIONELEMENTINSERT('unbalanced','',TOTAL,'c');
DIMENSIONELEMENTINSERT('unbalanced','',NORTH,'c');
DIMENSIONELEMENTINSERT('unbalanced','',TK1,'c');
DIMENSIONELEMENTCOMPONENTADD('unbalanced',TOTAL,NORTH,1.000000);
DIMENSIONELEMENTCOMPONENTADD('unbalanced',NORTH,TK1,1.000000);
DIMENSIONELEMENTCOMPONENTADD('unbalanced',TK1,G1,1.000000);

ELSEIF (TK1@<>'');

DIMENSIONELEMENTINSERT('unbalanced','',TOTAL,'c');
DIMENSIONELEMENTINSERT('unbalanced','',NORTH,'c');
DIMENSIONELEMENTINSERT('unbalanced','',TK1,'n');
DIMENSIONELEMENTCOMPONENTADD('unbalanced',TOTAL,NORTH,1.000000);
DIMENSIONELEMENTCOMPONENTADD('unbalanced',NORTH,TK1,1.000000);

ELSE;

DIMENSIONELEMENTINSERT('unbalanced','',TOTAL,'c');
DIMENSIONELEMENTINSERT('unbalanced','',NORTH,'n');
DIMENSIONELEMENTCOMPONENTADD('unbalanced',TOTAL,NORTH,1.000000);

ENDIF;