Remove the restriction of ELPAR

[macsir]

Just want to share my thoughts to remove the restriction of ELPAR. We all know that the last para is the seq of the parent, but sometimes if the dim has multiple hierarchies and hierarchies sequence could be refreshed dynamically. How can we dynamically know the right seq of the parent for an element?
e.g. orginial code

Code: Select all

par = ELPAR('Department',Dept,1);

it is getting the first fixed parent of an element.

Add a loop before this code, dynamically get position for parent of each department, which is stored in par_pos

Code: Select all

par_pos = 0;
par_i = 1;
par_size = DIMSIZ('Department');
WHILE (par_i <= par_size);
    par_name = ELPAR('Department',Dept,par_i);
    IF( (par_name @<> '') & (ELISANC('Department','Right Root Element',par_name) = 1));
       par_size = 0;
       par_pos = par_i;
    ENDIF;
    par_i = par_i + 1;
END;

par = ELPAR('Department',Dept,par_pos);

If any better idea, please share.

[rmackenzie]

Just want to share my thoughts to remove the restriction of ELPAR.

The usual advice is to avoid use of ELPAR in dimensions that have multiple hierarchies. If you have dimensions with only a single hierarchy, there need not be talk of a restriction on the use of ELPAR. If you can control the indexing of your dimensions that do control multiple hierarchies (e.g. using XDI) then once again ELPAR may be used with some caution.

Really, the restriction really applies to the use of ELPAR in rules, rather than TI. As you've shown, you can think about work-arounds in TI. In rules, however, it's not really possible to program workarounds and really the hierarchy index needs to be known and fixed for a rule to be consistent over time. The problem of a change to dimension indexing (through a dimension update) changing the order of the hierarchies means that use of ELPAR could have to restricted in certain models.

[Martin Ryan]

I agree with Robin. I would use attributes instead of ELPAR, e.g. "parent1" and "parent2". This gives you complete control over where you're looking.

I do, as it happens, have a similar example to yours where I use TI to check if any Client has multiple parents. There are two hierarchies so I need to check them both - e.g. each element should have two parents, one from each hierarchy. This does get a bit messy and I use elisanc in a similar fashion to your example to check whether the parent belongs to hierarchy 1 or hierarchy 2. I guess this could be applied to rules as well as TI, but it'd be messy (my TI code is pretty convoluted, I think the rules would be worse).

So I guess I haven't followed my own advice there. Maybe I'll change that process to use attributes...

[macsir]

Thanks for sharing, both.

Yes, it is true. I would try to use attribute or index to control them next time.

[rmackenzie]

Macsir, ELISANC is a relatively 'expensive' function in TM1 because the engine has to work out if two elements are indirectly linked in what could be a very deep hierarchy. Because you have a loop using DIMSIZ and not ELPARN your code is at risk of being slower and inefficient than it could be because if Dept's parents never roll-up to Right Root Element then you still do the loop for the number of elements in the dimension; which could be a lot. I would update as following:

Code: Select all

nParentIndex = 0;
nParentCounter = 1;
nMaxParents = ELPARN ( 'Department', sDepartment );

WHILE ( nCounter <= nParentCounter );

  # get parent and check its relation to the root element
  sParent = ELPAR ( 'Department', sDepartment, nParentCounter );
  nCheck = ELISANC ( 'Department', 'Right Root Element', sParent );

  IF ( nCheck = 1 );
    # found the index
    nParentIndex = nParentCounter;
    # break loop
    Break;
  ENDIF;

  nParentCounter = nParentCounter + 1;

END;

[macsir]

Macsir, ELISANC is a relatively 'expensive' function in TM1 because the engine has to work out if two elements are indirectly linked in what could be a very deep hierarchy. Because you have a loop using DIMSIZ and not ELPARN your code is at risk of being slower and inefficient than it could be because if Dept's parents never roll-up to Right Root Element then you still do the loop for the number of elements in the dimension; which could be a lot. I would update as following:

Code: Select all

nParentIndex = 0;
nParentCounter = 1;
nMaxParents = ELPARN ( 'Department', sDepartment );

WHILE ( nCounter <= nParentCounter );

  # get parent and check its relation to the root element
  sParent = ELPAR ( 'Department', sDepartment, nParentCounter );
  nCheck = ELISANC ( 'Department', 'Right Root Element', sParent );

  IF ( nCheck = 1 );
    # found the index
    nParentIndex = nParentCounter;
    # break loop
    Break;
  ENDIF;

  nParentCounter = nParentCounter + 1;

END;

Yes, thanks for reminding. I will update my codes.