Thanks Wim and Orlando. Please take a look into the below code to what i have written.
DataSource is ExpenseReporting cube.
Variables are
vScenario, vVersion, vYear, vProjects, vCurrency, vDivision, vBusinessUnits, vExpenseAccounts, vMonth, Value.
Parameters are
pScenario, pYear,pVersion, pDivision, pDelimiter.
In Prolog:
Code: Select all
#****Begin: Generated Statements***
#****End: Generated Statements****
cProcess = GetProcessName();
cTimeStamp = TimSt( Now, '\Y\m\d\h\i\s' );
cDebugFile = GetProcessErrorFileDirectory | cProcess | '.' | cTimeStamp | '.';
cViewName = '}' | cProcess | '.' | NumberToString( ROUND( RAND( ) *100000 ) );
cSubsetName = cViewName;
sDebugFile = cDebugFile | 'Prolog.log';
cCubeName = 'EMS_ExpenseReporting';
###---------------- Source View Creation-----------------------------------------####
If( ViewExists( cCubeName, cViewName ) = 1 );
ViewDestroy( cCubeName, cViewName );
EndIf;
ViewCreate( cCubeName, cViewName );
ViewExtractSkipCalcsSet( cCubeName, cViewName, 1 );
ViewExtractSkipZeroesSet( cCubeName, cViewName, 1 );
ViewExtractSkipRuleValuesSet( cCubeName, cViewName, 0 );
###____ BEGIN Scenario____###
vDimName = 'Scenario';
vSubsetExists = SubsetExists ( vDimName, cSubsetName );
If (vSubsetExists = 1);
SubsetDestroy(vDimName, cSubsetName );
EndIf;
SubsetCreate ( vDimName, cSubsetName);
SubsetElementInsert ( vDimName, cSubsetName, pScenario, 0);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
###____END Scenario____###
###____BEGIN Year____###
vDimName = 'Year';
vSubsetExists = SubsetExists ( vDimName, cSubsetName );
If (vSubsetExists = 1);
SubsetDestroy(vDimName, cSubsetName );
EndIf;
ExecuteProcess('Bedrock.Dim.Sub.Create.ByElement',
'pDimension', vDimName, 'pSubset', cSubsetName, 'pElements', pYear, 'pDelimiter',pDelimiter
);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
###____END Year____###
###____BEGIN Version____###
vDimName = 'Version';
vSubsetExists = SubsetExists ( vDimName, cSubsetName );
If (vSubsetExists = 1);
SubsetDestroy(vDimName, cSubsetName );
EndIf;
Executeprocess('Bedrock.Dim.Sub.Create.Consolidation.Leaf','pDimension',vDimName,'pSubSet',cSubsetName, 'pConsol',pVersion);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
###____END version____###
###-------------------------BEGIN All N level elements for all other dimensions--------------------- ###
###____BEGIN Projects___###
vDimName = 'Projects';
ExecuteProcess('Bedrock.Dim.Sub.Create.Leaf',
'pDimension', vDimName, 'pSubset', cSubsetName, 'pExclusions', ''
);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
###____END Projects____###
###____BEGIN Currency_____###
vDimName = 'Currency';
ExecuteProcess('Bedrock.Dim.Sub.Create.Leaf',
'pDimension', vDimName, 'pSubset', cSubsetName, 'pExclusions', ''
);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
###____END Currency____###
###___BEGIN Month____###
vDimName = 'Month';
ExecuteProcess('Bedrock.Dim.Sub.Create.Leaf',
'pDimension', vDimName, 'pSubset', cSubsetName, 'pExclusions', ''
);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
###____END Month____###
###____BEGIN ExpenseAccounts____###
vDimName = 'ExpenseAccounts';
ExecuteProcess('Bedrock.Dim.Sub.Create.Leaf',
'pDimension', vDimName, 'pSubset', cSubsetName, 'pExclusions', '808003&808004'
);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
###____END ExpenseAccounts____###
#------------BEGIN Division----------------
vDimName = 'Division';
If(pDivision @='All');
ExecuteProcess('Bedrock.Dim.Sub.Create.Leaf',
'pDimension', vDimName, 'pSubset', cSubsetName, 'pExclusions', ''
);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
Else;
Executeprocess('Bedrock.Dim.Sub.Create.Consolidation.Leaf','pDimension',vDimName,'pSubSet',cSubsetName, 'pConsol',pDivision);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
EndIf;
#----------END Division------------------
###--------------------- Completed View Creation----------------------######
Under
DATA tab:
Code: Select all
#****Begin: Generated Statements***
#****End: Generated Statements****
vYM=pYear | '-' | ATTRSL('Month','Jun','Month_No');
#vYM=vYear | '-' | DimensionATTRS('Month', 'Month_No');
CellPutn(Value,'EMS_ExpenseReporting_Forecast',pScenario,pVersion,pYear,vProjects,vCurrency,vDivision,vBusinessUnits,vExpenseAccounts,vMonth,vYM);
In
Data tab, if i use ATTRSL, it can only be used for one element of Month dimension at any given point of time. I was also trying to use DimensionATTRS but do not know if it will be right here.
One thing to note is the
Snapshot dimension which i have created is containing elements starting from 2018-01, 2018-02 ...till 2023-12 but the Source cube contains Year starting from 2014. This new TI process is being executed for a specific passed Year parameter.
Result:
Upon executing this TI process, although it is getting some data for 2018-06 (element of Snapshot dimension) in the target cube, the values are completely wrong.
It is even putting values in to the Target cube for other years as well but completely different values than the Source cube.
I do not know what to do now.
Please help!!