How to write to range of cells within TM1 Cube

[Ashleigh W]

Hello again Experts, is there a way to write to range of (level 0 elements ) cells within TM1 Cube like in Rule we can specify a range we want to populate? For example CellPutN or CellPutS can only write to single point of intersection.

Please advise.

[ascheevel]

To clarify, you're wanting to use a TI to CellPut to multiple intersections from one source record in the TI datasource? You can do that with multiple CellPut statements in your TI or you can use a WHILE loop and iterate through a list of target elements.

[Ashleigh W]

If I have a Parent Element (A), can I have a TI Process to write 'X' to measure 'Flag' all the children of A instead of having to loop?

Currently my code looks something like this in data tab. Is there another way to write to cells instead of loop through tDIM?

Code: Select all

tDIM = 'Cost Center';

Idx = 1;
MaxIdx=DIMSIZ(tDIM);
WHILE(i<= MaxIdx);
  Elem = DIMNM(tDIM,i);
  IF(ELLEV(tDIM,Elem) = 0);
    
	IF(ELISANC(tDIM,vParent,Elem) > 0);
		CELLPUTN(....);
	ENDIF;
	
  ENDIF;
  i =i +1;
END;

[Wim Gielis]

This is pretty much okay.

One thing to look at, and in opinion not much of a gain or better solution, is to use CellPutProportionalSpread in TI at the level of vParent. This would avoid looping but it opens another can of worms potentially.

If your cube design and requirements are fine, then this looping strategy is fine.

[ascheevel]

CellPutProportionalSpread might be an option, but that would only apply to leaf elements that already have a non-zero value and would do a proportional spread and not a repeat that the code you posted implies you're trying to do. I mention this as one option, but I don't think it's much value to your use case.

Is your issue with the code you have that it's slow performance due to looping through all elements of the dimension for each data record? ELISANC is also an expensive operation on large dims where a leaf element is a child of multiple rollups. Do you need to use ELISANC because your vParent is not level 1 in the dimension and there may be intermediate consolidations between vParent and the leaf elements? If your vParent will always be level 1 and you simply want to loop through the leaf level children you could use code like the below for better performance.

Code: Select all

tDIM = 'Cost Center';

i = ELCOMPN(tDIM, vParent)
WHILE(i > 0);
	Elem = ELCOMP(tDim, vParent, i);
	IF(ELLEV(tDim, Elem) = 0);
		CELLPUTN(....);
 	ENDIF;
	i = i - 1;
END;

[Ashleigh W]

Thanks for the insight / very useful information. I'll definitely try CellPutProportionalSpread.

[PavoGa]

CellPutProportionalSpread will not perform very well. In my testing it is a performance dog compared to looping. I very much like the idea of it, but there are simply faster ways of doing what you are describing.

[Ashleigh W]

PavoGa - I tried CellPutProportionalSpread and it is fast enough but I actually wanted to fill/write String values. Loop as well worked pretty much the same however I am interested to know if there are other options too.

[Wim Gielis]

For texts your options are even more limited compared to numbers.
Depending on the cube design and what you want to achieve maybe a rule can help here, to populate cells in memory. But I doubt it very much. Stick to loops.

[Ashleigh W]

Thanks again Wim for your help.