TM1 Cube extract to Oracle DB table (ODBC) - Help

[spiderwallet]

Hi All,

I have been using the ODBCOpen, ODBCOutput, ODBCClose commands in TI to push data from TM1 to Oracle via Insert statement in the DATA tab.
This process has been OK for small data sets but struggles with larger data sets i.e. 700k records.

To get around this, I have altered the TI to asciioutput each cell to csv in the data tab and am now trying to insert the csv into the Oracle table directly via the epilog.
Apparently this can be achieved in Microsoft SQL via BULK insert table from csv, but am struggling to find something similar in Oracle via ODBC that works.

The other method that I already know about is to ftp the csv file to the unix box shared by Oracle and then create an external table in the target schema@DB. But hoping there's an easier way...

Has anyone been able to do this? Any help would be appreciated.

Thanks!

[lotsaram]

Another strategy which we always recommend is to batch the ODBCINSERT within TI by concatenation of the insert into or update into statements into a TI string variable. With a long() test of the variable when it gets to 32k characters then execute the ODBCINSERT otherwise just proceed to next record. Then a closing insert on the epilog to catch the remainder.

Instead of an insert per record you may then only have one insert per several hundred records so the end to end time can also be order of magnitude faster.

[paulsimon]

Hi

I am no Oracle export but you could try the following from an example I found on the net which seems to be the equivalent of the SQL Server BULKINSERT.

Define a .ctl file to describe the import

load data
options(direct=true)
insert
into table emp
fields terminated by ',' optionally enclosed by '"' TRAILING NULLCOLS
(
c1 DECIMAL EXTERNAL,
c2
)
Execute SQL*Loader as below.

sqlldr userid=user/pass@db data=reports.csv control=emp.ctl

I don't know if you can execute SQL*Loader via an ODBC call based on an Oracle DSN. However, the alternative might be an ExecuteCommand. Obviously you would need the Oracle Client on the TM1 Box, but I guess that you already have that for the existing ODBC method.

Regards

Paul Simon

[spiderwallet]

Thanks Paul & lotsofram for the help.

I've tried both solutions and have decided to use the long function and concatenate each insert statement as shown below. Once the length reaches a max i.e. >500, it calls the ODBCOutput query and then sets the query back to '';

The query passes approx. 50 insert statements at once and has sped up the process by 50x which ill be further tinkering to find its limits.

Below is the the oracle query structure that I've used, which seems to be the smallest amount of characters possible for my scenario (no column name definitions required).

INSERT INTO table
SELECT '1','2','3','4','5','6','7','8' FROM DUAL
UNION ALL
SELECT '1','2','3','4','5','6','7','8' FROM DUAL
UNION ALL
....

Thanks!
N