Generic Ti for Multiple Cubes - Archive Versions
-
- Regular Participant
- Posts: 156
- Joined: Tue Aug 17, 2010 11:51 am
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 7
Generic Ti for Multiple Cubes - Archive Versions
Hi Guys
Bit of a brain teaser, and not sure if its possible. Best to Illustrate by way of Example:
Cube1 dims = Year, Month, Version, Product, Measure
Cube2 dims = Year, Month, Version, Product, Customer, Measure
My plan:
1) Generic Master Ti to call generic "slave" Ti and pass in Cube name and required source and target version (eg source = Ver1, target = Ver1_Archive)
2) Slave T1, creates view (based on cube parameter) and creates a subset only for version dim and assigns to view
3) The resultant view will therefore contain "all" for all other dimensions
4) Assign this view as the source for the Ti
5) With this as the source view, CellPutN against the target version.
So the Master Ti will have something like this:
ExecuteProcess('Slave T1, 'pCube', 'Cube1', 'pSourceVer', 'Ver1', 'pDestVer', 'Ver1_Archive' );
ExecuteProcess('Slave T1, 'pCube', 'Cube2', 'pSourceVer', 'Ver1', 'pDestVer', 'Ver1_Archive' );
The problem:
The two cubes have a different number of dimensions. Creating the source is not a problem, but with a variable number of dimensions, can't see how I can use the CellPutN. There will be different variables coming through.
Can anyone think of a way around this?
Many thanks!
Chris
Bit of a brain teaser, and not sure if its possible. Best to Illustrate by way of Example:
Cube1 dims = Year, Month, Version, Product, Measure
Cube2 dims = Year, Month, Version, Product, Customer, Measure
My plan:
1) Generic Master Ti to call generic "slave" Ti and pass in Cube name and required source and target version (eg source = Ver1, target = Ver1_Archive)
2) Slave T1, creates view (based on cube parameter) and creates a subset only for version dim and assigns to view
3) The resultant view will therefore contain "all" for all other dimensions
4) Assign this view as the source for the Ti
5) With this as the source view, CellPutN against the target version.
So the Master Ti will have something like this:
ExecuteProcess('Slave T1, 'pCube', 'Cube1', 'pSourceVer', 'Ver1', 'pDestVer', 'Ver1_Archive' );
ExecuteProcess('Slave T1, 'pCube', 'Cube2', 'pSourceVer', 'Ver1', 'pDestVer', 'Ver1_Archive' );
The problem:
The two cubes have a different number of dimensions. Creating the source is not a problem, but with a variable number of dimensions, can't see how I can use the CellPutN. There will be different variables coming through.
Can anyone think of a way around this?
Many thanks!
Chris
-
- Regular Participant
- Posts: 156
- Joined: Tue Aug 17, 2010 11:51 am
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 7
Re: Generic Ti for Multiple Cubes - Archive Versions
ps Different number of variables - so view applied to Cube 1 will return 5 variables when used as a data source, and 6 variables against cube 2
-
- MVP
- Posts: 1822
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Generic Ti for Multiple Cubes - Archive Versions
This is exactly how I set up version archiving processes in all of my models.
You've described the master process perfectly.
So for the slave process you need to:
Plus the obvious stuff of checking for whether the measure dimension is a string or numeric element etc etc etc.
You've described the master process perfectly.
So for the slave process you need to:
- Check how many dimensions are in the Cube on the prolog
- Create your process using a dummy source (something with at least as many variables as your biggest cube has dimensions)
- On the data tab have an IF, ELSEIF, ELSEIF etc. statement that says IF cube has 4 dimensions then CellPutN ( vCol5, pCubeName, pDestVer, vCol2,vCol3, vCol4 );... etc.
Plus the obvious stuff of checking for whether the measure dimension is a string or numeric element etc etc etc.
Declan Rodger
-
- MVP
- Posts: 3194
- 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: Generic Ti for Multiple Cubes - Archive Versions
Maybe Bedrock is suited for this exercise ?
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: 118
- Joined: Wed Apr 03, 2019 12:10 am
- OLAP Product: IBM PA
- Version: 2.0.9.x
- Excel Version: Microsoft 365 x64
Re: Generic Ti for Multiple Cubes - Archive Versions
Hi Chris,
I'll help you, this is adapted from what we've used in production for multiple years. Put in some extra # comments for you. Let us know you make out.
This.
I'll help you, this is adapted from what we've used in production for multiple years. Put in some extra # comments for you. Let us know you make out.
Code: Select all
# Version dimension
sVersionDim = 'Version';
# Iterate through all cubes to find ones with sVersionDim
nCurrentCube = 1;
nCubesToCopy = DimSiz('}Cubes');
While(nCurrentCube <= nCubesToCopy);
# Get name of cube
sCurrentCube = DimNm('}Cubes', nCurrentCube);
# Only check non-control cubes
If(SubSt(sCurrentCube, 1, 1) @<> '}');
# Iterate through all dimensions of the cube to see if it has sVersionDim
nCurrentCubeCurrentDimension = 1;
nCurrentCubeDimensions = CubeDimensionCountGet(sCurrentCube);
While(nCurrentCubeCurrentDimension <= nCurrentCubeDimensions);
# Get name of Nth dimension of cube
sCurrentCubeCurrentDimension = TabDim(sCurrentCube, nCurrentCubeCurrentDimension);
# If the Nth dimension is sVersionDim: run copy process
If(sCurrentCubeCurrentDimension @= sVersionDim);
ExecuteProcess('}bedrock.cube.data.copy',
'pCube', sCurrentCube,
'pDimDelim','~',
'pEleStartDelim', '@',
'pCubeLogging', 1,
'pEleMapping', Expand('%sVersionDim%@%pSourceVer%->%pDestVer%'));
# Exit while loop for current cube after sVersionDim is found and copy process is run
Break;
EndIf;
nCurrentCubeCurrentDimension = nCurrentCubeCurrentDimension + 1;
End;
EndIf;
nCurrentCube = nCurrentCube + 1;
End;
-
- Regular Participant
- Posts: 156
- Joined: Tue Aug 17, 2010 11:51 am
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 7
Re: Generic Ti for Multiple Cubes - Archive Versions
Hi Guys
A big thank you to all of you. A huge help, and answered my question perfectly!
Regards
Chris
A big thank you to all of you. A huge help, and answered my question perfectly!
Regards
Chris
-
- Regular Participant
- Posts: 156
- Joined: Tue Aug 17, 2010 11:51 am
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 7
Re: Generic Ti for Multiple Cubes - Archive Versions
@declanr
Hi there. Thx for your help. The only problem here is that my Version dimension is not always in the same place, so not sure how it is possible to determine the "position" of pDestVer in my CellPutN. Any thoughts?
Hi there. Thx for your help. The only problem here is that my Version dimension is not always in the same place, so not sure how it is possible to determine the "position" of pDestVer in my CellPutN. Any thoughts?
-
- MVP
- Posts: 3194
- 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: Generic Ti for Multiple Cubes - Archive Versionsm
You cannot do that. A set of IF statements depending on the tabdim count is needed.
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: 156
- Joined: Tue Aug 17, 2010 11:51 am
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 7
Re: Generic Ti for Multiple Cubes - Archive Versions
beginning to think this is not possible on my last point. I would need to user arrays which are not supported
-
- Regular Participant
- Posts: 218
- Joined: Wed May 06, 2020 2:58 pm
- OLAP Product: Planning Analytics
- Version: 2.0.9
- Excel Version: 2016
Re: Generic Ti for Multiple Cubes - Archive Versions
It's possible as wim alludes to you'd need an if statement
I presume the datasource variables are named something like this v1, v2...
In the data tab, instead of renaming the variable to pdestver, redefine what v1 or whatever the variable is instead, then the cellputn is straightforward. Here's an illustration
I presume the datasource variables are named something like this v1, v2...
In the data tab, instead of renaming the variable to pdestver, redefine what v1 or whatever the variable is instead, then the cellputn is straightforward. Here's an illustration
Code: Select all
Prolog tab
# number of dimensions in cube
ndimcount=3;
# location of the version dimension
# this can be determined with tabdim and a while loop
ndestver =1;
Data tab
# assign target version to the variable
If( ndestver = 1); v1 = pdestver;
Elseif( ndestver = 2); v2 = pdestver;
Elseif( ndestver = 3); v3 = pdestver;
....
....
Endif;
# push string or numeric value to destinate version
If(value_is_string = 0);
If(ndimcount=2); cellputn(nvalue,v1,v2);
ElseIf(ndimcount=3); cellputn(nvalue,v1,v2,v3);
...
Endif;
Else;
If(ndimcount=2); cellputs(svalue,v1,v2);
ElseIf(ndimcount=3); cellputs(svalue,v1,v2,v3);
...
Endif;
Endif;
-
- Regular Participant
- Posts: 156
- Joined: Tue Aug 17, 2010 11:51 am
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 7
Re: Generic Ti for Multiple Cubes - Archive Versions
Thanks @burnstripe!