Page 1 of 1
Export TM1 Dimension to file
Posted: Tue Aug 27, 2013 5:28 pm
by 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.
Re: Export TM1 Dimension to file
Posted: Tue Aug 27, 2013 6:10 pm
by declanr
sdarpa wrote: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
Re: Export TM1 Dimension to file
Posted: Fri Aug 30, 2013 10:53 am
by sdarpa
Thanks a bunch!!!! It worked with just a few little tweaks.
Re: Export TM1 Dimension to file
Posted: Fri Aug 30, 2013 11:11 pm
by Wim Gielis
sdarpa wrote: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.
Re: Export TM1 Dimension to file
Posted: Sat Aug 31, 2013 1:45 pm
by 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;
Re: Export TM1 Dimension to file
Posted: Sat Aug 31, 2013 3:41 pm
by Wim Gielis
The community thanks you
Re: Export TM1 Dimension to file
Posted: Sun Sep 01, 2013 9:56 am
by 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;