Preview but get no data in Turbo Integrator (ODBC: Oracle Da

[David Lee]

Hi all,

We want to read data from an Oracle DB table and write the data into TM1 cube using Turbo Integrator. However, we can only see the table schema but no data in Preview in Preview, and cannot read any data.

Our setup procedure is as follows:
1. Setup a 64-bit ODBC System DSN in Windows ODBC Data Source Administrator
2. Create a simple TI process to select all columns from a table, as the attached image shows.
3. Only schema appear in preview. There is no data.
4. In the Data phase, no data is read from the table

We would like to know if there is anything wrong in our setup procedure. Please help us to determine the cause of the problem. Our environment information are shown below:
OS(TM1 Server and Client): Microsoft Windows 7 64-bit
TM1 Server: V9.5.2 FP1 64-bit
TM1 Client: V9.5.2 FP1 32-bit
Oracle Database: V11.2.0.1
Oracle Instant Client: V11.2.0.1 64-bit

Thanks in advance for your support!

[tomok]

The obvious answer would be that the user FINDW has access to the D_BUSINESSORG table but not any of the data in it so that no records are returned in the query. If that is not the answer then you might try adding the following parameter to the tm1s.cfg file:

SQLFetchScroll=T

That sometimes fixed Oracle/TM1 issues.

[David Lee]

Thanks tomok. We followed your advice to add "UseSQLFetchScroll=T" into tm1s.cfg. If we use "SELECT * FROM ..." to select all columns, there is still no data shown in Preview. However, if we change our SQL to select only one column, we can see the first 10 rows in Preview, but after that, the TM1 server crashes every time. After opening debug mode, I found the following message.

7596 [2] DEBUG 2011-12-30 02:48:26.219 TM1.SQLAPI Doing SQLConnectW( "WISTRON_PRD", "FINDW" )
7596 [2] DEBUG 2011-12-30 02:48:26.219 TM1.SQLAPI Doing SQLConnectW( "WISTRON_PRD", "FINDW" )
7596 [2] ERROR 2011-12-30 02:48:27.152 TM1.SQLAPI
7596 [2] ERROR 2011-12-30 02:48:27.152 TM1.SQLAPI
7596 [2] ERROR 2011-12-30 02:48:27.152 TM1.SQLAPI
7596 [2] ERROR 2011-12-30 02:48:27.152 TM1.SQLAPI
7596 [2] DEBUG 2011-12-30 02:48:51.019 TM1.SQLAPI Doing SQLConnectW( "WISTRON_PRD", "FINDW" )
7596 [2] DEBUG 2011-12-30 02:48:51.019 TM1.SQLAPI Doing SQLConnectW( "WISTRON_PRD", "FINDW" )

Since there is no further description on the ERROR message, I can not determine the cause of the problem. Is this a product bug or setting error? Thanks in advance & happy new year!

[mvaspal]

You can also try this thread:
http://www.tm1forum.com/viewtopic.php?f=3&t=5448

There is a bug with Oracle 11g ODBC driver described in the Cognos Express documentation.
I am not sure this helps you and we are using Oracle 10g so I also did not try it but you can give it a try.

[David Lee]

Thanks for the information. I have done the following settings on my TM1 server.

• Select "Enable Closing Cursors"
• Add "UseSQLFetchScroll=T" in tm1s.cfg

And I got the following results of different settings of my TI process

• Select "Use Unicode" and select all the columns of the table: Only schema but no data is shown in Preview. When I move to TI Variable tab, the server crashes.
• Select "Use Unicode" and select only 1 column of the table: Both schema and data are shown in Preview. When I move to TI Variable tab, the server crashes.
• Un-select "Use Unicode" and select all the columns of the table: Only schema but no data is shown in Preview. I can move to TI Variable and other tabs, but the server doesn't get any line of data.
• Un-select "Use Unicode" and select only 1 column of the table: Both schema and data are shown in Preview. I can move to TI Variable and other tabs, and the server can get data correctly.

Here is my conclusion:
1. The server will crash as long as "Use Unicode" is selected. Does this means the Oracle DB are not using Unicode?
2. TI cannot read the data as long as we use "SELECT * FROM ..." to select data of all columns. Does anyone got this problem before?

[rmackenzie]

TI cannot read the data as long as we use "SELECT * FROM ..." to select data of all columns. Does anyone got this problem before?

Yes, on rare occasions, this has happened to me. It seems inexplicable especially as other SQL clients can read the data with no issues whatsoever. Thinking on it, the last time this happened was actually with SQL Server and I don't recall a specific occasion with Oracle.

Un-select "Use Unicode" and select only 1 column of the table: Both schema and data are shown in Preview. I can move to TI Variable and other tabs, and the server can get data correctly.

This makes me think it is the data type definition of one of the columns in your table that is throwing TI. SQL variants have tons of datatypes (VARCHAR, INT, REAL, TIMESTAMP, RAW etc) and TI only has two (reals and strings). TI must attempt to convert any incoming type to one of its two internal types and that is where I think it falls over when failing to bring in a dataset.
I suggest that you should do a select statement for each column in turn in your table and see if TI is falling over on one or more of them. Perhaps you will need a view on that table that casts one column type into another, or there will be a way of writing your SQL query to perform that transform simply so TI can bring the data in.
HTH.