Generic Ti for Multiple Cubes - Archive Versions

Post Reply
chewza
Posts: 146
Joined: Tue Aug 17, 2010 11:51 am
OLAP Product: TM1
Version: 9.5
Excel Version: 7

Generic Ti for Multiple Cubes - Archive Versions

Post by chewza »

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
chewza
Posts: 146
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

Post by chewza »

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
declanr
MVP
Posts: 1815
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

Post by declanr »

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:
  • 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.
I always keep my version dimension as the first dim in all cubes. If your version dimension appears in different places you will need to also define the position of that and change the relevant variable to the target version as you go.

Plus the obvious stuff of checking for whether the measure dimension is a string or numeric element etc etc etc.
Declan Rodger
Wim Gielis
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: Generic Ti for Multiple Cubes - Archive Versions

Post by Wim Gielis »

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
Adam
Posts: 94
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

Post by Adam »

Hi Chris,
Wim Gielis wrote: Fri Mar 17, 2023 9:14 pm Maybe Bedrock is suited for this exercise ?
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;
Take care.
Adam
chewza
Posts: 146
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

Post by chewza »

Hi Guys

A big thank you to all of you. A huge help, and answered my question perfectly!

Regards
Chris
chewza
Posts: 146
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

Post by chewza »

@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?
Wim Gielis
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: Generic Ti for Multiple Cubes - Archive Versionsm

Post by Wim Gielis »

chewza wrote: Mon Mar 20, 2023 3:47 pmHi 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?
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
chewza
Posts: 146
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

Post by chewza »

beginning to think this is not possible on my last point. I would need to user arrays which are not supported
burnstripe
Regular Participant
Posts: 197
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

Post by burnstripe »

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

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;
chewza
Posts: 146
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

Post by chewza »

Thanks @burnstripe!
Post Reply