Not too long ago I was part of an effort help get a dynamic allocation solution off the ground. It was struggling because the data source was a MDX view, and the client was changing this view. Simpling linking the TI process to the MDX view wound not guaranteed the process would work because changing the dimension order causes the TI process to fail
I manage to solve this problem by writing a process that converts any MDX view to a native view. There is an assumption that the MDX views are simple queries and do not have any nesting or subqueries. Here are key snippets in my processes
Setup define some variables. 1st setup a temp variable for every dimension. These variables help prevent adding the same element to a subset by skipping the element if it used in the previous iteration.
We validate the cube target view parameter has a value. The cube target view is created with the value of the parameter. Note, if the view already exist then it is deleted and recreated.
#### Setup Target View ####
IF (long(pTargetView) > 0);
If( ViewExists( pCube, pTargetView ) = 1 );
ViewDestroy(pCube,pTargetView );
ENDIF;
ViewCreate( pCube, pTargetView , pTargetViewTemp);
Else;
nErrors = 1;
sMessage = 'Target view is not defined';
sDataSourceType = 'NULL';
ItemReject( sMessage );
ENDIF;
sTemp1 = '';
sTemp2 = '';
sTemp3 = '';
sTemp4 = '';
sTemp5 = '';
2nd We get the dimension name for the cube that has a MDX View.. I am going up to 5 dimensions in this example. You can increase it to any number you need.
sDim1 = TabDim( pCube, 1 );
sDim2 = TabDim( pCube, 2 );
sDim3 = TabDim( pCube, 3 );
sDim4 = TabDim( pCube, 4 );
sDim5 = TabDim( pCube, 5 );
The TI function ViewMDXGet is used to get the string value of the MDX query in the cube view
sMDXView = ViewMDXGet (pCube, pSourceView);
Next, we get the position of each dimension in the MDX view. The scan function can search for the dimension name inside the text value of sMDXView
nDimPosition1 = SCAN( sDim1, sMDXView );
nDimPosition2 = SCAN( sDim2, sMDXView );
nDimPosition3 = SCAN( sDim3, sMDXView );
nDimPosition4 = SCAN( sDim4, sMDXView );
nDimPosition5 = SCAN( sDim5, sMDXView );
Now we are going to sort dimension variables and have them match the order of the MDX view. we am going to make several passes because my algorithm only moves a dimension 1 position at a time. Below is logic for 5 dimension, and you can follow the pattern and go up to the number of dimensions you need. Note my while loop starts at 2 because every cube has to have at least 2 dimensions
nDimCubeCounter = CubeDimensionCountGet( pCube );
nDimPositionTemp = 0;
nDimNameTemp = '';
nLooper = 2;
While ( nLooper <= nDimCubeCounter);
#### Sort Dim 1 ######
IF (long(trim(sDim2)) > 0);
IF (nDimPosition1 > nDimPosition2);
nDimPositionTemp = nDimPosition2;
nDimNameTemp = sDim2;
nDimPosition2 = nDimPosition1;
sDim2 = sDim1;
nDimPosition1 = nDimPositionTemp;
sDim1 = nDimNameTemp;
ENDIF;
ENDIF;
#### Sort Dim 2 ######
IF (long(trim(sDim3)) > 0);
IF (nDimPosition2 > nDimPosition3);
nDimPositionTemp = nDimPosition3;
nDimNameTemp = sDim3;
nDimPosition3 = nDimPosition2;
sDim3 = sDim2;
nDimPosition2 = nDimPositionTemp;
sDim2 = nDimNameTemp;
ENDIF;
ENDIF;
#### Sort Dim 3 ######
IF (long(trim(sDim4)) > 0);
IF (nDimPosition3 > nDimPosition4);
nDimPositionTemp = nDimPosition4;
nDimNameTemp = sDim4;
nDimPosition4 = nDimPosition3;
sDim4 = sDim3;
nDimPosition3 = nDimPositionTemp;
sDim3 = nDimNameTemp;
ENDIF;
ENDIF;
#### Sort Dim 4 ######
IF (long(trim(sDim5)) > 0);
IF (nDimPosition4 > nDimPosition5);
nDimPositionTemp = nDimPosition5;
nDimNameTemp = sDim5;
nDimPosition5 = nDimPosition4;
sDim5 = sDim4;
nDimPosition4 = nDimPositionTemp;
sDim4 = nDimNameTemp;
ENDIF;
ENDIF;
#### Sort Dim 5 ######
IF (long(trim(sDim6)) > 0);
IF (nDimPosition5 > nDimPosition6);
nDimPositionTemp = nDimPosition6;
nDimNameTemp = sDim6;
nDimPosition6 = nDimPosition5;
sDim6 = sDim5;
nDimPosition5 = nDimPositionTemp;
sDim5 = nDimNameTemp;
ENDIF;
ENDIF;
nLooper = nLooper + 1;
END;
Now we create a subset for all of the dimensions in the cube. Note this deletes all of the elements in the target subset if it exists otherwise, it creates a new target subset
Now create the subsets for each dimension
IF (LONG(sDim1) > 0);
IF (SubsetExists(sDim1, pTargetView) = 1);
SubsetDeleteAllElements (sDim1,pTargetView );
ELSE;
SubsetCreate(sDim1,pTargetView,pTargetViewTemp );
ENDIF;
ENDIF;
IF (LONG(sDim2) > 0);
IF (SubsetExists(sDim2, pTargetView) = 1);
SubsetDeleteAllElements (sDim2,pTargetView );
ELSE;
SubsetCreate(sDim2,pTargetView,pTargetViewTemp );
ENDIF;
ENDIF;
IF (LONG(sDim3) > 0);
IF (SubsetExists(sDim3, pTargetView) = 1);
SubsetDeleteAllElements (sDim3,pTargetView );
ELSE;
SubsetCreate(sDim3,pTargetView,pTargetViewTemp );
ENDIF;
ENDIF;
IF (LONG(sDim4) > 0);
IF (SubsetExists(sDim4, pTargetView) = 1);
SubsetDeleteAllElements (sDim4,pTargetView );
ELSE;
SubsetCreate(sDim4,pTargetView,pTargetViewTemp );
ENDIF;
ENDIF;
IF (LONG(sDim5) > 0);
IF (SubsetExists(sDim5, pTargetView) = 1);
SubsetDeleteAllElements (sDim5,pTargetView );
ELSE;
SubsetCreate(sDim5,pTargetView,pTargetViewTemp );
ENDIF;
ENDIF;
Now in the metadata section of the process we insert elements to the target subset. Below is code to add elements to the first-dimension subset. This can easily be modified to handle more dimensions by changing the number in the cube
Let's look at this section of the cube
- Checks to see if the dimension is defined.
- It checks the temp variable to make sure it did not already process the element for the subset
- Next if statement is another check to verify that the element does not exist in the subset
- Next section inserts the new element in the subset
- Note pForce0LevelElement uses the 0 level elements of the consolidated element when it is turned on (1)
- consolidated elements can be inserted in the subset when pForce0LevelElement is set to 0
- If the element is consolidated and pForce0LevelElement is set to 0 then the element is inserted into the target subset
- When element is consolidated or pForce0LevelElement is set to 1 then
- A MDX statement is used to get all the 0 level children of the consolidated element. This same MDX statement is used for 0 level elements
- Next section makes a temporary subset and copies all of the existing elements from the target subset to the temporary subset. Then it unions the new elements and temporary subset to create the new target subset
### Dimension 1
IF (LONG(sDim1) > 0);
#Do not process same element
IF (v1 @<> sTemp1);
IF (SubsetElementExists (sDim1, pTargetView, v1) = 0);
### Leaf Level Element
IF( DTYPE( sDim1, v1) @<> 'C' % pForce0LevelElement = 0 );
SubsetElementInsert( sDim1, pTargetView, v1, 1 );
If ( pDebug >= 1 );
AsciiOutput( sFilePath_NameMeta, 'Insert element ' | v1 | ' into dimension ' | sDim1 );
ENDIF;
ELSE;
### Consolidated Element
sMDX = '{TM1FILTERBYLEVEL({Descendants({['|sDim1|'].['|v1|']})}, 0)}';
IF (SubsetGetSize(sDim1, pTargetView) > 0);
### Setup temporary subset
IF (SubsetExists (sDim1, pTargetView | '_Temp') > 0);
SubsetDeleteAllElements (sDim1,pTargetView | '_Temp');
ELSE;
SubsetCreate(sDim1,pTargetView | '_Temp' ,pTargetViewTemp );
ENDIF;
sMDXTemp = '{TM1SubsetToSet(['| sDim1 |'],"' | pTargetView | '")}';
SubSetMDXSet(sDim1,pTargetView | '_Temp',sMDXTemp);
SubSetMDXSet(sDim1,pTargetView | '_Temp','');
sMDX = '{UNION({TM1SubsetToSet(['| sDim1 |'],"' | pTargetView | '_Temp")},' | sMDX | ')}';
SubsetDeleteAllElements (sDim1,pTargetView );
ENDIF;
If ( pDebug >= 1 );
AsciiOutput( sFilePath_NameMeta, 'MDX Statement ' | sMDX | ' created for dimension ' | sDim1 );
EndIf;
SubSetMDXSet(sDim1,pTargetView,sMDX);
SubSetMDXSet(sDim1,pTargetView,'');
###Cleanup
sMDXTemp = '';
# Remove temp subset if exist
IF (SubsetExists( sDim1, pTargetView | '_Temp' ) = 1);
SubsetDestroy( sDim1, pTargetView | '_Temp' );
ENDIF;
### Element Type check
ENDIF;
### Subset Element check
ENDIF;
### Dimension check
sTemp1 = v1;
ENDIF;
ENDIF;
In the Epilog section of the process assign the subsets to the cube
IF (LONG(trim(sDim1)) > 0);
ViewSubsetAssign( pCube, pTargetView, sDim1, pTargetView );
ENDIF;
IF (LONG(trim(sDim2)) > 0);
ViewSubsetAssign( pCube, pTargetView, sDim2, pTargetView );
ENDIF;
IF (LONG(trim(sDim3)) > 0);
ViewSubsetAssign( pCube, pTargetView, sDim3, pTargetView );
ENDIF;
IF (LONG(trim(sDim4)) > 0);
ViewSubsetAssign( pCube, pTargetView, sDim4, pTargetView );
ENDIF;
IF (LONG(trim(sDim5)) > 0);
ViewSubsetAssign( pCube, pTargetView, sDim5, pTargetView );
ENDIF;
This works really well when your data source is a cube view from a PAW book that the users manipulate. This process creates a native cube view with native dimension subsets that I used in creating views for calculating dynamic allocation. You may need to use this in other ways.