Cube Transfer TI Process

Post Reply
srp313
Posts: 26
Joined: Mon Jul 10, 2017 10:02 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2016

Cube Transfer TI Process

Post by srp313 » Wed Jul 04, 2018 5:32 pm

Hi Members,

I request your guidance for my below problem:

I have to create a Cube transfer TI process which has to copy the data from Source cube: ExpRep to Destination cube: ExpRepForecast.

Now the problem is both the cubes contain same set of dimensions except
  • Source Cube contains Year and Month dimensions
  • Destination cube contains SNAPSHOT dimension ( a new dimension built in from of Year-Month, say 2018-01,2018-02 and so on - it does not contain Year, Month dimensions.
How do i approach writing a code for accomplishing this task ?

Again i have to mention here i am terribly struggling in TM1 being new to it and request your help.
Best Regards,
srp

Wim Gielis
MVP
Posts: 1829
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Cube Transfer TI Process

Post by Wim Gielis » Wed Jul 04, 2018 9:09 pm

The code to write into the destination cube, will be something like this in pseudo-code:

Code: Select all

vYM = vYear | '-' | vMonth;
CellIncrementN( vValue, vDestCube, '...', '...', '...', vYM, '...', '...', 'msr');
Assuming that your variables with data coming from the source cube, are called vYear and vMinth for year and month, respectively.
Also assuming that the names of the elements can be obtained by concatenation and inserting a '-' in the middle.
Stick to the correct order of the dimensions in the destination cube.

Other than that, this is a standaard data integration process with a zero out view, view source creation, and so on.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

srp313
Posts: 26
Joined: Mon Jul 10, 2017 10:02 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2016

Re: Cube Transfer TI Process

Post by srp313 » Thu Jul 05, 2018 6:56 am

Hi Wim, Thanks so much for replying.

I understood what the pseudocode is implying but the problem is Month dimension i.e. vMonth variable coming from source cube contains data in form of Jan, Feb, Mar and so on and not in form of 01, 02 , 03 ...

What to do in this case ?

(P.S. I have already created a new empty cube i.e Destination cube containing the Snapshot dimension:2018-01, 2018-02 and so on.)

2. Also , i have a fundamental question to ask - in a standard data integration process, so first we have to zero put the target view , then we have to create a temporary view based on the Source cube and then do all the operations on this data from the Source Cube in the DATA tab.

Is this understanding correct? Please let me know if i am missing an important point/understanding.
Best Regards,
srp

User avatar
orlando
Posts: 52
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: Cube Transfer TI Process

Post by orlando » Thu Jul 05, 2018 7:19 am

hi,
srp313 wrote:
Thu Jul 05, 2018 6:56 am
I understood what the pseudocode is implying but the problem is Month dimension i.e. vMonth variable coming from source cube contains data in form of Jan, Feb, Mar and so on and not in form of 01, 02 , 03 ...

What to do in this case ?
You could create an alias or attribute in your month dimension eg. "month_no"

Jan = 01
Feb = 02

Then vYM = vYear | '-' | ATTRSL('Month', 'Jan', 'month_no');
srp313 wrote:
Thu Jul 05, 2018 6:56 am

2. Also , i have a fundamental question to ask - in a standard data integration process, so first we have to zero put the target view , then we have to create a temporary view based on the Source cube and then do all the operations on this data from the Source Cube in the DATA tab
The zeroout works the same way. You could start the process with two parameter (year and month). The element for the snapshot dimension is createt the same way: vYM = vYear | '-' | ATTRSL('Month', 'Jan', 'month_no');

best regards,
orlando

srp313
Posts: 26
Joined: Mon Jul 10, 2017 10:02 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2016

Re: Cube Transfer TI Process

Post by srp313 » Thu Jul 05, 2018 7:43 am

Thanks much orlando for replying!You do not know how much i appreciate it.
You could create an alias or attribute in your month dimension eg. "month_no"
Jan = 01
Feb = 02
Then vYM = vYear | '-' | ATTRSL('Month', 'Jan', 'month_no');
I understood the above but here, i have a Snapshot dimension which is already created in the application.
  • In the Snapshot dimension, i have manuyally inserted all the elements starting from 2018-01, 2018-02 .... till 2021-12.
  • This Snapshot dimension has been used to create a new Destination cube ExpRepForecast along with other dimensions form the Source cube ExpRep. The new cube is empty now.
What i am understanding is we have to concatenate the Source view variables vYear and Month dimension elements and have to save it as a new variable vYM which will be used to write into the destination cube.

Is there a way we can use the Snapshot dimension elements itself rather than concatenating or am i thinking it wrong way?

Also, in Wim's reply, he has mentioned 'msr' in the CelLIncrement statement - does it denote anything special or it is jsut the last dimension of the Destinaton cube ?
Best Regards,
srp

Wim Gielis
MVP
Posts: 1829
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Cube Transfer TI Process

Post by Wim Gielis » Thu Jul 05, 2018 8:22 am

Msr is just the indication of the measures dimension, indeed the last dimension in any cube.

You need to concatenate information (year and month) to indicate TM1 on which element in the YearMonth dimension to write the data.
You need to make sure that it maps correctly. For example, use an attribute as was suggested above.
Without a good mapping you will never be able to transfer the data from A to B.
If you were to do it manually and copy/paste values yourself, you would also look for the correct YearMonth based on Year and Month, no ?

Yes you need 1 views, a zero out view and source view. We normally do this in 1 process. (Prolog tab)
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
orlando
Posts: 52
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: Cube Transfer TI Process

Post by orlando » Thu Jul 05, 2018 8:35 am

srp313 wrote:
Thu Jul 05, 2018 7:43 am

What i am understanding is we have to concatenate the Source view variables vYear and Month dimension elements and have to save it as a new variable vYM which will be used to write into the destination cube.

Is there a way we can use the Snapshot dimension elements itself rather than concatenating or am i thinking it wrong way?

You can't use your Snapshot dimension in the source view, because your source cube does not have this dimension. You have to use the month and year dimension and then concatenate the both dimensions in a variable to use this in the CellPutN formula.

srp313
Posts: 26
Joined: Mon Jul 10, 2017 10:02 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2016

Re: Cube Transfer TI Process

Post by srp313 » Thu Jul 05, 2018 3:13 pm

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!!
Best Regards,
srp

Wim Gielis
MVP
Posts: 1829
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Cube Transfer TI Process

Post by Wim Gielis » Thu Jul 05, 2018 4:39 pm

Hello

- I do not see the zero out code
- As you use Bedrock, it's impossible to see what actually enters your source view, and what not. In fact, the code is not easy to read.

For your information, this:

Code: Select all

vSubsetExists = SubsetExists ( vDimName, cSubsetName );
If (vSubsetExists = 1);
      SubsetDestroy(vDimName, cSubsetName );
EndIf;
can be reduced to:

Code: Select all

SubsetDestroy(vDimName, cSubsetName );
- use the AsciiOutput function with the correct variable names in the Data tab to understand what values you get in the Data tab. Which months, versions, and so on. Also, use AsciiOutput to understand WHERE you are going to write to. In fact, do I see this correctly that you always want to write to June ? Jun is hardcoded in the data tab. Seems not in correspondence with your source view.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

srp313
Posts: 26
Joined: Mon Jul 10, 2017 10:02 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2016

Re: Cube Transfer TI Process

Post by srp313 » Thu Jul 05, 2018 5:57 pm

Hello Wim,

1. Yes, I am trying to write a ZeroOut code and most of the Prolog has been taken from exsiting TI process. Infact almost all the exsting TI processes are using Bedrock processes and i am trying to use the same since i do not have a deep understanding now being new.

2. Thanks for pointing this out. I got it.

Code: Select all

SubsetDestroy(vDimName, cSubsetName );
3. Using ASCIIOUTPUT, i will try to create a file and see what is being sent to Data tab. I could not understand the below :
Wim Gielis wrote:
Thu Jul 05, 2018 4:39 pm
Also, use AsciiOutput to understand WHERE you are going to write to.
I think you are referring to 'Jun'.

Yes, actually i could not find a way in which the below command ATTRSL can be used to extract all values for a particular year , say from 2018-01 till 2018-12. It is because at any point of time, it can be used only for one Month, right?

Code: Select all

vYM=pYear | '-' | ATTRSL('Month','Jun','Month_No');
and after this, there is the CellPutN statement:

Code: Select all

CellPutn(Value,'EMS_ExpenseReporting_Forecast',pScenario,pVersion,pYear,vProjects,vCurrency,vDivision,vBusinessUnits,vExpenseAccounts,vMonth,vYM);
I had hardcoded Jun becasue i was trying to see is the Ti process working for a particular month , here june, upon execution. But this is not working since values transferred for Jun for year 2018(passed parameter) are completely different compared with the Source cube values. ( I do not know where this is going wrong, it should have worked right ?)

How do i implement this for all months of a year? So i have to individually write ATTRSL statements for each month or is this approach incorrect?

I am sorry if these questions sound basic but i am trying to work on it.
Best Regards,
srp

Wim Gielis
MVP
Posts: 1829
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Cube Transfer TI Process

Post by Wim Gielis » Thu Jul 05, 2018 7:01 pm

Do you also test and experiment yourself ? You seem to only ask return questions without trying yourself.

If you were to use an AsciiOutput statement with the contents of the CellPutN:

Code: Select all

'EMS_ExpenseReporting_Forecast',pScenario,pVersion,pYear,vProjects,vCurrency,vDivision,vBusinessUnits,vExpenseAccounts,vMonth,vYM);
you will get a text file with the cells in the cube that you will write to.
If you want the values that are written, add your Value (converted to a string) to the AsciiOutput.

Howcome you have a CellPutN with 'vMonth,vYM' at the end ? Do you understand what the dimensionality of your cubes is ?

Code: Select all

vYM=pYear | '-' | ATTRSL('Month','Jun','Month_No');
... why not replacing 'Jun' with vMonth ? Now you are loading data for several months onto the same June month (if your code works, which I doubt).
Please step away from the process and draw the relationships between the cubes on a sheet of paper. Write down which manipulations need to be done, what is the source, what is the target, and so on.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Post Reply