How to delete partially a Cube Tm1

[ablancof]

Hi everybody, i need your help with a problem

I want to reload the last 2 months of data into a TM1 cube. I can use the data functions to workout when the beginning of the last month was. From there I basically want to remove all data greater than that date.
The date dimension is in the format of yyyy-mm-dd.

is it posible? delete a part of a cube, last two months?

Thanks a lot

[Martin Ryan]

Yep, you want ViewZeroOut. The usual way to do it is to create a view in the prolog using functions like ViewCreate, SubsetCreate, ViewSubsetAssign and then finally ViewZeroOut.

Below is an example of how I do it. It's designed to be fairly portable with a lot of use of variables.

Code: Select all

sCube='MyCube';
sObjName='sys_' | getprocessname();
if(viewexists(sCube, sObjName)=1); viewdestroy(sCube, sObjName); endif;
ViewCreate(sCube, sObjName);

sDim='Date';
sElem='2012-11-16';
if(subsetexists(sDim, sObjName)<>1); subsetcreate(sDim, sObjName); endif;
subsetdeleteallelements(sDim, sObjName);
subsetelementinsert(sDim, sObjName, sElem, 1);
ViewSubsetAssign(sCube, sObjName, sDim, sObjName);

ViewZeroOut(sCube, sObjName);
ViewDestroy(sCube, sObjName);

You'll need some more smarts around selecting the date. I would have pStartDate and pEndDate defined as parameters in the TI process and a "counter" attribute in the date dimension. I'd then cycle through the entire date dimension looking for elements that have a "counter" value between the counter value of pStartDate and pEndDate.

[ablancof]

Hello Martin, thanks for your answer. I have a

Im new using Tm1,the idea is going to delete date by date, isnt? but how can i do parameters? you told me that i had to create an pStartDate and pEndDate parameters. how can i declare them? i would like to delete from current date to current date - 2 months ().
Thanks in advance

Yep, you want ViewZeroOut. The usual way to do it is to create a view in the prolog using functions like ViewCreate, SubsetCreate, ViewSubsetAssign and then finally ViewZeroOut.

Below is an example of how I do it. It's designed to be fairly portable with a lot of use of variables.

Code: Select all

sCube='MyCube';
sObjName='sys_' | getprocessname();
if(viewexists(sCube, sObjName)=1); viewdestroy(sCube, sObjName); endif;
ViewCreate(sCube, sObjName);

sDim='Date';
sElem='2012-11-16';
if(subsetexists(sDim, sObjName)<>1); subsetcreate(sDim, sObjName); endif;
subsetdeleteallelements(sDim, sObjName);
subsetelementinsert(sDim, sObjName, sElem, 1);
ViewSubsetAssign(sCube, sObjName, sDim, sObjName);

ViewZeroOut(sCube, sObjName);
ViewDestroy(sCube, sObjName);

You'll need some more smarts around selecting the date. I would have pStartDate and pEndDate defined as parameters in the TI process and a "counter" attribute in the date dimension. I'd then cycle through the entire date dimension looking for elements that have a "counter" value between the counter value of pStartDate and pEndDate.

[Alan Kirk]

Hello Martin, thanks for your answer. I have a

Im new using Tm1,the idea is going to delete date by date, isnt? but how can i do parameters? you told me that i had to create an pStartDate and pEndDate parameters. how can i declare them?

I suggest that you go here, download the Analytic Server TurboIntegrator Guide, and have a read through it. The manual itself is rubbish since it assumes that you'll do everything through the Map tab and the appalling auto-generated code that it writes which is rarely good and never flexible, and the coverage of customised code is perfunctory at best. (Other than that it's not a bad manual but since "that" is the metaphorical 90% of the code that a TM1 Admin will write in the real world, I stand by my statement that the manual's overall value to those seeking a good understanding of how to write TI code is... "rubbish".)

However it will get you across a number of basic concepts that you need to understand before you can start writing your own custom code.

[ablancof]

Hi Alan , thanks! i'm going to read this just now. Exactly im a programmer but i dont know the syntax to work in TI. Really Thanks

[ablancof]

Hi people, i got, i used the code given by ryan. i had to put it in Metadata Tab so as working and append this line in the end SubsetDestroy(sDim, sObjName). I did a SQL statement which it return the days that i need. Well That's all. Thank you so much.

SQL
select * from dwh.dim_fecha
where
fe_fecha >= (
select (CURRENT DATE - (DAY(CURRENT DATE)-1)DAYS)- 1 MONTH from sysibm.sysdummy1)
and fe_fecha<=CURRENT DATE
order by fe_fecha

METADATA TAB

sCube='Cubo_de_Ventas';
sObjName='sys_' | getprocessname();
if(viewexists(sCube, sObjName)=1); viewdestroy(sCube, sObjName); endif;
ViewCreate(sCube, sObjName);

sDim='Fecha';

if(subsetexists(sDim, sObjName)<>1); subsetcreate(sDim, sObjName); endif;
subsetdeleteallelements(sDim, sObjName);


subsetelementinsert(sDim, sObjName, miFecha, 1);
ViewSubsetAssign(sCube, sObjName, sDim, sObjName);

ViewZeroOut(sCube, sObjName);

ViewDestroy(sCube, sObjName);

SubsetDestroy(sDim, sObjName);

[Ravi_NITJ]

I think i have a better, generalized solution for this kind of situation...

Create a TI process - "Date_data_delete", with below code in prolog tab:
1.Destroy the subset "tobedeleted"
2.do the calculation for getting startdate (enddate - 2 months) , enddate (will be given)
3. executeprocess('date_delete_data_2','pstartdate',PDate2,'penddate',Pdate1);

Now, In second TI - "Date_delete_data_2"
Data Source : Dimension subset ( Date_dim dimension)
Parameter : pstartdate and penddate
Prolog Tab:
1.check if subset "tobedeleted" exists or not, then create it.
2.get the serial no of . startdata and enddate as below:
v2 =DAYNO(penddate);
v3 =DAYNO(pstartdate);

Metadata Tab:
1.get serial no for dates in dimension
v1 =DAYNO(Date_data);

if((v1>v3) & (v1<v2));
subsetelementinsert('Date_dim','tobedeleted',Date_data,1);
asciioutput('C:\OTD\tesco.txt', Date_data);
endif;

Epilog Tab:
1.create a view using above subset for Date dimension, for that cube.
2. View zeroout

Hope it helps.s.

ablancof

Martin Ryan

Alan Kirk

-- Is this approach correct???