Ignore a row based on a value while data loading in TM1 cube

[sreesuku]

Hi All,

During data loading in to a TM1 cube, Is it possible to ignore an entire row if a specific value is there in the data file.
Eg : I have 4 dimensions Period,Country, Departments,Measures
My data file is having a entries like
Row 1) Jan, India,ABC,40
Row 2) Feb,India,BCD,30
Row 3) Mar,China,ABC,20

I want to exclude a row if dept = ABC. Here rows 1 and 3 should be excluded while data loading.
Can we write some code to do this ??

Regards
Sree

[mikko]

Hi Sree,

It's pretty simple:
1. Do "normal" TI-script to load data in cube "as-is"
2. Go Advanced tab and choose Data tab
3. Copy CellPutN/CellPutS line from "generated" section and paste it below this section
4. In variables tab change your data variable content from "data" to "other"
5. Go again in Advanced-->Data tab and add your if-condition to line you copied from generated section (e.g. if(vDepartment@='ABC', CellPutN(....),.....))
6. Test

-Mikko

[David Usherwood]

Think it'd be easier to code an ItemSkip before the existing generated code:

Code: Select all

if(dept @= 'ABC');
itemskip;
endif;
****Generated Code Start****

[sreesuku]

Thanks Davida and Mikko..
I will check your options..
One more query.. will this have an impact on my data loading time if we have more number of records ?? Because it has to check for the value and ignore the rows ??
I have more than one such conditions.. If it takes more time to load I have ignore these values while creating the input file itself(Which is a tough process considering my current query). Whats your opinion ?

Regards
Sree

[lotsaram]

.. will this have an impact on my data loading time if we have more number of records ?? Because it has to check for the value and ignore the rows ??
I have more than one such conditions.. If it takes more time to load I have ignore these values while creating the input file itself(Which is a tough process considering my current query). Whats your opinion ?

The overhead for performing such a conditional check is minimal, and it is also balanced by not performing any further actions for the record if the skip conditions are met. I don't think you would notice and difference in processing time.

[tomok]

Only you can make the decision as the whether it is worth your time to modify the input source to exclude the records you don't want or just do an ItemSkip in TI to ignore them. It probably depends on how big the file is and how much TI processing time you would save by excluding them in the input file.