Cube Copy 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 Copy TI Process

Post by srp313 »

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
tomok
MVP
Posts: 2831
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 »

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: 26
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 »

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
tomok
MVP
Posts: 2831
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 »

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: 26
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 »

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
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: Cube Copy TI Process

Post by macsir »

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: 26
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 »

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
tomok
MVP
Posts: 2831
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 »

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: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Cube Copy TI Process

Post by Wim Gielis »

Tom is right. I use code like this:

Code: Select all

sMDX = '{TM1FILTERBYLEVEL(DESCENDANTS([Month].[' | pMonth | ']), 0)}';
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
srp313
Posts: 26
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 »

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