TI that will insert element on top of its four parents, but not at top of hierarchy

[wigglyrat]

Hi - I have the following TI that builds a dimension of offices (all offices have an 'agency number') with a hierarchy as follows (code below):

All Agency Numbers (top level)
|-Head office
|-|-Division
|-|-|-Region
|-|-|-|-Branch

There is a separate dimension of Sales People (referred to as BDE's). Each BDE is in charge of a number of offices.

I would like to change the hierarchy above to be as follows:

All Agency Numbers (still top level)
|-BDE
|-|-Head office
|-|-|-Division
|-|-|-|-Region
|-|-|-|-|-Branch

I am very new to TM1. Can anyone please advise me how the TI should be modified to do this?

Many thanks in advance,

Code: Select all

lng_Index = 0;

# check whether the agency number exists
lng_Index = DimIx( s_DimzzBrokerNo, vAgencyNumber );

If ( lng_Index = 0 & Load_zzBDE = 1 );
# agency number not found

    # check whether the head office exists
    # if the index returns zero (not found) add it to 'All' hierarchy at consolidation level
    lng_Index = DimIx( s_DimzzBrokerBDE, vHeadOffice_ICEref );
    If ( lng_Index = 0 );
        DimensionElementInsert( s_DimzzBrokerBDE, '', vHeadOffice_ICEref, 'C');
        DimensionElementComponentAdd( s_DimzzBrokerBDE, 'All Agency Numbers', vHeadOffice_ICEref, 1);
    EndIf;

    # check whether the division exists
    # if the index returns zero (not found) add it to head office as consolidation level
    lng_Index = DimIx( s_DimzzBrokerBDE, vDivision_ICEref );
    If ( lng_Index = 0 );
        DimensionElementInsert( s_DimzzBrokerBDE, '', vDivision_ICEref, 'C');
        DimensionElementComponentAdd( s_DimzzBrokerBDE, vHeadOffice_ICEref, vDivision_ICEref, 1);
    EndIf;
    
    # check whether the region exists
    # if the index returns zero (not found) add it to division as consolidation level
    lng_Index = DimIx( s_DimzzBrokerBDE, vRegion_ICEref );
    If ( lng_Index = 0 );
        DimensionElementInsert( s_DimzzBrokerBDE, '', vRegion_ICEref, 'C');
        DimensionElementComponentAdd( s_DimzzBrokerBDE, vDivision_ICEref, vRegion_ICEref, 1);
    EndIf;

    # check whether the branch office exists
    # if the index returns zero (not found) add it to region as consolidation level
    lng_Index = DimIx( s_DimzzBrokerBDE, vBranchOffice_ICEref );
    If ( lng_Index = 0 );
        DimensionElementInsert( s_DimzzBrokerBDE, '', vBranchOffice_ICEref, 'C');
        DimensionElementComponentAdd( s_DimzzBrokerBDE, vRegion_ICEref, vBranchOffice_ICEref, 1);
    EndIf;

    # load the agency number to the branch office
    DimensionElementComponentAdd( s_DimzzBrokerBDE, vBranchOffice_ICEref, vAgencyNumber, 1);

EndIf;

[tomok]

The "I'm new to TM1" card can only be played when you've exhausted your own efforts to find a solution. What have you tried so far? Seems to me you just need to add a section just above the "Head Office" section to do the same thing for the BDE.

[wigglyrat]

Thanks for your assistance. The BDE information is pulled in via a different TI. This data comes from a csv file that lists BDE's and agencies that they look after.
The TI above pulls data from the data warehouse so that particular variable vBDE is available only in the csv file/different TI.
I generally don't know where to begin; I can see it would be easy enough to do if vBDE were available in this TI but it is not.

[wigglyrat]

I have investigated the use of ELPAR but I believe this is not ideal as the hierarchy has four levels and would need a nested ELPAR function

[tomok]

Thanks for your assistance. The BDE information is pulled in via a different TI. This data comes from a csv file that lists BDE's and agencies that they look after.
The TI above pulls data from the data warehouse so that particular variable vBDE is available only in the csv file/different TI.
I generally don't know where to begin; I can see it would be easy enough to do if vBDE were available in this TI but it is not.

Don't you think this would have been relevant information to include in your original post? How in the world could anyone have provided a solution without knowing this?

Why do you think you need a nested ELPAR? Seems to me you can just modify the first process to NOT add the Offices anywhere (just let them be orphan nodes and in your second process (the one with the Office/BDE relationship do the following:

• 1. Add the BDE element to the dimension

• 2. Make the corresponding Office a component of the BDE

• 3. Make the BDE a component of All Agency Numbers