Page 1 of 1
Ignore a row based on a value while data loading in TM1 cube
Posted: Thu Dec 08, 2011 7:12 am
by 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
Re: Ignore a row based on a value while data loading in TM1
Posted: Thu Dec 08, 2011 8:50 am
by 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
Re: Ignore a row based on a value while data loading in TM1
Posted: Thu Dec 08, 2011 9:52 am
by 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****
Re: Ignore a row based on a value while data loading in TM1
Posted: Thu Dec 08, 2011 11:18 am
by 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
Re: Ignore a row based on a value while data loading in TM1
Posted: Thu Dec 08, 2011 12:54 pm
by lotsaram
sreesuku wrote:.. 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.
Re: Ignore a row based on a value while data loading in TM1
Posted: Thu Dec 08, 2011 12:56 pm
by 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.