Copying Data from 1 Element to Another via TI
-
- Regular Participant
- Posts: 269
- Joined: Tue Apr 21, 2009 3:43 am
- OLAP Product: Cognos TM1, Planning
- Version: 9.1 SP3 9.4 MR1 FP1 9.5
- Excel Version: 2003
Copying Data from 1 Element to Another via TI
I'd like has anyone done anything like copying data from 1 version to another with just creating a parameter for the new name of the version? Always using the last member of the version dimension as the source data? Since I'm not too familiar with how data transfer is without declaring any data source or cube view.
Since im looking to populate all cubes with the version dimension with the last version's data when a new version is created and not do it manually by declaring the cube view.
Since im looking to populate all cubes with the version dimension with the last version's data when a new version is created and not do it manually by declaring the cube view.
-
- 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: Copying Data from 1 Element to Another via TI
Hi
Tonight, I have been writing a I process that does exactly this. I will write a post and give the full code tomorrow evening after work. Off to bed now!![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
Wim
Tonight, I have been writing a I process that does exactly this. I will write a post and give the full code tomorrow evening after work. Off to bed now!
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
Wim
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
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
-
- 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: Copying Data from 1 Element to Another via TI
Here's my article, can be found on my website too. Good luck.
Often a TM1 consultant or Admin person needs to copy all data on an element to another element. Examples may include a budget version iteration that is duplicated to ease data entry, or an element that is used as a backup. Also, in some cases, a TI process might temporarily store data on another element.
You can of course do this manually. Open the cube in a Cube viewer, create a view containing source and target element, and list all elements to the lowest level in rows and/or columns. Don't forget to turn zero suppression on or your view is very likely to be too big. Then, either use data spreading or copy-paste the data. Yet another approach is to slice the data on the source element to Excel, and use a bunch of DBSW functions to upload the values on the target element. Over at olapforums.com, Appleglaze28 asks for a generic TI-based approach. Here is how:
Create a view on the cube in which you need to copy data. It can be a really trivial view with only 1 element seected in each of the dimensions, that is not important. But you will find it handy to layout the dimensions in the cube to match the order of the dimensions in the cube: stack the last dimension of the cube in the columns, the last-but-one dimension in the rows, and the other dimensions (in the right order) in the titles. Save the view with the name SourceView.
After that, create a new process. As the Datasource Type, choose TM1 > Cube view. In the Data Source Name, navigate to the cube and the view you just created. Your view is an easy one and does not contain the real source data to be copied, but hold on… TI code in the Prolog tab of the process will recreate the view on the fly depending on the element whose data you need to copy! All that you need to do now is make sure it exists to help you setup the process.
Then turn to the Advanced > Parameters tab and create 3 parameters:
1. Name: pDimension, Type: String, Default Value: leave this empty Prompt Question: Copy the data on an element in which dimension ?
2. Name: pSourceElement, Type: String, Default Value: leave this empty, Prompt Question: Name of the Source element ?
3. Name: pTargetElement, Type: String, Default Value: leave this empty, Prompt Question: Name of the Target element ?
Before diving into the code, a few words on the benefits of this process. The process allows you to copy text data. In addition, it allows you to copy data from consolidated levels. Also, pay attention to the 7 Preliminary checks that are done prior to advancing to the real stuff. That boils down to creating a view for the target data and zero it out, and creating a view for the source data to loop through in the Data tab. The source view potentially includes consolidated values if you ask so based on pSourceElement.
Here is the code in the Prolog tab:
Here is the code in the Data tab:
Here is the code in the Epilog tab:
As you can tell from inspecting the code, the code in the Data tab is specific to your cube at hand. Therefore, you will certainly have to adjust the code in the Data tab. Together with the topc statements in the Prolog tab, these are the only changes you need to make to the process. For instance, the variable containing the name of the view for the source data (in the Prolog tab) should match your choice in the source tab. Specifically for the Data tab, add the correct variable names representing the dimensions, in the correct order! If you do this correctly the process should run smoothly. If you prefer to, turn off the logging of changes to this cube whilst executing it.
Wim
Often a TM1 consultant or Admin person needs to copy all data on an element to another element. Examples may include a budget version iteration that is duplicated to ease data entry, or an element that is used as a backup. Also, in some cases, a TI process might temporarily store data on another element.
You can of course do this manually. Open the cube in a Cube viewer, create a view containing source and target element, and list all elements to the lowest level in rows and/or columns. Don't forget to turn zero suppression on or your view is very likely to be too big. Then, either use data spreading or copy-paste the data. Yet another approach is to slice the data on the source element to Excel, and use a bunch of DBSW functions to upload the values on the target element. Over at olapforums.com, Appleglaze28 asks for a generic TI-based approach. Here is how:
Create a view on the cube in which you need to copy data. It can be a really trivial view with only 1 element seected in each of the dimensions, that is not important. But you will find it handy to layout the dimensions in the cube to match the order of the dimensions in the cube: stack the last dimension of the cube in the columns, the last-but-one dimension in the rows, and the other dimensions (in the right order) in the titles. Save the view with the name SourceView.
After that, create a new process. As the Datasource Type, choose TM1 > Cube view. In the Data Source Name, navigate to the cube and the view you just created. Your view is an easy one and does not contain the real source data to be copied, but hold on… TI code in the Prolog tab of the process will recreate the view on the fly depending on the element whose data you need to copy! All that you need to do now is make sure it exists to help you setup the process.
Then turn to the Advanced > Parameters tab and create 3 parameters:
1. Name: pDimension, Type: String, Default Value: leave this empty Prompt Question: Copy the data on an element in which dimension ?
2. Name: pSourceElement, Type: String, Default Value: leave this empty, Prompt Question: Name of the Source element ?
3. Name: pTargetElement, Type: String, Default Value: leave this empty, Prompt Question: Name of the Target element ?
Before diving into the code, a few words on the benefits of this process. The process allows you to copy text data. In addition, it allows you to copy data from consolidated levels. Also, pay attention to the 7 Preliminary checks that are done prior to advancing to the real stuff. That boils down to creating a view for the target data and zero it out, and creating a view for the source data to loop through in the Data tab. The source view potentially includes consolidated values if you ask so based on pSourceElement.
Here is the code in the Prolog tab:
Code: Select all
############################
# Wim Gielis
# Aexis NV Belgium
# wim.gielis@gmail.com
# 11/12/09
# TI code to copy data from 1 element to another
# (elements must be in the same dimension)
# Also on http://www.wimgielis.be
##############################
## A. Explicit constants
vCubeName='rdmc_KPI_Data';
vSourceViewName='SourceView';
vTargetViewName='TargetView';
vSourceSubsetName='SourceSubset';
vTargetSubsetName='TargetSubset';
## B. Preliminary checks
# dimension-related
IF(DIMENSIONEXISTS(pDimensionName)=0);
PROCESSQUIT;
ENDIF;
# elements-related
IF(DIMIX(pDimensionName,pSourceElement)=0);
PROCESSQUIT;
ENDIF;
IF(DIMIX(pDimensionName,pTargetElement)=0 % DTYPE(pDimensionName,pTargetElement)@='C');
PROCESSQUIT;
ENDIF;
IF(TRIM(pSourceElement)@=TRIM(pTargetElement));
PROCESSQUIT;
ENDIF;
# process-related
IF(DATASOURCETYPE@<>'VIEW');
PROCESSQUIT;
ENDIF;
IF(DATASOURCENAMEFORSERVER@<>vCubeName);
PROCESSQUIT;
ENDIF;
IF(DATASOURCECUBEVIEW@<>vSourceViewName);
PROCESSQUIT;
ENDIF;
## C. Create a view containing all data for the Source element
# 1. Tidy up the Source view
VIEWDESTROY(vCubeName,vSourceViewName);
SUBSETDESTROY(pDimensionName,vSourceSubsetName);
# 2. Create subset
SUBSETCREATE(pDimensionName,vSourceSubsetName);
SUBSETELEMENTINSERT(pDimensionName,vSourceSubsetName,pSourceElement,1);
# 3. Create view & assign subset
VIEWCREATE(vCubeName,vSourceViewName);
VIEWSUBSETASSIGN(vCubeName,vSourceViewName,pDimensionName,vSourceSubsetName);
IF(DTYPE(pDimensionName,pSourceElement)@='C');
VIEWSETSKIPCALCS(vCubeName,vSourceViewName,0);
ENDIF;
VIEWSETSKIPRULEVALUES(vCubeName,vSourceViewName,0);
## D. Zero out a view containing all data for the Target element
# 1. Tidy up
VIEWDESTROY(vCubeName,vTargetViewName);
SUBSETDESTROY(pDimensionName,vTargetSubsetName);
# 2. Create subset
SUBSETCREATE(pDimensionName,vTargetSubsetName);
SUBSETELEMENTINSERT(pDimensionName,vTargetSubsetName,pTargetElement,1);
# 3. Create view & assign subset
VIEWCREATE(vCubeName,vTargetViewName);
VIEWSUBSETASSIGN(vCubeName,vTargetViewName,pDimensionName,vTargetSubsetName);
# 4. Zero out the view
VIEWZEROOUT(vCubeName,vTargetViewName);
# 5. Tidy up
VIEWDESTROY(vCubeName,vTargetViewName);
SUBSETDESTROY(pDimensionName,vTargetSubsetName);
Code: Select all
IF(CELLISUPDATEABLE(vCubeName,
MY_DIMENSION_VARIABLES_CHANGE_OVER_HERE,
pTargetElement,OTHER_DIMENSION_VARIABLES)=1);
IF(VALUE_IS_STRING=0);
CELLPUTN(NValue,vCubeName,
MY_DIMENSION_VARIABLES_CHANGE_OVER_HERE,
pTargetElement,OTHER_DIMENSION_VARIABLES);
ELSE;
CELLPUTS(SValue,vCubeName,
MY_DIMENSION_VARIABLES_CHANGE_OVER_HERE,
pTargetElement,OTHER_DIMENSION_VARIABLES);
ENDIF;
ENDIF;
Code: Select all
# 4. Tidy up the Source view
VIEWDESTROY(vCubeName,vSourceViewName);
SUBSETDESTROY(pDimensionName,vSourceSubsetName);
Wim
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
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
-
- Community Contributor
- Posts: 132
- Joined: Thu Oct 15, 2009 7:45 pm
- OLAP Product: TM1
- Version: 9.4.1 9.5 9.5.1
- Excel Version: 2003 2007
Re: Copying Data from 1 Element to Another via TI
I have to do this constantly and developed a process a few months back.
You do need a view from the source cube. You can use any view you would like, as the process will create its own view to use in the Prolog.
Use the variables tab to define variables for the dimensions to be used later on.
Short summary of the process:
Prolog - define source members for each dimension, use 'All' for all members. define target members for each dimension (must be level 0), leave blank if all members. define source cube, and a target cube if moving data between cubes. define variables for view name, dimension names, subset names, MDX scripts. error check to delete any existing view/subset. create subsets using source members. create a new view, assign subsets to the view, change datasource of the TI process to be the new view.
Data - if a target member is defined than load to that member, else load to the source member for that dimension.
*** I did not account for text data as we do not store text data, but you can easily add an if statement to the data tab if needed. ***
Epilog - Delete all subsets and the view created.
Code:
Prolog -
You would have to update the number according to the number of dimensions you have. Also update the names of the dimensions as those are important in the MDX scripts.
Data -
The "v" variables were defined on the variables tab. Also, you can update the variable in the CellPutN statement to use TgtCube if needed.
Epilog -
You do need a view from the source cube. You can use any view you would like, as the process will create its own view to use in the Prolog.
Use the variables tab to define variables for the dimensions to be used later on.
Short summary of the process:
Prolog - define source members for each dimension, use 'All' for all members. define target members for each dimension (must be level 0), leave blank if all members. define source cube, and a target cube if moving data between cubes. define variables for view name, dimension names, subset names, MDX scripts. error check to delete any existing view/subset. create subsets using source members. create a new view, assign subsets to the view, change datasource of the TI process to be the new view.
Data - if a target member is defined than load to that member, else load to the source member for that dimension.
*** I did not account for text data as we do not store text data, but you can easily add an if statement to the data tab if needed. ***
Epilog - Delete all subsets and the view created.
Code:
Prolog -
Code: Select all
# ********************** Source Dimension Values **************************************#
# Type the member name if other than all
SrcGeo = 'US';
SrcLIC = 'All';
SrcPer = 'All';
SrcCS = 'All';
SrcCase = '2009';
SrcDiv = 'All';
SrcChan = 'All';
SrcCube = 'test';
# ********************** Target Dimension Values **************************************#
# Make sure only the target dimension that is being updated is filled
# Only required if it is changing
# THE VALUE CAN ONLY BE A LEVEL 0 Value.
TgtGeo = '912';
TgtLIC = '';
TgtPer = '';
TgtCS = '';
TgtCase = '';
TgtDiv = '';
TgtChan = '';
#TgtCube = '';
# Declare View name
sView = 'Copy';
# Declare Dimensions
sDim1 = 'geo';
sDim2 = 'customer';
sDim3 = 'account';
sDim4 = 'period';
sDim5 = 'case';
sDim6 = 'division';
sDim7 = 'channel';
# Declare Dimension Subsets Name
sSub1 = 'Copy - '| SrcGeo |'';
sSub2 = 'Copy - '| SrcCS |'';
sSub3 = 'Copy - '| SrcLIC |'';
sSub4 = 'Copy - '| SrcPer |'';
sSub5 = 'Copy - '| SrcCase |'';
sSub6 = 'Copy - '| SrcDiv |'';
sSub7 = 'Copy - '| SrcChan |'';
# Declare Subsets Properties
# Only used if user choses something other than All in the parameters
sSubT1 = '['| sDim1 |'].['| SrcGeo |']';
sSubT2 = '['| sDim2 |'].['| SrcCS |']';
sSubT3 = '['| sDim3 |'].['| SrcLIC |']';
sSubT4 = '['| sDim4 |'].['| SrcPer |']';
sSubT5 = '['| sDim5 |'].['| SrcCase |']';
sSubT6 = '['| sDim6 |'].['| SrcDiv |']';
sSubT7 = '['| sDim7 |'].['| SrcChan |']';
# 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;
# Create subsets
if (SrcGeo@='All');
SubsetCreatebyMDX(sSub1, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim1|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub1, '{TM1DRILLDOWNMEMBER( {'|sSubT1|'},ALL,RECURSIVE )}');
endif;
if (SrcCS@='All');
SubsetCreatebyMDX(sSub2, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim2|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub2, '{TM1DRILLDOWNMEMBER( {'|sSubT2|'},ALL,RECURSIVE )}');
endif;
if (SrcCase@='All');
SubsetCreatebyMDX(sSub5, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim5|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub5, '{TM1DRILLDOWNMEMBER( {'|sSubT5|'},ALL,RECURSIVE )}');
endif;
if (SrcLIC@='All');
SubsetCreatebyMDX(sSub3, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim3|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub3, '{TM1DRILLDOWNMEMBER( {'|sSubT3|'},ALL,RECURSIVE )}');
endif;
if (SrcPer@='All');
SubsetCreatebyMDX(sSub4, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim4|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub4, '{TM1DRILLDOWNMEMBER( {'|sSubT4|'},ALL,RECURSIVE )}');
endif;
if (SrcDiv@='All');
SubsetCreatebyMDX(sSub6, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim6|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub6, '{TM1DRILLDOWNMEMBER( {'|sSubT6|'},ALL,RECURSIVE )}');
endif;
if (SrcChan@='All');
SubsetCreatebyMDX(sSub7, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim7|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub7, '{TM1DRILLDOWNMEMBER( {'|sSubT7|'},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);
Data -
Code: Select all
# initialize variable used to load into target members.
ldGeo = vGeo;
ldCS = vCS;
ldCase = vCase;
ldLIC = vLIC;
ldPer = vPer;
ldDiv = vDiv;
ldChan = vChan;
# Check which target values to use
# update load variables if a target value is given.
If(TgtCase@<>'');
ldCase = TgtCase;
endif;
if(TgtGeo@<>'');
ldGeo = TgtGeo;
endif;
if(TgtCS@<>'');
ldCS = TgtCS;
endif;
if(TgtCase@<>'');
ldCase = TgtCase;
endif;
if(TgtLIC@<>'');
ldLIC = TgtLIC;
endif;
if(TgtPer@<>'');
ldPer = TgtPer;
endif;
if(TgtDiv@<>'');
ldDiv = TgtDiv;
endif;
if(TgtChan@<>'');
ldChan = TgtChan;
endif;
# Copy Data
CellPutN(NVALUE, SrcCube, ldCase, ldChan, ldCS, ldDiv, ldGeo, ldLIC, ldPer);
Epilog -
Code: Select all
# 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);
Ankur Jain
-
- 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: Copying Data from 1 Element to Another via TI
Hello Ajain,
perhaps I missed something, but you don't do a zero out for the target cells? Which does not have to be wrong but then you should only copy to elements without data?
Wim
perhaps I missed something, but you don't do a zero out for the target cells? Which does not have to be wrong but then you should only copy to elements without data?
Wim
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
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
-
- Regular Participant
- Posts: 269
- Joined: Tue Apr 21, 2009 3:43 am
- OLAP Product: Cognos TM1, Planning
- Version: 9.1 SP3 9.4 MR1 FP1 9.5
- Excel Version: 2003
Re: Copying Data from 1 Element to Another via TI
The sample you gave is only for 1 cube is there no other way to copy all the data from a specific element dimension available in a number of cubes to a new target element in the same dimension?
Like I have 10 cubes with the version dimension. Isn't there a way to have the copy apply to all rather than just 1 cube? cause wouldnt declaring a view means its only allowed for a specific cube. Cause there might be a way that I'm not familiar with like zero out of cubes, which i used to think must be done per cube but Wim gave a nice TI process to ease the pain.
Wim, I'm still working on testing the sample code you gave me, do I really need to create dimension subset for the dimension? can you help me understand why?
Like I have 10 cubes with the version dimension. Isn't there a way to have the copy apply to all rather than just 1 cube? cause wouldnt declaring a view means its only allowed for a specific cube. Cause there might be a way that I'm not familiar with like zero out of cubes, which i used to think must be done per cube but Wim gave a nice TI process to ease the pain.
Wim, I'm still working on testing the sample code you gave me, do I really need to create dimension subset for the dimension? can you help me understand why?
-
- Community Contributor
- Posts: 132
- Joined: Thu Oct 15, 2009 7:45 pm
- OLAP Product: TM1
- Version: 9.4.1 9.5 9.5.1
- Excel Version: 2003 2007
Re: Copying Data from 1 Element to Another via TI
Wim, I have another process that I use to zero out target data as needed. I do not understand your second question.Wim Gielis wrote:Hello Ajain,
perhaps I missed something, but you don't do a zero out for the target cells? Which does not have to be wrong but then you should only copy to elements without data?
Wim
appleglaze28, if you wanted to use multiple cubes with same structure as source, you can change the source cube by updating the variable "DatasourceNameForServer" to the new cube.appleglaze28 wrote:The sample you gave is only for 1 cube is there no other way to copy all the data from a specific element dimension available in a number of cubes to a new target element in the same dimension?
Like I have 10 cubes with the version dimension. Isn't there a way to have the copy apply to all rather than just 1 cube? cause wouldnt declaring a view means its only allowed for a specific cube. Cause there might be a way that I'm not familiar with like zero out of cubes, which i used to think must be done per cube but Wim gave a nice TI process to ease the pain.
To accomplish this, I would add a parameter that would ask the name of the source cube. You can then use the parameter answer to change the value for "DatasourceNameForServer" and variable "SrcCube" on the prolog tab prior to creating the temporary view.
Ankur Jain
-
- Regular Participant
- Posts: 269
- Joined: Tue Apr 21, 2009 3:43 am
- OLAP Product: Cognos TM1, Planning
- Version: 9.1 SP3 9.4 MR1 FP1 9.5
- Excel Version: 2003
Re: Copying Data from 1 Element to Another via TI
Hi Ajain
I tried the TI Process script you gave...I'm not sure where I went wrong since I'm sure where I went wrong somewhere cause the process was successful but the value still was not copied to the other element intersection of one of the dimension.
Prolog:
Data
Epilog
I'm still tried testing Wim's also but neither gave me a successful outcome from testing it...I'm still in the process of learning TI more indepth
I tried the TI Process script you gave...I'm not sure where I went wrong since I'm sure where I went wrong somewhere cause the process was successful but the value still was not copied to the other element intersection of one of the dimension.
Prolog:
Code: Select all
# ********************** Source Dimension Values **************************************#
# Type the member name if other than all
SrcDim1 = 'Budget 1';
SrcDim2 = 'All';
SrcDim3 = 'All';
SrcCube = 'test_cube';
# ********************** Target Dimension Values **************************************#
# Make sure only the target dimension that is being updated is filled
# Only required if it is changing
# THE VALUE CAN ONLY BE A LEVEL 0 Value.
TgtDim1 = 'Actual';
TgtDim2 = 'All';
TgtDim3 = 'All';
#TgtCube = 'test_cube';
# Declare View name
sView = 'Copy';
# Declare Dimensions
sDim1 = 'base_version';
sDim2 = 'base_year';
sDim3 = 'base_month';
# Declare Dimension Subsets Name
sSub1 = 'Copy - '| SrcDim1 |'';
sSub2 = 'Copy - '| SrcDim2 |'';
sSub3 = 'Copy - '| SrcDim3 |'';
# Declare Subsets Properties
# Only used if user choses something other than All in the parameters
sSubT1 = '['| sDim1 |'].['| SrcDim1 |']';
sSubT2 = '['| sDim2 |'].['| SrcDim2 |']';
sSubT3 = '['| sDim3 |'].['| SrcDim3 |']';
# 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;
# Create subsets
if (SrcDim1@='All');
SubsetCreatebyMDX(sSub1, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim1|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub1, '{TM1DRILLDOWNMEMBER( {'|sSubT1|'},ALL,RECURSIVE )}');
endif;
if (SrcDim2@='All');
SubsetCreatebyMDX(sSub2, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim2|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub2, '{TM1DRILLDOWNMEMBER( {'|sSubT2|'},ALL,RECURSIVE )}');
endif;
if (SrcDim3@='All');
SubsetCreatebyMDX(sSub3, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim3|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub3, '{TM1DRILLDOWNMEMBER( {'|sSubT3|'},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);
Data
Code: Select all
# ********************** Source Dimension Values **************************************#
# Type the member name if other than all
SrcDim1 = 'Budget 1';
SrcDim2 = 'All';
SrcDim3 = 'All';
SrcCube = 'test_cube';
# ********************** Target Dimension Values **************************************#
# Make sure only the target dimension that is being updated is filled
# Only required if it is changing
# THE VALUE CAN ONLY BE A LEVEL 0 Value.
TgtDim1 = 'Actual';
TgtDim2 = 'All';
TgtDim3 = 'All';
#TgtCube = 'test_cube';
# Declare View name
sView = 'Copy';
# Declare Dimensions
sDim1 = 'base_version';
sDim2 = 'base_year';
sDim3 = 'base_month';
# Declare Dimension Subsets Name
sSub1 = 'Copy - '| SrcDim1 |'';
sSub2 = 'Copy - '| SrcDim2 |'';
sSub3 = 'Copy - '| SrcDim3 |'';
# Declare Subsets Properties
# Only used if user choses something other than All in the parameters
sSubT1 = '['| sDim1 |'].['| SrcDim1 |']';
sSubT2 = '['| sDim2 |'].['| SrcDim2 |']';
sSubT3 = '['| sDim3 |'].['| SrcDim3 |']';
# 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;
# Create subsets
if (SrcDim1@='All');
SubsetCreatebyMDX(sSub1, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim1|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub1, '{TM1DRILLDOWNMEMBER( {'|sSubT1|'},ALL,RECURSIVE )}');
endif;
if (SrcDim2@='All');
SubsetCreatebyMDX(sSub2, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim2|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub2, '{TM1DRILLDOWNMEMBER( {'|sSubT2|'},ALL,RECURSIVE )}');
endif;
if (SrcDim3@='All');
SubsetCreatebyMDX(sSub3, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim3|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub3, '{TM1DRILLDOWNMEMBER( {'|sSubT3|'},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);
Epilog
Code: Select all
# Destroy View
ViewDestroy(SrcCube, sView);
# Destroy Subsets
SubsetDestroy(sDim1, sSub1);
SubsetDestroy(sDim2, sSub2);
SubsetDestroy(sDim3, sSub3);
- Attachments
-
- cube to test the process
- test.jpg (39.42 KiB) Viewed 52572 times
-
- Community Contributor
- Posts: 132
- Joined: Thu Oct 15, 2009 7:45 pm
- OLAP Product: TM1
- Version: 9.4.1 9.5 9.5.1
- Excel Version: 2003 2007
Re: Copying Data from 1 Element to Another via TI
appleglaze,
can you please post the correct code for the Data tab? It looks like the same code as the prolog code.
In your view, you show that Budget 1 has all zeros. Budget 1 is your source?
can you please post the correct code for the Data tab? It looks like the same code as the prolog code.
In your view, you show that Budget 1 has all zeros. Budget 1 is your source?
Ankur Jain
-
- Posts: 7
- Joined: Fri Oct 30, 2009 6:07 am
- OLAP Product: tm1
- Version: 9.4 and 9.5
- Excel Version: 2007
Re: Copying Data from 1 Element to Another via TI
Hi All,
I've to implement a similar kind of functionality for around 20 cubes approx.The requirement is like the user wants to transfer data across 20 different cubes parameters being the same for all. So he need not enter the parameters again n again.
For this I've modified the procedure provided by Wim to satisfy the requirements. What I have to do is that for the user to transfer data from one element to another he'll have to enter the parameters in the very first TI process, then from the 1st TI process I Execute the next TI Process for another cube by passing the Parameters of the 1st TI Process to the ExecuteProcess function.
Currently I've implemented this for 4 cubes but it takes a very long time to complete. What could be the reason for this and how can it be resolved ???
Any help would be appreciated...
Thanks,
Aarti
I've to implement a similar kind of functionality for around 20 cubes approx.The requirement is like the user wants to transfer data across 20 different cubes parameters being the same for all. So he need not enter the parameters again n again.
For this I've modified the procedure provided by Wim to satisfy the requirements. What I have to do is that for the user to transfer data from one element to another he'll have to enter the parameters in the very first TI process, then from the 1st TI process I Execute the next TI Process for another cube by passing the Parameters of the 1st TI Process to the ExecuteProcess function.
Currently I've implemented this for 4 cubes but it takes a very long time to complete. What could be the reason for this and how can it be resolved ???
Any help would be appreciated...
Thanks,
Aarti
-
- Posts: 1
- Joined: Wed Sep 08, 2010 9:30 am
- OLAP Product: TM1
- Version: 9.4.1
- Excel Version: 2007
Re: Copying Data from 1 Element to Another via TI
HI Wim! This code is perfect for me to learn TI, but what I want to ask is, whether it is necessary to param the dimension, since I have to put the dimension-variables in the right order in the data tab? Depending on the Dimension, I choose as parameter, the Dimensionvariable pDimensionname will be on different places in the data-tab.. is that right?Wim Gielis wrote:
As you can tell from inspecting the code, the code in the Data tab is specific to your cube at hand. Therefore, you will certainly have to adjust the code in the Data tab. Together with the topc statements in the Prolog tab, these are the only changes you need to make to the process. For instance, the variable containing the name of the view for the source data (in the Prolog tab) should match your choice in the source tab. Specifically for the Data tab, add the correct variable names representing the dimensions, in the correct order! If you do this correctly the process should run smoothly. If you prefer to, turn off the logging of changes to this cube whilst executing it.
Wim
greez from austria,
mark
-
- 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: Copying Data from 1 Element to Another via TI
Hi there
Yes, you have to change the data tab.
I see what you mean, you should insert the dimension names and also in the correct order, but the dimension you pick decides where to put the target element.
The process is not dynamic in that way - I will think whether it's possible to incorporate. I don't think so, however.
Wim
Yes, you have to change the data tab.
I see what you mean, you should insert the dimension names and also in the correct order, but the dimension you pick decides where to put the target element.
The process is not dynamic in that way - I will think whether it's possible to incorporate. I don't think so, however.
Wim
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
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
-
- Posts: 22
- Joined: Wed Jan 26, 2011 3:06 pm
- OLAP Product: TM1
- Version: 9.5.1
- Excel Version: 2003
Re: Copying Data from 1 Element to Another via TI
Hi Wim,
I had an error regarding in Data tab, where there is one element that using picklist show the error
"Data Source line (1) Error: Data procedure line (0): Cannot convert field number 7, value "% KPI" to a real number."
thanks for your attention
I had an error regarding in Data tab, where there is one element that using picklist show the error
"Data Source line (1) Error: Data procedure line (0): Cannot convert field number 7, value "% KPI" to a real number."
thanks for your attention
-
- Posts: 8
- Joined: Thu Oct 14, 2010 3:22 pm
- OLAP Product: TM1
- Version: 10.1
- Excel Version: 2010
Re: Copying Data from 1 Element to Another via TI
Hi,
I've used the code you have been discussing and i just wanted a say thank you as this has been extremely useful..... However, i have one query i cannot get my head around.
I have used the coding mentioned in earlier posts (see below), but what i was hoping to achieve has not materialised. I want to copy values from annual total to a 0-level element in another year, but when i trigger this TI the only values copied over are from the October-13 period. Could someone advise on how to achieve this with what i have so far?
Many thanks
Lee
Prolog
# ********************** Source Dimension Values **************************************#
# Type the member name if other than all
SrcPeriod = '2013';
SrcCurr = 'Local Currency';
SrcVersion = 'Actual';
SrcLevel = 'All';
SrcChannel = 'Germany';
SrcCountry = 'All';
SrcSector = 'All';
SrcTechnology = 'All';
SrcProduct = 'All';
SrcMeasures = 'All';
SrcCube = 'ActualResults';
# ********************** Target Dimension Values **************************************#
# Make sure only the target dimension that is being updated is filled
# Only required if it is changing
# THE VALUE CAN ONLY BE A LEVEL 0 Value.
TgtPeriod = 'Oct-14';
TgtCurr = 'Local Currency';
TgtVersion = 'Actual';
TgtLevel = 'All';
TgtChannel = 'Germany';
TgtCountry = 'All';
TgtSector = 'All';
TgtTechnology = 'All';
TgtProduct = 'All';
TgtMeasures = 'All';
TgtCube = 'ActualResults';
# Declare View name
sView = '5Forecasting';
# Declare Dimensions
sDim1 = 'Period';
sDim2 = 'Currency';
sDim3 = 'Version';
sDim4 = 'Level';
sDim5 = 'SalesChannel';
sDim6 = 'Country';
sDim7 = 'Sector';
sDim8 = 'Technology';
sDim9 = 'Product';
sDim10 = 'Measures';
# Declare Dimension Subsets Name
sSub1 = 'Forecast - '| SrcPeriod |'';
sSub2 = 'Forecast - '| SrcCurr |'';
sSub3 = 'Forecast - '| SrcVersion |'';
sSub4 = 'Forecast - '| SrcLevel |'';
sSub5 = 'Forecast - '| SrcChannel |'';
sSub6 = 'Forecast - '| SrcCountry |'';
sSub7 = 'Forecast - '| SrcSector |'';
sSub8 = 'Forecast - '| SrcTechnology |'';
sSub9 = 'Forecast - '| SrcProduct |'';
sSub10 = 'Forecast - '| SrcMeasures |'';
# Declare Subsets Properties
# Only used if user choses something other than All in the parameters
sSubT1 = '['| sDim1 |'].['| SrcPeriod |']';
sSubT2 = '['| sDim2 |'].['| SrcCurr |']';
sSubT3 = '['| sDim3 |'].['| SrcVersion |']';
sSubT4 = '['| sDim4 |'].['| SrcLevel |']';
sSubT5 = '['| sDim5 |'].['| SrcChannel |']';
sSubT6 = '['| sDim6 |'].['| SrcCountry |']';
sSubT7 = '['| sDim7 |'].['| SrcSector |']';
sSubT8 = '['| sDim8 |'].['| SrcTechnology |']';
sSubT9 = '['| sDim9 |'].['| SrcProduct |']';
sSubT10 = '['| sDim10 |'].['| 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;
# Create subsets
if (SrcPeriod@='All');
SubsetCreatebyMDX(sSub1, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim1|'] )}, 3)}, ASC)} ' );
else;
#SubsetCreatebyMDX(sSub1, '{TM1DRILLDOWNMEMBER( {'|sSubT1|'},ALL,RECURSIVE )},3)};
SUBSETCREATE('Period',sSub1);
SUBSETELEMENTINSERT('Period',sSub1,SrcPeriod,1);
endif;
if (SrcCurr@='All');
SubsetCreatebyMDX(sSub2, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim2|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub2, '{TM1DRILLDOWNMEMBER( {'|sSubT2|'},ALL,RECURSIVE )}');
endif;
if (SrcVersion@='All');
SubsetCreatebyMDX(sSub3, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim3|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub3, '{TM1DRILLDOWNMEMBER( {'|sSubT3|'},ALL,RECURSIVE )}');
endif;
if (SrcLevel@='All');
SubsetCreatebyMDX(sSub4, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim4|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub4, '{TM1DRILLDOWNMEMBER( {'|sSubT4|'},ALL,RECURSIVE )}');
endif;
if (SrcChannel@='All');
SubsetCreatebyMDX(sSub5, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim5|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub5, '{TM1DRILLDOWNMEMBER( {'|sSubT5|'},ALL,RECURSIVE )}');
endif;
if (SrcCountry@='All');
SubsetCreatebyMDX(sSub6, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim6|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub6, '{TM1DRILLDOWNMEMBER( {'|sSubT6|'},ALL,RECURSIVE )}');
endif;
if (SrcSector@='All');
SubsetCreatebyMDX(sSub7, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim7|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub7, '{TM1DRILLDOWNMEMBER( {'|sSubT7|'},ALL,RECURSIVE )}');
endif;
if (SrcTechnology@='All');
SubsetCreatebyMDX(sSub8, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim8|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub8, '{TM1DRILLDOWNMEMBER( {'|sSubT8|'},ALL,RECURSIVE )}');
endif;
if (SrcProduct@='All');
SubsetCreatebyMDX(sSub9, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim9|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub9, '{TM1DRILLDOWNMEMBER( {'|sSubT9|'},ALL,RECURSIVE )}');
endif;
if (SrcMeasures@='All');
SubsetCreatebyMDX(sSub10, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim10|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub10, '{TM1DRILLDOWNMEMBER( {'|sSubT10|'},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);
#Arrange dimensions is view
ViewRowDimensionSet('ActualResults','5Forecasting','Product',1);
ViewRowDimensionSet('ActualResults','5Forecasting','Measures',2);
ViewRowDimensionSet('ActualResults','5Forecasting','Country',3);
ViewRowDimensionSet('ActualResults','5Forecasting','Sector',4);
ViewRowDimensionSet('ActualResults','5Forecasting','Technology',5);
ViewRowDimensionSet('ActualResults','5Forecasting','Level',6);
ViewColumnDimensionSet('ActualResults','5Forecasting','Period',1);
#Suppress Zeroes
ViewSuppressZeroesSet ('ActualResults', '5Forecasting', 1);
Data
CellPutN(NVALUE, TgtCube, TgtPeriod,TgtCurr, TgtVersion, Level, TgtChannel, Country, Sector,Technology,Product,Measures);
I've used the code you have been discussing and i just wanted a say thank you as this has been extremely useful..... However, i have one query i cannot get my head around.
I have used the coding mentioned in earlier posts (see below), but what i was hoping to achieve has not materialised. I want to copy values from annual total to a 0-level element in another year, but when i trigger this TI the only values copied over are from the October-13 period. Could someone advise on how to achieve this with what i have so far?
Many thanks
Lee
Prolog
# ********************** Source Dimension Values **************************************#
# Type the member name if other than all
SrcPeriod = '2013';
SrcCurr = 'Local Currency';
SrcVersion = 'Actual';
SrcLevel = 'All';
SrcChannel = 'Germany';
SrcCountry = 'All';
SrcSector = 'All';
SrcTechnology = 'All';
SrcProduct = 'All';
SrcMeasures = 'All';
SrcCube = 'ActualResults';
# ********************** Target Dimension Values **************************************#
# Make sure only the target dimension that is being updated is filled
# Only required if it is changing
# THE VALUE CAN ONLY BE A LEVEL 0 Value.
TgtPeriod = 'Oct-14';
TgtCurr = 'Local Currency';
TgtVersion = 'Actual';
TgtLevel = 'All';
TgtChannel = 'Germany';
TgtCountry = 'All';
TgtSector = 'All';
TgtTechnology = 'All';
TgtProduct = 'All';
TgtMeasures = 'All';
TgtCube = 'ActualResults';
# Declare View name
sView = '5Forecasting';
# Declare Dimensions
sDim1 = 'Period';
sDim2 = 'Currency';
sDim3 = 'Version';
sDim4 = 'Level';
sDim5 = 'SalesChannel';
sDim6 = 'Country';
sDim7 = 'Sector';
sDim8 = 'Technology';
sDim9 = 'Product';
sDim10 = 'Measures';
# Declare Dimension Subsets Name
sSub1 = 'Forecast - '| SrcPeriod |'';
sSub2 = 'Forecast - '| SrcCurr |'';
sSub3 = 'Forecast - '| SrcVersion |'';
sSub4 = 'Forecast - '| SrcLevel |'';
sSub5 = 'Forecast - '| SrcChannel |'';
sSub6 = 'Forecast - '| SrcCountry |'';
sSub7 = 'Forecast - '| SrcSector |'';
sSub8 = 'Forecast - '| SrcTechnology |'';
sSub9 = 'Forecast - '| SrcProduct |'';
sSub10 = 'Forecast - '| SrcMeasures |'';
# Declare Subsets Properties
# Only used if user choses something other than All in the parameters
sSubT1 = '['| sDim1 |'].['| SrcPeriod |']';
sSubT2 = '['| sDim2 |'].['| SrcCurr |']';
sSubT3 = '['| sDim3 |'].['| SrcVersion |']';
sSubT4 = '['| sDim4 |'].['| SrcLevel |']';
sSubT5 = '['| sDim5 |'].['| SrcChannel |']';
sSubT6 = '['| sDim6 |'].['| SrcCountry |']';
sSubT7 = '['| sDim7 |'].['| SrcSector |']';
sSubT8 = '['| sDim8 |'].['| SrcTechnology |']';
sSubT9 = '['| sDim9 |'].['| SrcProduct |']';
sSubT10 = '['| sDim10 |'].['| 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;
# Create subsets
if (SrcPeriod@='All');
SubsetCreatebyMDX(sSub1, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim1|'] )}, 3)}, ASC)} ' );
else;
#SubsetCreatebyMDX(sSub1, '{TM1DRILLDOWNMEMBER( {'|sSubT1|'},ALL,RECURSIVE )},3)};
SUBSETCREATE('Period',sSub1);
SUBSETELEMENTINSERT('Period',sSub1,SrcPeriod,1);
endif;
if (SrcCurr@='All');
SubsetCreatebyMDX(sSub2, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim2|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub2, '{TM1DRILLDOWNMEMBER( {'|sSubT2|'},ALL,RECURSIVE )}');
endif;
if (SrcVersion@='All');
SubsetCreatebyMDX(sSub3, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim3|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub3, '{TM1DRILLDOWNMEMBER( {'|sSubT3|'},ALL,RECURSIVE )}');
endif;
if (SrcLevel@='All');
SubsetCreatebyMDX(sSub4, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim4|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub4, '{TM1DRILLDOWNMEMBER( {'|sSubT4|'},ALL,RECURSIVE )}');
endif;
if (SrcChannel@='All');
SubsetCreatebyMDX(sSub5, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim5|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub5, '{TM1DRILLDOWNMEMBER( {'|sSubT5|'},ALL,RECURSIVE )}');
endif;
if (SrcCountry@='All');
SubsetCreatebyMDX(sSub6, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim6|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub6, '{TM1DRILLDOWNMEMBER( {'|sSubT6|'},ALL,RECURSIVE )}');
endif;
if (SrcSector@='All');
SubsetCreatebyMDX(sSub7, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim7|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub7, '{TM1DRILLDOWNMEMBER( {'|sSubT7|'},ALL,RECURSIVE )}');
endif;
if (SrcTechnology@='All');
SubsetCreatebyMDX(sSub8, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim8|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub8, '{TM1DRILLDOWNMEMBER( {'|sSubT8|'},ALL,RECURSIVE )}');
endif;
if (SrcProduct@='All');
SubsetCreatebyMDX(sSub9, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim9|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub9, '{TM1DRILLDOWNMEMBER( {'|sSubT9|'},ALL,RECURSIVE )}');
endif;
if (SrcMeasures@='All');
SubsetCreatebyMDX(sSub10, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim10|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub10, '{TM1DRILLDOWNMEMBER( {'|sSubT10|'},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);
#Arrange dimensions is view
ViewRowDimensionSet('ActualResults','5Forecasting','Product',1);
ViewRowDimensionSet('ActualResults','5Forecasting','Measures',2);
ViewRowDimensionSet('ActualResults','5Forecasting','Country',3);
ViewRowDimensionSet('ActualResults','5Forecasting','Sector',4);
ViewRowDimensionSet('ActualResults','5Forecasting','Technology',5);
ViewRowDimensionSet('ActualResults','5Forecasting','Level',6);
ViewColumnDimensionSet('ActualResults','5Forecasting','Period',1);
#Suppress Zeroes
ViewSuppressZeroesSet ('ActualResults', '5Forecasting', 1);
Data
CellPutN(NVALUE, TgtCube, TgtPeriod,TgtCurr, TgtVersion, Level, TgtChannel, Country, Sector,Technology,Product,Measures);
-
- Posts: 8
- Joined: Thu Oct 14, 2010 3:22 pm
- OLAP Product: TM1
- Version: 10.1
- Excel Version: 2010
Re: Copying Data from 1 Element to Another via TI
Ignore my last post..... A new day brings a little clarity and the obvious has now become apparent to me.
Cheers
Lee
Cheers
Lee
-
- Community Contributor
- Posts: 132
- Joined: Thu Oct 15, 2009 7:45 pm
- OLAP Product: TM1
- Version: 9.4.1 9.5 9.5.1
- Excel Version: 2003 2007
Re: Copying Data from 1 Element to Another via TI
I have attached my latest version of the process which is much cleaner / easier to use. I also added the subsequent clear process. I have made several changes to improve the process.
Do let me know if you experience any issues with this.
Do let me know if you experience any issues with this.
Last edited by ajain86 on Tue Aug 23, 2011 3:05 pm, edited 1 time in total.
Ankur Jain
-
- Posts: 22
- Joined: Sun Oct 23, 2011 12:14 pm
- OLAP Product: TM1
- Version: 951 952 101 10101
- Excel Version: 2003 2007 2010
Re: Copying Data from 1 Element to Another via TI
Hi Guys,
I really want to know how we can create above process to apply for all the cubes.
SInce i have version dimension in every cube and user will require to transfer data from one version to another in each and every cube. I have created a code and reached at a level by which i am able to get the output of dimensions in each cube along with its name and order. Same has been stored in variables as dim1, dim2 and so on... Now my question is how do i use this variables in data tab to direct TM1 to updata data in members of dimension which has been provided by the said variables. if i put cellputn(cubename,dim1,dim2,dim3..), Tm1 considers dimension name as value of this variables and tries to findout that in resepctive dimension. Which would certainly be not available. E.g. dim1 refers to dimension "Period", than my cell put n function tries to put data in "period" element of dimension "period" rather that putting data in to resepctive element of dimenion "period".
Pls help as to how to deal with this....
i am attaching my code below. Its basically based on wims code. I have added my code to identify dims.
This process will be triggered by other TI whose work is to apply this process to each cube.
Prolog Tab
Data Tab
I really want to know how we can create above process to apply for all the cubes.
SInce i have version dimension in every cube and user will require to transfer data from one version to another in each and every cube. I have created a code and reached at a level by which i am able to get the output of dimensions in each cube along with its name and order. Same has been stored in variables as dim1, dim2 and so on... Now my question is how do i use this variables in data tab to direct TM1 to updata data in members of dimension which has been provided by the said variables. if i put cellputn(cubename,dim1,dim2,dim3..), Tm1 considers dimension name as value of this variables and tries to findout that in resepctive dimension. Which would certainly be not available. E.g. dim1 refers to dimension "Period", than my cell put n function tries to put data in "period" element of dimension "period" rather that putting data in to resepctive element of dimenion "period".
Pls help as to how to deal with this....
i am attaching my code below. Its basically based on wims code. I have added my code to identify dims.
This process will be triggered by other TI whose work is to apply this process to each cube.
Prolog Tab
Code: Select all
## A. Explicit constants
vSourceViewName='SourceView';
vTargetViewName='TargetView';
vSourceSubsetName='SourceSubset';
vTargetSubsetName='TargetSubset';
## B. Preliminary checks
# dimension-related
IF(DIMENSIONEXISTS(pDimensionName)=0);
PROCESSQUIT;
ENDIF;
#elements-related
IF(DIMIX(pDimensionName,pSourceElement)=0);
PROCESSQUIT;
ENDIF;
IF(DIMIX(pDimensionName,pTargetElement)=0 % DTYPE(pDimensionName,pTargetElement)@='C');
PROCESSQUIT;
ENDIF;
IF(TRIM(pSourceElement)@=TRIM(pTargetElement));
PROCESSQUIT;
ENDIF;
#process-related
IF(DATASOURCETYPE@<>'VIEW');
PROCESSQUIT;
ENDIF;
IF(DATASOURCENAMEFORSERVER@<>vCubeName);
PROCESSQUIT;
ENDIF;
#IF(DATASOURCECUBEVIEW@<>vSourceViewName);
#PROCESSQUIT;
#ENDIF;
## C. Create a view containing all data for the Source element
# 1. Tidy up the Source view
VIEWDESTROY(vCubeName,vSourceViewName);
SUBSETDESTROY(pDimensionName,vSourceSubsetName);
# 2. Create subset
SUBSETCREATE(pDimensionName,vSourceSubsetName);
SUBSETELEMENTINSERT(pDimensionName,vSourceSubsetName,pSourceElement,1);
# 3. Create view & assign subset
VIEWCREATE(vCubeName,vSourceViewName);
VIEWSUBSETASSIGN(vCubeName,vSourceViewName,pDimensionName,vSourceSubsetName);
IF(DTYPE(pDimensionName,pSourceElement)@='C');
VIEWSETSKIPCALCS(vCubeName,vSourceViewName,0);
ENDIF;
VIEWSETSKIPRULEVALUES(vCubeName,vSourceViewName,0);
## D. Zero out a view containing all data for the Target element
# 1. Tidy up
VIEWDESTROY(vCubeName,vTargetViewName);
SUBSETDESTROY(pDimensionName,vTargetSubsetName);
# 2. Create subset
SUBSETCREATE(pDimensionName,vTargetSubsetName);
SUBSETELEMENTINSERT(pDimensionName,vTargetSubsetName,pTargetElement,1);
# 3. Create view & assign subset
VIEWCREATE(vCubeName,vTargetViewName);
VIEWSUBSETASSIGN(vCubeName,vTargetViewName,pDimensionName,vTargetSubsetName);
# 4. Zero out the view
VIEWZEROOUT(vCubeName,vTargetViewName);
# 5. Tidy up
#Identify Version Dimension position and total no. of dimensions in cube
Dimmax=1;
Dimlast=TabDim(vCubeName,1);
While(Dimlast@<>' ');
If(DimLast @=pDimensionName);
Bud_Ver_Pos=Dimmax;
Endif;
DimMax=Dimmax+1;
DimLast=TabDim(vCubeName,DimMax);
End;
DimMax=DimMax-1;
#Set all dims in cube
Counter=1;
While(Tabdim(vCubeName,counter)@<>' ');
If(Counter=1);
if(counter=Bud_ver_pos);
Dim1s=pSourceElement;
Dim1t=pTargetElement;
Else;
Dim1s=Tabdim(vCubeName,counter);
Dim1t=Tabdim(vCubeName,Counter);
Endif;
Endif;
If(Counter=2);
if(counter=Bud_ver_pos);
Dim2s=pSourceElement;
Dim2t=pTargetElement;
Else;
Dim2s=Tabdim(vCubeName,counter);
Dim2t=Tabdim(vCubeName,Counter);
Endif;
Endif;
If(Counter=3);
if(counter=Bud_ver_pos);
Dim3s=pSourceElement;
Dim3t=pTargetElement;
Else;
Dim3s=Tabdim(vCubeName,counter);
Dim3t=Tabdim(vCubeName,Counter);
Endif;
Endif;
If(Counter=4);
if(counter=Bud_ver_pos);
Dim4s=pSourceElement;
Dim4t=pTargetElement;
Else;
Dim4s=Tabdim(vCubeName,counter);
Dim4t=Tabdim(vCubeName,Counter);
Endif;
Endif;
If(Counter=5);
if(counter=Bud_ver_pos);
Dim5s=pSourceElement;
Dim5t=pTargetElement;
Else;
Dim5s=Tabdim(vCubeName,counter);
Dim5t=Tabdim(vCubeName,Counter);
Endif;
Endif;
If(Counter=6);
if(counter=Bud_ver_pos);
Dim6s=pSourceElement;
Dim6t=pTargetElement;
Else;
Dim6s=Tabdim(vCubeName,counter);
Dim6t=Tabdim(vCubeName,Counter);
Endif;
Endif;
If(Counter=7);
if(counter=Bud_ver_pos);
Dim7s=pSourceElement;
Dim7t=pTargetElement;
Else;
Dim7s=Tabdim(vCubeName,counter);
Dim7t=Tabdim(vCubeName,Counter);
Endif;
Endif;
If(Counter=8);
if(counter=Bud_ver_pos);
Dim8s=pSourceElement;
Dim8t=pTargetElement;
Else;
Dim8s=Tabdim(vCubeName,counter);
Dim8t=Tabdim(vCubeName,Counter);
Endif;
Endif;
Counter=Counter+1;
End;
DatasourceNameforServer=vCubeName;
datasourcetype= 'VIEW';
datasourcecubeview=vSourceViewName;
ASCIIOutput('C:\Log',Dim1s,Dim2s,Dim3s,Dim4s,Dim5s,Dim1t,Dim2t,Dim3t,Dim4t,Dim5t);
Code: Select all
#****Begin: Generated Statements***
#****End: Generated Statements****
If(Dimmax=3);
CellputN(NValue,vCubeName,Dim1t,Dim2t,Dim3t);
ElseIf(Dimmax=4);
CellputN(NValue,vCubeName,Dim1t,Dim2t,Dim3t,Dim4t);
ElseIf(Dimmax=5);
CellputN(NValue,vCubeName,Dim1t,Dim2t,Dim3t,Dim4t,Dim5t);
Endif;
-
- MVP
- Posts: 3651
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Copying Data from 1 Element to Another via TI
If you want to leverage a TI library that includes copying from one dimension element to another for any cube you should look at http://bedrocktm1.org/
-
- Posts: 101
- Joined: Thu Oct 20, 2011 6:53 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2007
- Location: London, UK
Re: Copying Data from 1 Element to Another via TI
Wim,Wim Gielis wrote: Before diving into the code, a few words on the benefits of this process. The process allows you to copy text data.Wim
first of all, many thanks your post has saved me a lot of time and effort trying to write a generic TI to copy from one version to another. I have set up a TI using your code and it works very well apart from it fails to copy the text data. The process completes with minor errors around the text values such as "Cannot convert field number 7, value "Firm" to a real number".
In the variables tab the variable type for "Value" is Numeric. I am using your code in the Data tab:
Code: Select all
IF(CELLISUPDATEABLE(vCubeName,vCurrency,vRegion,vProject,pTargetElement,vMeasure)=1);
IF(VALUE_IS_STRING=0);
CELLPUTN(NValue,vCubeName,vCurrency,vRegion,vProject,pTargetElement,vMeasure);
ELSE;
CELLPUTS(SValue,vCubeName,vCurrency,vRegion,vProject,pTargetElement,vMeasure);
ENDIF;
ENDIF;
Many thanks
-
- 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: Copying Data from 1 Element to Another via TI
Hello, try to set all variables to the String data type.
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
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