Transfer data within cubes for a particular month and future set of months in a year

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

Transfer data within cubes for a particular month and future set of months in a year

Post by srp313 » Sat May 12, 2018 2:00 pm

Hi all,
I have 2 cubes where business wants to transfer the data from 1st cube(Salarycube) to 2nd cube(ExpensePlan cube) for a particular Month(user passed parameter) and its further future months in a particular year(user passed parameter).

Application has an existing TI process pCTSalaryToExpensePlan where in its prolog, Month subset is being created:

Code: Select all

vDimName = 'Month';
      ExecuteProcess('Bedrock.Dim.Sub.Create.Leaf',
        'pDimension', vDimName,    'pSubset', cSubsetName,   'pExclusions', ''
      );
ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );
Further in the Prolog, it is also creating for its other dimensions Scenario, Year, Version, Division .
At last, it is doing a zeroOut of the target:

Code: Select all

#================
#ZeroOut the Target
If(pDivision @= 'All');
ExecuteProcess (
  'Bedrock.Cube.Data.ZeroOut',
  'pCube', cTargetCubeName,
  'pDelimDim', ';',
  'pDelimElem', '|',
'pFilter', 'Scenario|' | pScenario | ';Year|' | pYear| ';Version|' | pVersion| ';ExpenseAccounts|600310|600300|604100|600320|610120|610170|610300|610310|610320|610200|610240|610280',
  'pDebug', 0 );

Else;
ExecuteProcess (
  'Bedrock.Cube.Data.ZeroOut',
  'pCube', cTargetCubeName,
  'pDelimDim', ';',
  'pDelimElem', '|',
 'pFilter', 'Scenario|' | pScenario | ';Year|' | pYear| ';Version|' | pVersion| ';Division|' | pDivision | ;ExpenseAccounts|600310|600300|604100|600320|610120|610170|610300|610310|610320|610200|610240|610280',
  'pDebug', 0 );
EndIf
In the Data tab, it is writing the data into the intersection:

Code: Select all

CellPutn(vValue,'ExpensePlan',vScenario,vHRVersion,vYear,vCurrency,vDivision,vProject,vMonth,vHRAccounts);
My question is of 2 parts and i request your valuable suggestions and help with this as i fairly new to TM1:
1. Is carrying out a zeroOut of the target necessary? If we directly write into a new intersection value, will it not overwrite with each execution of the ti process?

2. How do i approach to implement the Month and Future months data transfer functionality- where business wants to transfer the data from 1st cube(Salarycube) to 2nd cube(ExpensePlan cube) for a particular Month(user passed parameter) and its further future months in a particular year(user passed parameter).?
Do i have to create a new subset for dimension Month where it has to store passed Month and its future months - how should i go forward?

A humble request for your help to a newbie. Please let me know your pointers and suggestions.
Best Regards,
srp

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

Re: Transfer data within cubes for a particular month and future set of months in a year

Post by Wim Gielis » Sat May 12, 2018 7:38 pm

1. Definitely. Since you don’t know upfront where the data will be in the source cube and data can change from one intersection to another, a zero out is needed.
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

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

Re: Transfer data within cubes for a particular month and future set of months in a year

Post by Wim Gielis » Sat May 12, 2018 9:12 pm

2. The zero out view and source view will both need a subset containing the correct month(s), like you described. You can choose to create 12 subsets manually and thus reduce the coding. If you do it wisely, you could deduct the subset name from the pMonth parameter that the user fills in.

If you prefer code, like I would, you can loop over the dimension elements. For each element, test if it’s level 0 and test if it’s equal to pMonth or ‘coming after’ pMonth. I would generally use an attribute (text, numeric, alias) to be able to compare elements. For example, 05 @>= 04 where 04 is pMonth and 05 is an element name or attribute of a month within the loop over months.

Alternatively, just like you would create YTD consolidations in the Month dimension, create YTG consolidations. YTD is year to date, YTG is year to go. Then creating and filling the subset becomes easy as you just need to grab the children of the correct YTG element.
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: Transfer data within cubes for a particular month and future set of months in a year

Post by srp313 » Mon May 14, 2018 11:26 am

Thanks Wim for the valuable suggestions. I am trying to work on them although i am finding it very difficult to proceed with the code.
Best Regards,
srp

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

Re: Transfer data within cubes for a particular month and future set of months in a year

Post by srp313 » Tue May 15, 2018 1:08 pm

Hi Wim and other members,

I have an update after trying working on this and i request your guidance. Please see below the details:

I have modified the exsiting TI process pCTSalaryToExpensePlan Prolog and Data tab as mentioned below:
  • Prolog Tab Code Change

Code: Select all

#vDimName = 'Month';
#     ExecuteProcess('Bedrock.Dim.Sub.Create.Leaf',
#      'pDimension', vDimName,    'pSubset', cSubsetName,   'pExclusions', ''
#  );
#ViewSubsetAssign (cCubeName, cViewName, vDimName, cSubsetName );


#========Create Future Month Dimension Subset========

#--------Include Future Months from pMonth--------------------------------

vInclude='';
 
z = DIMSIZ('Month');

While(z>0);
vMonth= DIMNM('Month', z);
 
x=ELLEV('Month',vMonth);

IF(x=0);
IF(ATTRN('Month', vMonth, 'Month Number') >= ATTRN('Month', pToMonth, 'Month Number'));
vInclude=vInclude|vMonth|'&';
ENDIF;
ENDIF;
 
z = z - 1;
END;

vIncluded=SUBST(vInclude, 1,(Long(vInclude)-1));

vDimName = 'Month';

      ExecuteProcess('Bedrock.Dim.Sub.Create.ByElement',
        'pDimension', vDimName, 'pSubset', cSubsetName, 'pElements', vIncluded
      );


#=====================================================
Under Prolog, since it using the Bedrock process Bedrock.Cube.Data.ZeroOut, i have tried to modify the same process as shown below:

Code: Select all

cTargetCubeName = 'ExpensePlan';

If(pDivision @= 'All');

ExecuteProcess (
  'Bedrock.Cube.Data.ZeroOut',
  'pCube', cTargetCubeName,
  'pDelimDim', ';',
  'pDelimElem', '|',
  'pFilter', 'Scenario|' | pScenario | 
               ';Year|' | pYear| 
               ';Version|' | pVersion| 
               ';Month|' | vIncluded|
               ';ExpenseAccounts|600310|600300|604100|600320|610120|610170|610300|610310|610320|610200|610240|610280',
   'pDebug', 0 );

# Current Forecast Adjustment Changes as part of Known error KE10002573 End

Else;

ExecuteProcess (
  'Bedrock.Cube.Data.ZeroOut',
  'pCube', cTargetCubeName,
  'pDelimDim', ';',
  'pDelimElem', '|',
   'pFilter', 'Scenario|' | pScenario | 
                ';Year|' | pYear|  
                ';Version|' | pVersion| 
                 ';Division|' | pDivision | 
                 ';Month|' | vIncluded|
                 ';ExpenseAccounts|600310|600300|604100|600320|610120|610170|610300|610310|610320|610200|610240|610280',
  'pDebug', 0 );
  • Data Tab Code Change

Code: Select all

iIndex = 1;
WHILE(iIndex <= SubsetGetSize(vDimName,cSubsetName));

vMonth=SubsetGetElementName(vDimName,cSubsetName,iIndex);

CellPutn(vValue,'ExpensePlan',vScenario,vHRVersion,vYear,vCurrency,vDivision,vProject,vMonth,vHRAccounts);

iIndex = iIndex+1;

end;
Parameters passed to run the process:

Code: Select all

pScenario,
pYear,
pDivision,
pDelimiter,
pVersion,
pToMonth

OUTOUT:

1. The modified TI process is completing execution with below minor error in the called TI Process Bedrock.Cube.Data.ZeroOut:

Code: Select all

Error: Prolog procedure line (148): Dimension: Month created an empty subset
I have executed and tested this for various combinations of parameters which are being passed (Scenario, Year,Version, pToMonth) but i am getting the same error each time.
I am not able to ascertain what is causing this error.

2. The expected output is
corresponding data for a particular months and all its future months must be transferred from Salary cube(Source cube) to ExpensePlan cube (Target cube)
Currently actual output is: For example, If i am executing the ti process passing parameters

Code: Select all

Forecast scenario, Year 2018, Division 1001, Version 'Working', pToMonth: Mar
, it should copy each intersection data for the respective months from the corresponding Source cube intersections to Target cube intersections,

But after execution with minor errors, it is actually copying the data for March month from the source cube intersection into the March intersection of the target cube and also copying the same March month data into all other months' intersection in the Target cube ExpensePlan.

I have tried much to find out how to resolve this and where i am going wrong. I request your help please.
Best Regards,
srp

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

Re: Transfer data within cubes for a particular month and future set of months in a year

Post by srp313 » Wed May 16, 2018 4:40 am

Hi friends,

I would greatly appreciate any and all pointers/suggestions.


Thanks,
srp
Best Regards,
srp

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

Re: Transfer data within cubes for a particular month and future set of months in a year

Post by Wim Gielis » Wed May 16, 2018 10:05 am

Hello,

Posting an update after only 15 hours can be too optimistic. We are all volunteers helping other lab when there is time.

Wim
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: Transfer data within cubes for a particular month and future set of months in a year

Post by srp313 » Wed May 16, 2018 10:23 am

Hi Wim,

I very well understand that and I am very sorry if my post was taken wrongly. I cannot be thankful enough to this forum members who are much helpful with their time and knowledge to other members.
Best Regards,
srp

Edward Stuart
Community Contributor
Posts: 222
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: Transfer data within cubes for a particular month and future set of months in a year

Post by Edward Stuart » Thu May 17, 2018 10:05 am

Error: Prolog procedure line (148): Dimension: Month created an empty subset
- What code is there on line 148 of the Prolog
- What do you think "Dimension: Month created an empty subset" means?

Have a look at the Source View created (if it is not deleted as part of the process, which is highly likely) and see if you can identify the issue.

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

Re: Transfer data within cubes for a particular month and future set of months in a year

Post by srp313 » Fri May 18, 2018 3:20 pm

Thanks for replying Edward!

1. In line 148 of the process Bedrock.Cube.Data.ZeroOut which is erroring out, following code(commented line #148) is present:

Code: Select all

sDimension = Trim( SubSt( sArgument, 1, nDelimElemIndex - 1 ) );
  sElements = Trim( SubSt( sArgument, nDelimElemIndex + 1, Long( sArgument ) ) );
  # Check that dimension exists in the cube
  If( DimensionExists( sDimension ) = 1 );
    # Step 3: Create subset and assign to view
    If( pDebug <= 1 );
      ExecuteProcess( 'Bedrock.Dim.Sub.Create.ByElement',
        'pDimension', sDimension,        'pSubset', cSubset,        'pElements', sElements,        'pDelimiter', pDelimElem,        'pDebug', pDebug      );
      If( SubsetGetSize( sDimension, cSubset ) > 0 );
        ViewSubsetAssign(pCube, cView, sDimension, cSubset);
      Else;
        # Empty subset created. Cancel process
        nErrors = 1;
        sMessage = 'Dimension: ' | sDimension | ' created an empty subset';
        If( pDebug >= 1 );
          AsciiOutput( sDebugFile, sMessage );
        EndIf;

#Line 148
[b]ItemReject( sMessage );[/b]

      EndIf;
    EndIf;
  Else;
    # The dimension does not exist in the model. Cancel process
    nErrors = 1;
    sMessage = 'Dimension: ' | sDimension | ' does not exist';
    If( pDebug >= 1 );
      AsciiOutput( sDebugFile, sMessage );
    EndIf;
    ItemReject( sMessage );
  EndIf;
End;
-So with the else condition being tue, i.e Month subset size<= 0, it is throwing out this error.

2.I understand it means that in the Prolog of the process, Month dimension is creating an empty subset but there is the below code written for Future Months ('Month' subset) where by the process Bedrock.Dim.Sub.Create.ByElement, it should have created one subset actually :
Am i missing something here?

I tried searching for the Source view but its seems it is getting deleted as you had pointed out.

Code: Select all

#========Create Future Month Dimension Subset========
#--------Include Future Months from pMonth--------------------------------

vInclude='';

z = DIMSIZ('Month');

While(z>0);
vMonth= DIMNM('Month', z);
 
x=ELLEV('Month',vMonth);

IF(x=0);
IF(ATTRN('Month', vMonth, 'Month Number') >= ATTRN('Month', pToMonth, 'Month Number'));
vInclude=vInclude|vMonth|'&';
ENDIF;
ENDIF;
 
z = z - 1;
END;

vIncluded=SUBST(vInclude, 1,(Long(vInclude)-1));

vDimName = 'Month';

      ExecuteProcess('Bedrock.Dim.Sub.Create.ByElement',
        'pDimension', vDimName, 'pSubset', cSubsetName, 'pElements', vIncluded
      );
Best Regards,
srp

Edward Stuart
Community Contributor
Posts: 222
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: Transfer data within cubes for a particular month and future set of months in a year

Post by Edward Stuart » Fri May 18, 2018 4:12 pm

Given that your subset is empty it is safe to say something is not working as you expect it too.

I would copy out the subset creation code into a temporary process and then start debugging to ascertain exactly what is not working. Either by creating a testing subset or via AsciiOutput.

As for finding the issue, the loops look ok but testing would confirm this. My guess is that your Month Number attribute is not operating as you expect it too. Either through the pMonth input or the attributes themselves

lotsaram
MVP
Posts: 3140
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Transfer data within cubes for a particular month and future set of months in a year

Post by lotsaram » Fri May 18, 2018 7:38 pm

Why would you be using Bedrock.Cube.Data.ZeroOut ?
As far as I know that process hasn't been in the bedrock library for many many years and is superseded by Bedrock.Cube.Data.Clear which you can get here.

But still the principle holds true if you feed a bedrock process bad parameters (like a non-existing dimension name) then it will intentionally abort with an error message. Feed it correct parameters and it will work.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

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

Re: Transfer data within cubes for a particular month and future set of months in a year

Post by srp313 » Tue May 22, 2018 10:12 am

Thanks Edward and lotsaram for replying.

Sorry i was a bit unwell and was unable to see your updates. I will work on what you folks have suggested and will get back with the results.

@lotsaram, actually there are some very old Ti processes present in the application which were and are still using Bedrock.Cube.Data.ZeroOut in them and are working perfectly.
Although i did come to know now that they have been taken out of the Bedrock library a bit late( one of the cons of a being a newbie in TM1) , i was trying to work with it.
But now i will try using the new process Bedrock.Cube.Data.Clear as you have kindly suggested.
Best Regards,
srp

Post Reply