TM1 Forum

Posted: **Tue Aug 09, 2011 5:44 am**

Hi all,

I'm fairly new to TM1 TI and a bit lost/confused in trying to build this location hierarchy: ALL LOCATIONS BY BUSINESS UNITS > Assetbusinessunit > Parent > Child

Here is a sample source data set extracted from external database:

Parent, Child, Assetbusinessunit
3000580_TOT, 3000580, EOS
3000581_TOT, 3000581, EOS
3000582_TOT, 3000582, EOS
3000583_TOT, 3000583, EOS
3000584_TOT, 3000584, EOS
3000585_TOT, 3000585, EOS
LEGACY ALL BUSINESS UNITS_TOT, 3000580_TOT, EOS
LEGACY ALL BUSINESS UNITS_TOT, 3000581_TOT, EOS
LEGACY ALL BUSINESS UNITS_TOT, 3000582_TOT, EOS
LEGACY ALL BUSINESS UNITS_TOT, 3000583_TOT, EOS
LEGACY ALL BUSINESS UNITS_TOT, 3000584_TOT, EOS
LEGACY ALL BUSINESS UNITS_TOT, 3000585_TOT, EOS

Here is the TI I used to build the hierarchy:

Code: Select all

**prolog tab**
cDimName = 'Maximo Location' ;
cBusUnit = 'ALL LOCATIONS BY BUSINESS UNITS';

**metadata tab**
IF(DIMIX(cDimName, Child)=0);
      DIMENSIONELEMENTINSERT(cDimName, '', Child, 'N');
ENDIF;

IF(DIMIX(cDimName, Parent)=0);
      DIMENSIONELEMENTINSERT(cDimName, '', Parent, 'C');
ENDIF;
      DIMENSIONELEMENTCOMPONENTADD(cDimName,Parent, Child, 1);

IF(DIMIX(cDimName, Assetbusinessunit)=0);
      DIMENSIONELEMENTINSERT(cDimName, '', Assetbusinessunit, 'C');
ENDIF;
     DIMENSIONELEMENTCOMPONENTADD(cDimName, Assetbusinessunit, Parent, 1);

IF(DIMIX(cDimName, cBusUnit)=0);
      DIMENSIONELEMENTINSERT(cDimName, '', cBusUnit, 'C');
ENDIF;
      DIMENSIONELEMENTCOMPONENTADD(cDimName,cBusUnit, ASSETBUSINESSUNIT, 1);

My expected result of the hierarchy would be:

ALL LOCATIONS BY BUSINESS UNITS (c)
--EOS (c)
----LEGACY ALL BUSINESSUNITS_TOT (c)
------300580_TOT (c)
--------300580 (n)

When I execute my TI process and view the expanded dimension, both Child and Parent appears twice, once under 'EOS' (Assetbusinessunit)and once under 'ALL LOCATIONS BY BUSINESS UNITS' (see attached screenshot "hierarchy.JPG"). However looking at a leaf elements (e.g. 300580), both have the same index (4).

So my question is have I done something wrong with the TI, or do I have to tune the source data extract SQL, or something completely different?

Hope someone can provide some guidance or point me to the right post to try and resolve this.

Cheers.

Ray

Posted: **Tue Aug 09, 2011 6:20 am**

Hi! Ray,

I think what causes this duplication in your hierarchy, is the source. Notice for example 3000580_TOT, where it appears to be in both Child and Parent columns. I guess if your goal is to set each cell data to appear only once (as elements) in that hierarchy, you should arrange your source data so each cell data (your fetched SQL data) will appear only once in that hierarchy, that is making sure that each cell data appears only on that column and not on the other columns. Or another way, you can do a concatenation for both Child and/or Parent 3000580_TOT entries inside the TI, so you don't have to arrange you source data anymore and that they'll be unique and different from each other. I hope that helps.

Thanks.

TM1 Forum

hierarchy duplicate

hierarchy duplicate

Re: hierarchy duplicate