Page 1 of 1

Generic Ti for Multiple Cubes - Archive Versions

Posted: Fri Mar 17, 2023 5:28 pm
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

Re: Generic Ti for Multiple Cubes - Archive Versions

Posted: Fri Mar 17, 2023 5:30 pm
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

Re: Generic Ti for Multiple Cubes - Archive Versions

Posted: Fri Mar 17, 2023 6:35 pm
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.

Re: Generic Ti for Multiple Cubes - Archive Versions

Posted: Fri Mar 17, 2023 9:14 pm
by Wim Gielis
Maybe Bedrock is suited for this exercise ?

Re: Generic Ti for Multiple Cubes - Archive Versions

Posted: Sat Mar 18, 2023 11:38 pm
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;

Re: Generic Ti for Multiple Cubes - Archive Versions

Posted: Mon Mar 20, 2023 11:15 am
by chewza
Hi Guys

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

Regards
Chris

Re: Generic Ti for Multiple Cubes - Archive Versions

Posted: Mon Mar 20, 2023 3:47 pm
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?

Re: Generic Ti for Multiple Cubes - Archive Versionsm

Posted: Mon Mar 20, 2023 3:57 pm
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.

Re: Generic Ti for Multiple Cubes - Archive Versions

Posted: Mon Mar 20, 2023 4:01 pm
by chewza
beginning to think this is not possible on my last point. I would need to user arrays which are not supported

Re: Generic Ti for Multiple Cubes - Archive Versions

Posted: Mon Mar 20, 2023 6:00 pm
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;

Re: Generic Ti for Multiple Cubes - Archive Versions

Posted: Mon Mar 20, 2023 8:42 pm
by chewza
Thanks @burnstripe!