SQL Query Extract Taking 3 Hours To Load Using TI

[slu]

All,

We upgrade from TM1 9.4 to 9.5.2. At the same time, we changed from Oracle SQL server to 2008 SQL server. Our source data is from JD Edward. The SQL statement we used to update a project dimension which is simple, there are only two variables to select, one is project ID (varchar), another is project name (char). Running this query takes seconds in SQL server 2008, there are only a total of 1329 records. But it takes three hours to import it through TM1 (ODBC). This is just one of TI processes we change data source, other works fine. Consultant who helps us doing upgrade stated that connection is good, if just running a few records of this table, it is good too. I copy new code (SQL server) and old code (Oracle SQL) below, consultant did the trim() in TI instead of in SQL. I read threads about SQL fails, none close to we experienced, I am looking for a solution. I try MS query tomorrow. This TI hung our chore and stops our upgrade process. Thank you

Code: Select all

select J_ABAN8 , J_ABALPH
from VW_TM1_F0101_SUBLEDGER_DESC

Code: Select all

select trim(J_ABAN8), J_ABALPH
from VW_TM1_F0101_SUBLEDGER_DESC

[jstrygner]

I experienced that speed of loading via ODBC may significantly change if you check/uncheck the "Use Unicode" checkbox on first tab of TurboIntegrator interface.

[nick_leeson]

TRIM is not a SQL Server Function is it a UDF that you are using ?

How about testing it with a single where clause ? Oracle to SQL server 2008 is not a easy one. Have you created the appropriate indexes ?

[slu]

we use unicode (check), also we use rtrim(ltrim()) to repalce Oracle SQL trim(), wroks for other TIs, but not this one. The source data is a view, maybe that affects TM1?

[tomok]

we use unicode (check)

Have you tested "unchecking" unicode?

[slu]

I have not tested unchecking unicode. I think the issue is related to data type. The TI is used to update dimension (one is project, other is customer, both have problems), TI syntax does element adding to the dimension if element is not found. Our DBA team changed one dimension's data type (customer ID from integer to char), a new customer ID was added to the dimension like 000101731-A, if we keep the orignal data type (int), a new customer ID was added to the dimension with a profix 101731.-Alias, now my dimension has three elements in it casuing attribute maintenance batch error- not unique attributes (for Orphan customers only), they are three elements in my customer dimension: 101731-Seven Eleven LLC, 101731.-Seven Eleven LLC, 000101731-A-Seven Eleven LLC. We contact IBM support for the problem. As to my project dimension, we change data type from VarChar to Char, the TI was "idle"...

[nick_leeson]

OK Take the "new" SQL and put into a new TI and do a preview and compare the timings !!

[slu]

I think my consultant fixed the problem by using numbertostring function for cust dimension, using ASCIItextout() for project ID, both processes worked suddenly. My TI experience is only sufficient to understand how written TIs do, I am not up to developing anything yet. Seems like an easy fix since I knew it is date type issue, not sure why it took so long for the consultant to figure that out.