Page 1 of 1

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

Posted: Sat May 12, 2018 2:00 pm
by srp313
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.

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

Posted: Sat May 12, 2018 7:38 pm
by Wim Gielis
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.

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

Posted: Sat May 12, 2018 9:12 pm
by Wim Gielis
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.

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

Posted: Mon May 14, 2018 11:26 am
by srp313
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.

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

Posted: Tue May 15, 2018 1:08 pm
by srp313
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.

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

Posted: Wed May 16, 2018 4:40 am
by srp313
Hi friends,

I would greatly appreciate any and all pointers/suggestions.


Thanks,
srp

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

Posted: Wed May 16, 2018 10:05 am
by Wim Gielis
Hello,

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

Wim

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

Posted: Wed May 16, 2018 10:23 am
by srp313
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.

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

Posted: Thu May 17, 2018 10:05 am
by Edward Stuart
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.

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

Posted: Fri May 18, 2018 3:20 pm
by srp313
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
      );

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

Posted: Fri May 18, 2018 4:12 pm
by Edward Stuart
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

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

Posted: Fri May 18, 2018 7:38 pm
by lotsaram
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.

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

Posted: Tue May 22, 2018 10:12 am
by srp313
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.