Replicate dimension hierarchy

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

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

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