The TM1 CellIsUpdateable function allows us to test if a cell in a cube can be written to. If true, the function returns a 1, if false a 0.
This is really useful when you have a TI process and you are not sure if the cells referenced by the source data are writable or not. For example, let’s say we are using a TI to move data from one cube to another. We have a process that exports an entire year of data from the source and a rule in the target that becomes active as the year progresses. We only want to load data for those periods that don’t have a rule and will use CellIsUpdateable to exclude the records where there is a rule in the target cell.
Syntax of TM1 CellIsUpdateable
The syntax is
CellIsUpdateable(Cube, element1, element2, elementn);where:
- Cube, is the cube you are testing,
- Element1, is an element from the first dimension of that cube that is at the intersection to be tested,
- Element2, is an element from the second dimension of that cube that is at the intersection to be tested,
- Elementn, is an element from the nth dimension of that cube that is at the intersection to be tested.
Note that the elements must be in dimension order and you must have an entry for each dimension in a cube. Obviously you can use aliases in place of elements if required.
Usage
Typically we use CellIsUpdateable inside an If statement to test if a cell is able to be written to and then if so, to attempt to write to it, while if not, then we Skip the intersection.
Why would we use it? Using TM1 CellIsUpdateable allows us to avoid TI errors when we try to write to a rule driven cell. Thus we get clean processes that also run faster.
Example
Building on the use case above, we could use CellIsUpdateable as follows:
IF ( CellIsUpdateable(Sales, vYear, vMonth, vProduct, vMeasure) = 1); CellPutN (50000, Sales, vYear, vMonth, vProduct, vMeasure);
ELSE;
ITEMSKIP;
ENDIF;This can obviously be written with either a CellPutN, CellIncrementN or CellPutS, but not a CellIncrementS! The ItemSkip does like it says, skips the record from the source.
