copying data from one version to another.

Post Reply
ryan
Posts: 59
Joined: Thu May 26, 2011 5:04 am
OLAP Product: COGNOS
Version: 9.0
Excel Version: 2007

copying data from one version to another.

Post 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
Configuration Details
Microsoft Window Server 2003 Exterprise x64 Edition Service Pack 2
RAM 32 GB
using IBM Cognos Express Xcelerator (Version 10.1)
MS-Office Excel 2010 (12.0.6514.5000)SP2 MSO (12.0.6425.1000)
Microsoft .NET Framework 3.5
Top
Gregor Koch
MVP
Posts: 263
Joined: Fri Jun 27, 2008 12:15 am
OLAP Product: Cognos TM1, CX
Version: 9.0 and up
Excel Version: 2007 and up

Re: copying data from one version to another.

Post 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
Top
tomok
MVP
Posts: 2836
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:
Contact tomok

Re: copying data from one version to another.

Post 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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Top
Post Reply

Return to “IBM TM1, Planning Analytics, PAx and PAW”