How export a dimension to SQL

Product:

Planning Analytics 2.0.9.19

TM1_version=TM1-AW64-ML-RTM-11.0.919.10-0
TM1_name=IBM Cognos TM1

Issue:

How export dim values to SQL?

Solution:

Create a TM1 TI process that have the dimension as a data source. Set the variable name to account, and as string. On the TM1 server create a ODBC connection to the SQL database.

Enter code similar to this:

#Section Prolog
sDimension = 'plan_chart_of_accounts' ;

sSource = 'OdbcDatabasename' ;
sClient = 'sql login user' ;
sPassword = 'sql login password' ;
sUseUnicodeODBC = 1 ;

ODBCOpenEx(sSource, sClient, sPassword, sUseUnicodeODBC) ;

#Section Metadata

#Section Data
sSQL = '';

CountofParent = ElParN( sDimension , Account);
ElementLevel = ElLev( sDimension , Account);
ParentCounter = 0;

WHILE (ParentCounter <= CountofParent);
ParentElement = ElPar( sDimension, Account ,ParentCounter);

sSQL = Expand('INSERT INTO dbo.AccountTable (Account, parentelement, countofparent, elementlevel) VALUES (''%Account%'',''%ParentElement%'',''%CountofParent%'', ''%ElementLevel%'')');

ODBCOutPut( sSource , sSQL );
ASCIIOutput( 'd:\temp\debug.txt', Account, ParentElement , NumberToString( CountofParent ) , NumberToString( ElementLevel ) );

ParentCounter = ParentCounter+1;
END;

#Section Epilog