Export TM1 Dimension to file

[sdarpa]

Does anyone know how to write the TM1 TI Process that will export a dimension to a text file in the same exact format as when you right-click on the dimension and choose "Export Dimension..."? There will be dimensions that have multiple hierarchies mind you. I have been able to export them using a TI Process to a flat file (Bedrock's dimension export scripting), but it is in a different format and then the person that needs the dimension export for a 3rd party BI system will have to redo all his existing scripting to bring it into that BI software. I'd like to avoid putting that burden on him. Thank you.

[declanr]

Does anyone know how to write the TM1 TI Process that will export a dimension to a text file in the same exact format as when you right-click on the dimension and choose "Export Dimension..."? There will be dimensions that have multiple hierarchies mind you. I have been able to export them using a TI Process to a flat file (Bedrock's dimension export scripting), but it is in a different format and then the person that needs the dimension export for a 3rd party BI system will have to redo all his existing scripting to bring it into that BI software. I'd like to avoid putting that burden on him. Thank you.

I would question whether this is the best way to get the dimension data into an external tool but if it is what you wish to go with then the following should just about get you there (I have just written it now without testing so it may have typos etc but should explain the basic premise.)

Prolog:

Code: Select all

sDim = <dimension_name>;
sOutputFile = 'D:\' | sDim | '.csv';

Data:

Code: Select all

If ( DTYPE ( sDim, v1 ) @= 'C' );
	AsciiOutput ( sOutputFile, 'C,' | v1 );
	iCount = 1;
	iMax = ElCompN ( sDim, v1 );
	While ( iCount <= iMax );
		sChild = ElComp ( sDim, v1, iCount );
		sWeight = NumberToStringEx ( ElWeight ( sDim, v1, sChild ), '#.0,#########', '.',',' );
		AsciiOutput ( sOutputFile, ',' | sChild | ',' | sWeight );
		iCount = iCount + 1;
	End;
ElseIf ( DTYPE ( sDim, v1 ) @='N' );
	AsciiOutput ( sOutputFile, 'N,' | v1 );
ElseIf ( DTYPE ( sDim, v1 ) @='S' );
	AsciiOutput ( sOutputFile, 'S,' | v1 );
EndIf;

You will need to have the dimension in your data source with the all subset, you could also set this in the prolog and just have a parameter to define the dimension so that 1 TI will work throughout your (and any) model.

HTH

[sdarpa]

Thanks a bunch!!!! It worked with just a few little tweaks.

[Wim Gielis]

Thanks a bunch!!!! It worked with just a few little tweaks.

In the spirit of this forum, sharing knowledge and working solutions, please can you post up your final version of the code?
And/or the changes you did to Rodger's code. Thanks.

[sdarpa]

Sure. I didn't need the file name in the output and the | signs in the Data tab were not helping. Prologue remained the same.

DATA tab:

Code: Select all

If ( DTYPE ( sDim, v1 ) @= 'C' );
	AsciiOutput ( sOutputFile, 'C', v1 );
	iCount = 1;
	iMax = ElCompN ( sDim, v1 );
	While ( iCount <= iMax );
		sChild = ElComp ( sDim, v1, iCount );
		sWeight = NumberToString ( ElWeight ( sDim, v1, sChild ) );
		AsciiOutput ( sOutputFile, '',  sChild , sWeight );
		iCount = iCount + 1;
	End;
ElseIf ( DTYPE ( sDim, v1 ) @='N' );
	AsciiOutput ( sOutputFile, 'N', v1 ); ElseIf ( DTYPE ( sDim, v1 ) @='S' );
	AsciiOutput ( sOutputFile, 'S', v1 ); EndIf;

[Wim Gielis]

The community thanks you

[rmackenzie]

This could benefit only so slightly from reducing the amount of IF...ELSEIF logic:

Code: Select all

sElementType = DTYPE ( sDim, v1 );
# always output the element and its type
AsciiOutput ( sOutputFile, sElementType , v1 );
# cycle through children if it is a consolidated element
IF ( sElementType @= 'C' );
   iCount = 1;
   iMax = ElCompN ( sDim, v1 );
   While ( iCount <= iMax );
      sChild = ElComp ( sDim, v1, iCount );
      sWeight = NumberToString ( ElWeight ( sDim, v1, sChild ) );
      AsciiOutput ( sOutputFile, '',  sChild , sWeight );
      iCount = iCount + 1;
   End;
EndIf;