Page 1 of 1
Replicate dimension hierarchy
Posted: Mon Nov 21, 2011 12:22 pm
by ADW
Hi,
I have a hierarchy containing two consolidations/alternate hierarchies.
Region
--Children of Region
&
Activity
--Children of Activity
(Children of Region are the same as Children of Activity, therefore Region = Activity)
These are in the same dimension and I want to create a TI process to split them into two (new) separate dimensions. I have had a go (using the wizard which I've never done before) and I can get the elements to come across but not the hierarchy structure.
Does anyone know the correct way to do this?
Re: Replicate dimension hierarchy
Posted: Mon Nov 21, 2011 4:35 pm
by David Usherwood
TM1 dimensions are essentially a set of parent child pairs (many to many, because of multiple hierarchies). We've used that in the past to 'replicate' dimensions between servers, via a relational store.
If you make your data source to be the All subset of the dimension and iterate through the children, you should be able to create the content you are after.
Re: Replicate dimension hierarchy
Posted: Mon Nov 21, 2011 4:50 pm
by Kaveenga
Hi ADW,
I've included the code of a TM1 process that takes in a "dimension" and a "parent element" and then spits out an CSV file with the
1.Child,
2.Child descrip,
3.Child Type,
4.Child Weight,
5.Parent and
6.Parent Descrip
You can then process the CSV files to create the new dimensions.
Have a look at
bedrocktm1.org for pre-developed scripts for creating dimensions from CSV files.
HTH
Kavee
Code: Select all
Parameters
pDim
pParEl
### START: Declare common variables ###
cUser = TM1User();
cProcess = 'Utils_ExportToFile_Child_Parent_Struc_of_Dim';
cTimeStamp = TimSt( Now, '\Y\m\d\h\i\s' );
cDebugFile = GetProcessErrorFileDirectory | cProcess | '.' | cTimeStamp | '.' | cUser;
sDebugFile = cDebugFile | 'Prolog.debug';
cSub = cProcess | ' ' | cUser;
cFileName = cSub | '.' | pDim | '.' | pParEl | '.' | cTimeStamp | '.csv';
cFilePath = 'D:\TM1\TM1General\Sandbox\' ;
cFileNameAndPath = cFilePath | cFileName;
### END: Declare common variables ###
### START: Validate Parameters ###
nErrors = 0;
# Validate dimension
If( Trim( pDim ) @= '' );
nErrors = 1;
sMessage = 'No dimension specified';
AsciiOutput( sDebugFile, sMessage );
ItemReject( sMessage );
EndIf;
If( DimensionExists( pDim ) = 0 );
nErrors = 1;
sMessage = 'Invalid dimension: ' | pDim;
AsciiOutput( sDebugFile, sMessage );
ItemReject( sMessage );
EndIf;
# Validate Parent Element
If( Trim( pParEl ) @= '' );
nErrors = 1;
sMessage = 'No parent element specified';
If( pDebug >= 1 );
AsciiOutput( sDebugFile, sMessage );
EndIf;
ItemReject( sMessage );
EndIf;
If( DIMIX( pDim, pParEl ) = 0 );
nErrors = 1;
sMessage = 'Element ' | pParEl | ' does not exist in ' | pDim;
If( pDebug >= 1 );
AsciiOutput( sDebugFile, sMessage );
EndIf;
ItemReject( sMessage );
EndIf;
### END: Validate Parameters ###
### Create temporary MDX subset expanding the parent and children
vMDX = '{TM1DRILLDOWNMEMBER( {[' | pDim | '].[' | pParEl | ']}, ALL, RECURSIVE )}';
IF( SubsetExists(pDim,cSub) = 1 ) ;
SubsetDestroy(pDim,cSub) ;
ENDIF ;
SubsetCreatebyMDX(cSub, vMDX);
### Output header row for file
AsciiOutput ( cFileNameAndPath
, 'Child'
, 'Child Descrip'
, 'Child Type'
, 'Child Weight'
, 'Parent'
, 'Parent Descrip');
### Loop through the Subset
vNoOfElements = SubsetGetSize( pDim, cSub );
i = 1;
WHILE( i <= vNoOfElements);
# Get the element in order
vElement = SubsetGetElementName( pDim, cSub, i );
### Check if element is a Consolidation
### (the very first element of the subset should be a C level element}
IF(DTYPE( pDim, vElement ) @= 'C' );
### If a C element get the number of children and description;
vNoOfChildren = ELCOMPN( pDim, vElement );
vElDescrip = ATTRS( pDim, vElement, 'Descrip' );
c = 1;
### Loop through Children and output to file.
WHILE( c <= vNoOfChildren);
vChild = ELCOMP( pDim, vElement, c );
vChildDescrip = ATTRS( pDim, vElement, 'Descrip' );
AsciiOutput ( cFileNameAndPath
, vChild
, vChildDescrip
, DTYPE( pDim, vChild )
, NumberToString( ELWEIGHT( pDim, vElement, vChild ))
, vElement
, vElDescrip );
c = c + 1;
END;
ENDIF;
i = i + 1;
END;