Cube Copy TI Process

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

Cube Copy TI Process

Post by srp313 » Wed May 02, 2018 8:48 am

Hi friends,

I am still fairly new to TM1 and am requesting your earnest guidance in solving the below problem of mine.

I have a Cube upon which there is a TI Process defined for copying data within the cube for the whole year based on the Scenario, Version and Division selected by the user (from a source Scenario, Version , Division to a destination Scenario, Version & Division).
Thus, it is copying the data for the whole Year currently.

But right now, business requirement is copy it for a specific month. For example, Business wants to copy the CurrentBudget Scenario data for January month to Forecast scenario and January month - they should be able to do it by selecting a specific month.

Currently in the TI Process, in the PROLOG, it is creating subsets based on Scenario, Version and Year.

How should i approach in solving this problem? Being a beginner to TM1, it is really difficult to come up with an approach to solve it.

I request your help please.

Thanks so much,
srp
Best Regards,
srp

User avatar
tomok
MVP
Posts: 2483
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Cube Copy TI Process

Post by tomok » Wed May 02, 2018 10:11 am

Add a parameter two parameters to your process, pSourceMonth and pTargetMonth. Then create a subset in both the source and target views and add the new parameter elements to those subsets. This will narrow it down to just the single month.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

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

Re: Cube Copy TI Process

Post by srp313 » Wed May 02, 2018 11:36 am

Thanks so much @tomok for replying!

i. I have created 2 new parameters pSourceMonth and pTargetMonth.

ii. Under Prolog, since it is copying data within the same cube, i have added the below code (Code for Year subset was existing and i have added the Month Subset as mentioned below)
#------------BEGIN Year----------------
#vDimName = 'Year';
#vSubsetExists = SubsetExists ( vDimName, cSubsetName );
#If (vSubsetExists = 1);
# SubsetDestroy(vDimName, cSubsetName );
#EndIf;
#SubsetCreate ( vDimName, cSubsetName);
#SubsetElementInsert ( vDimName, cSubsetName, pFromYear, 0);
#ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
#----------END Year------------------

#------------BEGIN MONTH
vDimName = 'Month';
vSubsetExists = SubsetExists ( vDimName, cSubsetName );
If (vSubsetExists = 1);
SubsetDestroy(vDimName, cSubsetName );
EndIf;
SubsetCreate ( vDimName, cSubsetName);
SubsetElementInsert ( vDimName, cSubsetName, pSourceMonth, 0);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
#----------END MONTH

iii. under Data tab, i have added pTargetMonth:
#****Begin: Generated Statements***
#****End: Generated Statements****

#=====================================================
#Code to check If both source and target are same
#=====================================================

If(pFromScenario @= pToScenario & pFromVersion @= pToVersion & pFromYear @= pToYear);
ProcessQuit;
Endif;

#=====================================================

vDimName = 'Version';

IF((ELLEV(vDimName,pToVersion) > 0));
ToVersion=vVersion;
ELSE;
ToVersion=pToVersion;
ENDIF;

#====================================================================
#LOADING AND COPYING DATA FROM EXPENSE PLAN TO EXPENSE PLAN CUBE
#====================================================================

IF(CellIsUpdateable('ExpensePlan',pToScenario,ToVersion,pToYear,vCurrency,vDivision,vProjects,pTargetMonth, vExpenseAccounts)=1);

CellPutn(vValue,'ExpensePlan',pToScenario,ToVersion, pToYear,vCurrency,vDivision,vProjects,pTargetMonth,vExpenseAccounts);

EndIf;

But while executing the TI process for copying data from say CurrentBudget Scenario data, January month,2017 to Forecast scenario, January month,2018 - although it is copying some data but the data are not exactly same.

For example for month Jun:
Output values between Source and Destination Months are not matching

Output:
For JUNE
( source:CurrentBudget Scenario) , , , , , ,, (Target:Forecast Scenario ), , ,, , , ,
105,470,996.94 70,574,387.07 3,192,752.150 3,054,63733,028 105,0880
145410230.4 79256825.713571616.785 0 3415239.78537394.5118982.5 1.36666E-06
Where i am going wrong?
Last edited by srp313 on Wed May 02, 2018 11:49 am, edited 1 time in total.
Best Regards,
srp

User avatar
tomok
MVP
Posts: 2483
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Cube Copy TI Process

Post by tomok » Wed May 02, 2018 11:49 am

Why do you have all the code referring to the year commented out?
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

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

Re: Cube Copy TI Process

Post by srp313 » Wed May 02, 2018 12:04 pm

I am so sorry Tom for the mistake. In a hurry to implement it, i missed out uncommenting the Year Code.

Thanks much for pointing it out!

Now the values are being copied correctly for an individual month :)

- one more question to you please Tom?
Assuming a requirement is to copy data for a particular Source Scenario to Destination Scenario from month to month irrespective of any Year.
Does this mean only Scenario, Month subsets would be created in Prolog and Year subset has to be removed - will this work?
Best Regards,
srp

User avatar
macsir
Community Contributor
Posts: 548
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Cube Copy TI Process

Post by macsir » Wed May 02, 2018 9:12 pm

You don't need to remove year. As long as you get all level 0 elements from year dimension, it still can remain.
But for easier coding, I would like to remove it and make sure your extracting view is suppressing zeros and skip consolidations.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

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

Re: Cube Copy TI Process

Post by srp313 » Thu May 03, 2018 9:53 am

Thanks macsir for replying! I understood what you have mentioned - could you please let me know more on why we need to suppress zeroes and skip consolidations as you have mentioned.

On top of this, I have a new requirement where business has asked to modify this TI Process to implement the copying of data for individual months as well as YTD Months .

Month dimension has an YTD subset containing elements such as JAN-YTD (Jan), FEB-YTD (Jan, Feb),MAR-YTD (Jan, Feb, Mar) and so on till DEC-YTD(contains Jan to Dec).
They want the elements of this YTD subset to be passed by them as parameters in order to execute the TI process.

So essentially,
  • if user passes MAR-YTD, the ti process should copy data for Jan, Feb, Mar from Source to Target Destination Scenarios and Year.
  • if user passes JAN-YTD, the ti process should copy data for Jan-YTD i.e. Jan.
  • if user passes only a single month within any of the YTD like say , user passes only June, then the ti process should copy data only for JUN month.
Currently the need to the data to be copied for same month/s from Source to destination scenarios. So , i have removed the pTargetMonth parameter and am using only pSourceMonth parameter to create the subset as well as in the Data tab.

Right now Month subset is being created as mentioned below:
#------------BEGIN MONTH
vDimName = 'Month';
vSubsetExists = SubsetExists ( vDimName, cSubsetName );
If (vSubsetExists = 1);
SubsetDestroy(vDimName, cSubsetName );
EndIf;
SubsetCreate ( vDimName, cSubsetName);
SubsetElementInsert ( vDimName, cSubsetName, pSourceMonth, 0);
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
#----------END MONTH
This gets executed and copies the data correctly if i pass individual months like Jan, Feb , etc to it. But if i pass a YTD element from Month dimension like say JUN-YTD, the process executes successfully but no data is being copied for the months Jan to Jun.

I was thinking this should have worked but it is not.

Anywhere i am going wrong? Please let me know your suggestions.
Best Regards,
srp

User avatar
tomok
MVP
Posts: 2483
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Cube Copy TI Process

Post by tomok » Thu May 03, 2018 10:41 am

The issue is that when you decide to copy a YTD element you are creating a subset with the YTD element instead of the individual months underneath that YTD element. You can't do that because you can't write to a consolidated node, only the leaf elements below it. You need to modify your subset creation process to add only months, not the YTD element.

I would do this by creating the subset with an MDX expression, instead of a hard-coded subset. If you use code like this:

Code: Select all

sMDX = '{TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[Month].[' | pMonth | ']},ALL,RECURSIVE)}, 0)}';
This will result in only months, regardless of whether you pass a single month or YTD element. If you pass Mar-YTD, it will result in Jan, Feb and Mar and if you pass Mar it will result in Mar.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

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

Re: Cube Copy TI Process

Post by Wim Gielis » Thu May 03, 2018 2:08 pm

Tom is right. I use code like this:

Code: Select all

sMDX = '{TM1FILTERBYLEVEL(DESCENDANTS([Month].[' | pMonth | ']), 0)}';
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: 24
Joined: Mon Jul 10, 2017 10:02 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2016

Re: Cube Copy TI Process

Post by srp313 » Sat May 12, 2018 1:37 pm

Thank you so much Tom and Wim for your pointers. I am now trying to find out more ways to learn mdx.
Best Regards,
srp

Post Reply