TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

copying data from one version to another.

https://www.tm1forum.com/viewtopic.php?t=5137

Page 1 of 1

copying data from one version to another.

Posted: Thu Jun 02, 2011 6:35 am
by ryan
Hi I am copying data from one version to another version with in a cube.

Have to copy of Actuals of this year to Budget of next year (Let set the actuals of this month this year should flow to budget of the same month next year, eg:Actual of May 2011 should populate in Budget of May2012. This should be done from Jan to Dec)

I have already created a TI process but unable to copy the values.

Code:

Code: Select all

Prolog
========================================================

#****Begin: Generated Statements***
#****End: Generated Statements****

# ********************** Source Dimension Values **************************************#

SrcVersion = 'Actual';
SrcInterlineFlag = 'All';
SrcBorneFlag = 'All';
SrcDirection = 'All';
SrcAccount='All';
SrcService='All';
SrcVoyage='All';
SrcOriginRegion='All';
SrcDestinationREgion='All';
SrcPeriod='2011';
SrcCotainerType='All';
SrcContainerSize='All';
SrcMeasures='All';

SrcCube = 'SF_Forecast';

# ********************** Target Dimension Values **************************************#

TgtVersion = 'Budget';
TgtInterlineFlag = '';
TgtBorneFlag = '';
TgtDirection = '';
TgtAccount='';
TgtService='';
TgtVoyage='';
TgtOriginRegion='';
TgtDestinationREgion='';
TgtPeriod='2012';
TgtCotainerType='';
TgtContainerSize='';
TgtMeasures='';

TgtCube = '';

# Declare View name
sView = 'Copy';

# Declare Dimensions
sDim1 = 'Version';
sDim2 = 'SF_InterlineFlag';
sDim3 = 'SF_BorneFlag';
sDim4 = 'SF_Direction';
sDim5 = 'SF_Account';
sDim6 = 'SF_Service';
sDim7 = 'SF_Voyage';
sDim8 = 'SF_OriginRegion';
sDim9 = 'SF_DestinationRegion';
sDim10 = 'Period';
sDim11 = 'SF_ContainerType';
sDim12 = 'SF_ContainerSize';
sDim13 = 'SF_Measures';


# Declare Dimension Subsets Name
sSub1 = 'Copy - '| SrcVersion |'';
sSub2 = 'Copy - '| SrcInterlineFlag |'';
sSub3 = 'Copy - '| SrcBorneFlag |'';
sSub4 = 'Copy - '| SrcDirection |'';
sSub5 = 'Copy - '| SrcAccount |'';
sSub6 = 'Copy - '| SrcService |'';
sSub7 = 'Copy - '| SrcVoyage |'';
sSub8 = 'Copy - '| SrcOriginRegion |'';
sSub9 = 'Copy - '| SrcDestinationREgion |'';
sSub10 = 'Copy - '| SrcPeriod |'';
sSub11 = 'Copy - '| SrcCotainerType |'';
sSub12 = 'Copy - '| SrcContainerSize |'';
sSub13 = 'Copy - '| SrcMeasures |'';

# Declare Subsets Properties
# Only used if user choses something other than All in the parameters
sSubT1 = '['| sDim1 |'].['| SrcVersion |']';
sSubT2 = '['| sDim2 |'].['| SrcInterlineFlag |']';
sSubT3 = '['| sDim3 |'].['| SrcBorneFlag |']';
sSubT4 = '['| sDim4 |'].['| SrcDirection |']';
sSubT5 = '['| sDim5 |'].['| SrcAccount |']';
sSubT6 = '['| sDim6 |'].['| SrcService |']';
sSubT7 = '['| sDim7 |'].['| SrcVoyage |']';
sSubT8 = '['| sDim8 |'].['| SrcOriginRegion |']';
sSubT9 = '['| sDim9 |'].['| SrcDestinationREgion |']';
sSubT10 = '['| sDim10 |'].['| SrcPeriod |']';
sSubT11 = '['| sDim11 |'].['| SrcCotainerType |']';
sSubT12 = '['| sDim12 |'].['| SrcContainerSize |']';
sSubT13 = '['| sDim13 |'].['| SrcMeasures |']';



# Destroy previous view (if exists)
if (ViewExists(SrcCube, sView) = 1);
ViewDestroy(SrcCube, sView);
endif;

#Error Check
# Destroy existing subsets
if (SubsetExists(sDim1,sSub1) = 1);
SubsetDestroy(sDim1, sSub1);
endif;
if (SubsetExists(sDim2,sSub2) = 1);
SubsetDestroy(sDim2, sSub2);
endif;
if (SubsetExists(sDim3,sSub3) = 1);
SubsetDestroy(sDim3, sSub3);
endif;
if (SubsetExists(sDim4,sSub4) = 1);
SubsetDestroy(sDim4, sSub4);
endif;
if (SubsetExists(sDim5,sSub5) = 1);
SubsetDestroy(sDim5, sSub5);
endif;
if (SubsetExists(sDim6,sSub6) = 1);
SubsetDestroy(sDim6, sSub6);
endif;
if (SubsetExists(sDim7,sSub7) = 1);
SubsetDestroy(sDim7, sSub7);
endif;
if (SubsetExists(sDim8,sSub8) = 1);
SubsetDestroy(sDim8, sSub8);
endif;
if (SubsetExists(sDim9,sSub9) = 1);
SubsetDestroy(sDim9, sSub9);
endif;
if (SubsetExists(sDim10,sSub10) = 1);
SubsetDestroy(sDim10, sSub10);
endif;
if (SubsetExists(sDim11,sSub11) = 1);
SubsetDestroy(sDim11, sSub11);
endif;
if (SubsetExists(sDim12,sSub12) = 1);
SubsetDestroy(sDim12, sSub12);
endif;
if (SubsetExists(sDim13,sSub13) = 1);
SubsetDestroy(sDim13, sSub13);
endif;

# Create subsets
if (SrcVersion@='All');
SubsetCreatebyMDX(sSub1, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim1|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub1, '{TM1DRILLDOWNMEMBER( {'|sSubT1|'},ALL,RECURSIVE )}');
endif;

if (SrcInterlineFlag@='All');
SubsetCreatebyMDX(sSub2, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim2|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub2, '{TM1DRILLDOWNMEMBER( {'|sSubT2|'},ALL,RECURSIVE )}');
endif;

if (SrcBorneFlag@='All');
SubsetCreatebyMDX(sSub3, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim3|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub3, '{TM1DRILLDOWNMEMBER( {'|sSubT3|'},ALL,RECURSIVE )}');
endif;

if (SrcDirection@='All');
SubsetCreatebyMDX(sSub4, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim4|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub4, '{TM1DRILLDOWNMEMBER( {'|sSubT4|'},ALL,RECURSIVE )}');
endif;

if (SrcAccount@='All');
SubsetCreatebyMDX(sSub5, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim5|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub5, '{TM1DRILLDOWNMEMBER( {'|sSubT5|'},ALL,RECURSIVE )}');
endif;


if (SrcService@='All');
SubsetCreatebyMDX(sSub6, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim6|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub6, '{TM1DRILLDOWNMEMBER( {'|sSubT6|'},ALL,RECURSIVE )}');
endif;

if (SrcVoyage@='All');
SubsetCreatebyMDX(sSub7, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim7|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub7, '{TM1DRILLDOWNMEMBER( {'|sSubT7|'},ALL,RECURSIVE )}');
endif;

if (SrcOriginRegion@='All');
SubsetCreatebyMDX(sSub8, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim8|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub8, '{TM1DRILLDOWNMEMBER( {'|sSubT8|'},ALL,RECURSIVE )}');
endif;

if (SrcDestinationRegion@='All');
SubsetCreatebyMDX(sSub9, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim9|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub9, '{TM1DRILLDOWNMEMBER( {'|sSubT9|'},ALL,RECURSIVE )}');
endif;

if (SrcPeriod@='All');
SubsetCreatebyMDX(sSub10, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim10|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub10, '{TM1DRILLDOWNMEMBER( {'|sSubT10|'},ALL,RECURSIVE )}');
endif;

if (SrcCotainerType@='All');
SubsetCreatebyMDX(sSub11, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim11|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub11, '{TM1DRILLDOWNMEMBER( {'|sSubT11|'},ALL,RECURSIVE )}');
endif;

if (SrcContainerSize@='All');
SubsetCreatebyMDX(sSub12, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim12|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub12, '{TM1DRILLDOWNMEMBER( {'|sSubT12|'},ALL,RECURSIVE )}');
endif;

if (SrcMeasures@='All');
SubsetCreatebyMDX(sSub13, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim13|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub13, '{TM1DRILLDOWNMEMBER( {'|sSubT13|'},ALL,RECURSIVE )}');
endif;

# Create View
ViewCreate(SrcCube, sView);

# Assign new view as datasource
DatasourceCubeview=sView;

# Assign Subsets
ViewSubsetAssign(SrcCube, sView, sDim1, sSub1);
ViewSubsetAssign(SrcCube, sView, sDim2, sSub2);
ViewSubsetAssign(SrcCube, sView, sDim3, sSub3);
ViewSubsetAssign(SrcCube, sView, sDim4, sSub4);
ViewSubsetAssign(SrcCube, sView, sDim5, sSub5);
ViewSubsetAssign(SrcCube, sView, sDim6, sSub6);
ViewSubsetAssign(SrcCube, sView, sDim7, sSub7);
ViewSubsetAssign(SrcCube, sView, sDim8, sSub8);
ViewSubsetAssign(SrcCube, sView, sDim9, sSub9);
ViewSubsetAssign(SrcCube, sView, sDim10, sSub10);
ViewSubsetAssign(SrcCube, sView, sDim11, sSub11);
ViewSubsetAssign(SrcCube, sView, sDim12, sSub12);
ViewSubsetAssign(SrcCube, sView, sDim13, sSub13);

Code: Select all

=======================
@Data
=======================

#****Begin: Generated Statements***
#****End: Generated Statements****

# initialize variable used to load into target members.

ldVersion = Version;
ldInterlineFlag = SF_InterlineFlag;
ldBorneFlag = SF_BorneFlag;
ldDirection = SF_Direction;
ldAccount = SF_Account;
ldService = SF_Service;
ldVoyage = SF_Voyage;
ldOriginRegion = SF_OriginRegion;
ldDestinationREgion = SF_DestinationRegion;
ldPeriod = Period;
ldContainerType = SF_ContainerType;
ldContainerSize = SF_ContainerSize;
ldMeasures = SF_Measures;

# Check which target values to use
# update load variables if a target value is given.

If(TgtVersion@<>'');
ldVersion = TgtVersion;
endif;

if(TgtInterlineFlag@<>'');
ldInterlineFlag = TgtInterlineFlag;
endif;

if(TgtBorneFlag@<>'');
ldBorneFlag  = TgtBorneFlag;
endif;

if(TgtDirection@<>'');
ldDirection = TgtDirection;
endif;

if(TgtAccount@<>'');
ldAccount = TgtAccount;
endif;

if(TgtService@<>'');
ldService = TgtService;
endif;

if(TgtVoyage@<>'');
ldVoyage  = TgtVoyage;
endif;

if(TgtOriginRegion@<>'');
ldOriginRegion = TgtOriginRegion;
endif;

if(TgtDestinationREgion@<>'');
ldDestinationREgion = TgtDestinationREgion;
endif;

if(TgtPeriod@<>'');
ldPeriod = TgtPeriod;
endif;

if(TgtCotainerType@<>'');
ldCotainerType = TgtCotainerType;
endif;

if(TgtContainerSize@<>'');
ldContainerSize = TgtContainerSize;
endif;

if(TgtMeasures@<>'');
ldMeasures= TgtMeasures;
endif;

# Copy Data
#CellPutN(NVALUE, TgtCube, ldVersion, ldInterlineFlag, ldBorneFlag, ldDirection, ldAccount, ldService, ldVoyage, ldOriginRegion, ldDestinationREgion, l
#dPeriod, ldContainerType, ldContainerSize, ldMeasures);

IF(CELLISUPDATEABLE(srcCube, 
                       ldVersion, ldInterlineFlag, ldBorneFlag, ldContainerSize, ldContainerType, ldDirection, ldAccount, ldService, ldOriginRegion, ldDestinationREgion, ldPeriod, ldVoyage, ldMeasures)=1);
CellPutN(NVALUE, srcCube, ldVersion, ldInterlineFlag, ldBorneFlag, ldContainerSize, ldContainerType, ldDirection, ldAccount, ldService, ldOriginRegion, ldDestinationREgion, ldPeriod, ldVoyage, ldMeasures);
ENDIF;

Code: Select all

===============================
@Epilog:
==============================

# Destroy View
ViewDestroy(SrcCube, sView);

# Destroy Subsets
SubsetDestroy(sDim1, sSub1);
SubsetDestroy(sDim2, sSub2);
SubsetDestroy(sDim3, sSub3);
SubsetDestroy(sDim4, sSub4);
SubsetDestroy(sDim5, sSub5);
SubsetDestroy(sDim6, sSub6);
SubsetDestroy(sDim7, sSub7);
SubsetDestroy(sDim8, sSub8);
SubsetDestroy(sDim9, sSub9);
SubsetDestroy(sDim10, sSub10);
SubsetDestroy(sDim11, sSub11);
SubsetDestroy(sDim12, sSub12);
SubsetDestroy(sDim13, sSub13);



Am I required to write any rule to achieve this?
please some one help me

Re: copying data from one version to another.

Posted: Thu Jun 02, 2011 7:25 am
by Gregor Koch
Hi

Simple answer: No, you don't have to write a rule.

It would be helpful if you would state what kind of errors you get or how it is not working.

But here are some general comments on your code.

Prolog:
If you want to use all elements out of a dimension in your source/target view then don't assign a subset at all.
Whatever you do don't use Dynamic Subsets as source in your load as most times it will run like a dog with one leg compared to a static subset (or no subset).

Data Tab:
Why even bother to define loads of different targets (e.g. Accounts)?
The only one you really need is Version and Period and I think Period is the one that is incorrect.
It looks like you are trying to write to period '2012' rather than to 'Jan2012', 'Feb2012'.
The CellIsUpdateable will return '0' for all and you won't write.

Try a
Tgtperiod=Subst(ldPeriod,1,3)|TgtYear;
(where TgtYear ='2012'; and ldPeriod is the variable form your source)
and use that in your CellIsUpdateable and CellPut.

Also, there are some other posts which include some generic code to copy data within or between cubes.

Cheers

Re: copying data from one version to another.

Posted: Thu Jun 02, 2011 12:23 pm
by tomok
If you are copying inside the same cube then I have found that breaking that into two processes, where you extract the values to a flat file in process 1 and then load back into the cube in process 2, is much. much faster than doing it all in the same process.
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited