Coppy data within a cube

Post Reply
TM1_1
Posts: 4
Joined: Tue Aug 15, 2017 2:34 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2016

Coppy data within a cube

Post by TM1_1 »

Hi everyone
New in TM1, i would like to create a process to copy data between scenarios within the same cube
If the scenario is "Forecast", the TI will copy the last "Actual" Data & paste it for next coming month. For example if the "Actual"month is July, the process will copy/paste the same data of July in August, Sept...December
When i run the TI below, the error "the subset "vSubName" not fount in dimension "t_scenario". !!!!!!
here is my prolog & data tab

Code: Select all

###prolog###Prolog#####
### Constants ###
cProcess = GetProcessName();
cUser = TM1User();
cTimeStamp = TimSt( Now, '\Y\m\d-\hh\im\s' );
sRandomInt = NumberToString( INT( RAND( ) * 100000 ));
cDebugFile = GetProcessErrorFileDirectory | cProcess | '.' | cTimeStamp | '.' | sRandomInt ;
nErrors = 0;
nHeader = 1;
cSubset = '}' | cProcess;


### Initialise Debug ###
If( pDebug >= 1 );
  # Set debug file name
  sDebugFile = cDebugFile | 'Prolog.debug';

  # Log start time
  AsciiOutput( sDebugFile, 'Process Started: ' | TimSt( Now, '\d-\m-\Y @\h:\i:\s' ) );

  # Log parameters
  AsciiOutput( sDebugFile, 'Parameters: plannification : ' | pCyclePlannification );
EndIf;


### Get Planning cycle for SYS_Time 
IF ( pCyclePlannification @= 'Budget');
     cCycle = 'Budgeting';
ELSEIF ( pCyclePlannification @= 'Forecast');
     cCycle = 'Forecasting';
ELSE;
     ProcessQuit;
ENDIF;

cDestScenario = CellGetS ('SYS_Time', cCycle, 'Scenario' );

### Cube information ###

pCube = 'Exchange_Rate ';
vDimName1 = 'Currency'; 
vDimName2 = 't_Months';
vDimName3 = 't_Scenario';
vDimName4 = 'm_Index';

vTemp = '}VZO_' | cProcess | cTimeStamp | sRandomInt;
vViewName = vTemp;
vSubName = vTemp;


#View
ViewCreate(pCube, vViewName);


##Subsets
SubsetCreate ( vDimName1 , vSubName);
SubsetCreate ( vDimName3 , vSubName);
SubsetCreate ( vDimName4 , vSubName);

##Subsets Elements
SubsetElementInsert ( vDimName1 , vSubName ,'USD-CAD' , 1);
SubsetElementInsert ( vDimName1 , vSubName ,'USD-CNY' , 2);
SubsetElementInsert ( vDimName3 , vSubName , cDestScenario, 1);
SubsetElementInsert ( vDimName4 , vSubName ,'Period Average' , 1);



#Assignation 
ViewSubsetAssign( pCube, vViewName, vDimName1,   vSubName);
ViewSubsetAssign( pCube, vViewName, vDimName2,   'SYS_allmonth');
ViewSubsetAssign( pCube, vViewName, vDimName3, 'vSubName');
ViewSubsetAssign( pCube, vViewName, vDimName4,  'vSubName');


ViewZeroOut(pCube, vViewName);

##suppression de la vue et des subsets
ViewDestroy(pCube, vViewName);
SubsetDestroy(vDimName1, vSubName);
SubsetDestroy(vDimName3, vSubName);
SubsetDestroy(vDimName4, vSubName);


OldCubeLogChanges = CUBEGETLOGCHANGES(pCube);
CUBESETLOGCHANGES(pCube, 0);
[/td]

####Data####
[td]Data###
### Initialise Debug ###
If( pDebug >= 1 );
  # Set debug file name
  sDebugFile = cDebugFile | 'Data.debug';
  # Log start time
  IF (nHeader = 0);
      AsciiOutput( sDebugFile, 'Process Started: ' | TimSt( Now, '\d-\m-\Y @\h:\i:\s' ) );
      nHeader = 1;
   ENDIF;
EndIf;

IF (pDebug = 2);
    ItemSkip;
ENDIF;



cMyMonth=CellGetS('SYS_Time', 'Actuals', 'Month');
cPeriodAverage=CellGetN('Exchange_Rate',Currency,'cMyMonth',CellGetS('SYS_Time', 'Actuals', 'Scenario'),'Period Average');
iF( pCyclePlannification@='Forecast' & 't_Month'@>cMyMonth);

    
     CellIncrementN(cPeriodAverage, 'Exchange_Rate',Currency, t_months,'cDestScenario','Period Average');


ENDIF;
Thank you and sorry for my english
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Coppy data within a cube

Post by declanr »

Some of your viewsubsetassigns have quotes around the subset variable, remove them and it should work. With quotes it is trying to use the literal string instead of evaluating it to the subset name.
Declan Rodger
TM1_1
Posts: 4
Joined: Tue Aug 15, 2017 2:34 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2016

Re: Coppy data within a cube

Post by TM1_1 »

Thanks declanr for your response
It works BUT the result is not exactely what im looking for
I would like to copy all data from Actual scenario to Forecast scanario based on the actual month in the cube : SYS_Time
for example:
if the actual month in Sys_Time is April, the process will copy all data from "Actual" to "forecast" from January to April
AND from May to December will be the data from the last month in "Actual" scenario which is July
[td]
Actual:

Currency Jan Feb Ma april may june …. dec
USD-CAD 1 3 5 7 0 0 0
USD-CNY 2 4 6 8 0 0 0

Forecast:

Currency Jan Feb Ma april may june …. dec
USD-CAD 1 3 5 7 7 7 7
USD-CNY 2 4 6 8 8 8 8
[/td]
My problem is in the data tab, i don't figure out how i can use the statment "if" to make the difference between Actual & Forecast using the cube Sys_Time
Thanks
TM1_1
Posts: 4
Joined: Tue Aug 15, 2017 2:34 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2016

Re: Coppy data within a cube

Post by TM1_1 »

Sorry i made a mistake:

if the actual month in Sys_Time is April, the process will copy all data from "Actual" to "forecast" from January to April
AND from May to December will be the data from the last month in "Actual" scenario which is April not July
Thanks
Post Reply