How to Create Ragged/Unbalanced Hierarchy in TM1 Architect

Post Reply
User avatar
ptownbro
Posts: 18
Joined: Wed Apr 05, 2017 3:03 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013
Location: Los Angeles, CA

How to Create Ragged/Unbalanced Hierarchy in TM1 Architect

Post by ptownbro » Fri Sep 15, 2017 4:11 am

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

BariAbdul
Regular Participant
Posts: 403
Joined: Sat Mar 10, 2012 1:03 pm
OLAP Product: TM1,Cognos
Version: 9.5.2
Excel Version: 2007

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

Post by BariAbdul » Fri Sep 15, 2017 5:29 am

Please go through below post:

http://www.tm1forum.com/viewtopic.php?t=11139 Thanks
"You Never Fail Until You Stop Trying......"

User avatar
ptownbro
Posts: 18
Joined: Wed Apr 05, 2017 3:03 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013
Location: Los Angeles, CA

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

Post by ptownbro » 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?

Wim Gielis
MVP
Posts: 1538
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis » Sat Sep 16, 2017 4:55 pm

Use the Advanced > Metadata tab with functions like:
DimensionElementInsert
DimensionElementComponentAdd
If-Else-EndIf

Nothing more than that.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

lotsaram
MVP
Posts: 2998
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

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

Post by lotsaram » Sat Sep 16, 2017 8:38 pm

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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
ptownbro
Posts: 18
Joined: Wed Apr 05, 2017 3:03 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013
Location: Los Angeles, CA

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

Post by ptownbro » Sun Sep 17, 2017 3:24 pm

Thanks everyone. I'll give it a shot.

User avatar
ptownbro
Posts: 18
Joined: Wed Apr 05, 2017 3:03 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013
Location: Los Angeles, CA

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

Post by ptownbro » Mon Sep 18, 2017 4:15 am

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;

Edward Stuart
Community Contributor
Posts: 191
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester

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

Post by Edward Stuart » Mon Sep 18, 2017 7:10 am

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.

User avatar
ptownbro
Posts: 18
Joined: Wed Apr 05, 2017 3:03 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013
Location: Los Angeles, CA

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

Post by ptownbro » Wed Sep 20, 2017 3:10 am

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;

Post Reply