I recently needed to dynamically load the last 2 months worth of data into TM1.
If anyone was interested I could go into detail of the sql but within TI I had to make sure I deleted the current month and the previous month. The best way to do this was to define to seperate areas of the cube to zero out, the code is shown below. This code is based on a fiscal year of Jul to Jun and will need adjusting to your own fiscal year.
#***********************************************
# Zero out first month
#***********************************************
pYear1 = CAPIT(LOWER(TIMST(Now, '\Y')));
pMonthNo = StringToNumber(TIMST(Now, '\m'));
IF(pMonthNo > 6);
pYear1 = NumberToString(StringToNumber(pYear1) + 1);
ENDIF;
pMonth1 = TIMST(Now, '\M');
vCube = 'Revenue';
vDimName1 = 'Scenario';
vDimName2 = 'Fiscal_Year';
vDimName3 = 'Period';
vTemp = 'SystemUseOnly_Load' | STR(RAND, 10, 8);
vViewName = vTemp;
vSubName = vTemp;
SubsetCreate(vDimName1, vSubName);
SubsetCreate(vDimName2, vSubName);
SubsetCreate(vDimName3, vSubName);
SubsetElementInsert(vDimName1, vSubName, 'ACTUAL', 1);
SubsetElementInsert(vDimName2, vSubName, pYear1, 1);
SubsetElementInsert(vDimName3, vSubName, pMonth1, 1);
ViewCreate(vCube, vViewName);
ViewSubsetAssign(vCube, vViewName, vDimName1, vSubName);
ViewSubsetAssign(vCube, vViewName, vDimName2, vSubName);
ViewSubsetAssign(vCube, vViewName, vDimName3, vSubName);
ViewZeroOut(vCube, vViewName);
ViewDestroy(vCube, vViewName);
SubsetDestroy(vDimName1, vSubName);
SubsetDestroy(vDimName2, vSubName);
SubsetDestroy(vDimName3, vSubName);
#***********************************************
# Zero out second month
#***********************************************
IF(CAPIT(pMonth1) @= 'JUL');
pYear2 = DIMNM('Fiscal_Year', DIMIX('Fiscal_Year', pYear1) - 1);
pMonth2 = 'JUN';
ELSE;
pYear2 = pYear1;
pMonth2 = DIMNM('Period', DIMIX('Period', pMonth1) - 1);
ENDIF;
vCube = 'Revenue';
vDimName1 = 'Scenario';
vDimName2 = 'Fiscal_Year';
vDimName3 = 'Period';
vTemp = 'SystemUseOnly_Load' | STR(RAND, 10, 8);
vViewName = vTemp;
vSubName = vTemp;
SubsetCreate(vDimName1, vSubName);
SubsetCreate(vDimName2, vSubName);
SubsetCreate(vDimName3, vSubName);
SubsetElementInsert(vDimName1, vSubName, 'ACTUAL', 1);
SubsetElementInsert(vDimName2, vSubName, pYear2, 1);
SubsetElementInsert(vDimName3, vSubName, pMonth2, 1);
ViewCreate(vCube, vViewName);
ViewSubsetAssign(vCube, vViewName, vDimName1, vSubName);
ViewSubsetAssign(vCube, vViewName, vDimName2, vSubName);
ViewSubsetAssign(vCube, vViewName, vDimName3, vSubName);
ViewZeroOut(vCube, vViewName);
ViewDestroy(vCube, vViewName);
SubsetDestroy(vDimName1, vSubName);
SubsetDestroy(vDimName2, vSubName);
SubsetDestroy(vDimName3, vSubName);
