Moving Data from TM1 to SQL DB

[tkingcka]

I have been working on trying to move data from TM1 to a SQL server database. The process runs successfully, however the only data that shows up in the SQL table are numerics. I cannot get any string data to show up in the SQL table.

***********************************************
Here is my Prolog TAB

DSN= 'tm1_to_sQL';

UserID = 'IDIDIDIDIDIDIDID';
Pswe = 'AAAAAAAAAAAA';


ODBCOPEN(DSN, UserID, Pswd);
************************************************



************************************************************************************************************************************************************************
Here is my Advanced Data Tab.

IF(Appian_m @='Region Code');

ODBCOutput(DSN, 'INSERT INTO dbo.EV_APPIAN ([dimension_1_number], [exportvalue_region]) VALUES ( ''' | APC_Line | ''' , ''' | Value | ''' ) ' );

ENDIF;
**************************************************************************************************************************************************************************

The only thing I can think of is there must be an issue with the syntax. It is only a 2 dimensional cube and there is no numeric data in the measure dimension. The data source shows the following element: Line = 1; Measure = Code; Value = ABC The line value shows up, but the measuer value remains blank, as it will not load. Please provide any knowledge you might have on this issue or topic. Thanks.

[Duncan P]

Could you also include a "describe" of the SQL table and a picture of the variables tab of the process so that we can see how the TM1 variables and the SQL fields are defined?

[tkingcka]

I have attached the variables tab and a view of the SQL table structure, as I don't have direct access to the SQL table. Please let me know if you need any additional infomation. Thanks for your assistance.

Variables and SQL Table.docx

(55.84 KiB) Downloaded 355 times

[Duncan P]

Could you put the SQL insert statement into a variable and ASCIIOutput the variable into a file. Make sure that you set DatasourceASCIIQuoteCharacter to '' in the prolog.

Then you will see exactly what it is sending.

Also it would be useful to see in detail what the characteristics of each field in the SQL table are.

[BrianL]

You could also try enabling log4j.logger.TM1.SQL=DEBUG to see exactly what SQL is being sent by TM1 and what the return values are.

[tkingcka]

Duncan -I will try your recommendation and see what happens. As far as the the characteristics of each field in the SQL table, I reached out to the DBA to find out and am awaiting his response.

Thanks everyone for your replies.

[tkingcka]

Duncan - I did just find out that the dataypes for each column/element is varchar(2000), null).

I haven't done your tests as of yet because I am working on another task at the moment.

[tomok]

Any time you are trying to debug what is happening in an ODBCOutput function you should do an ASCIIOutput of the string you have created for the ODBCOutput command. This way you can see the EXACT SQL statement that you are sending to the database. Most of the time is pretty obvious, like a missing apostrophe, or trying to send strings to numeric fields or numerics to string fields, etc. Then it's an easy fix. Chances are yours is an SQL syntax error.