Generic Ti for Multiple Cubes - Archive Versions

[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]

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]

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.

[Wim Gielis]

Maybe Bedrock is suited for this exercise ?

[Adam]

Hi Chris,

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;

[chewza]

Hi Guys

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

Regards
Chris

[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]

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?

You cannot do that. A set of IF statements depending on the tabdim count is needed.

[chewza]

beginning to think this is not possible on my last point. I would need to user arrays which are not supported

[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]

Thanks @burnstripe!