Hi,

I am trying to export a portion of level 0 data to a SQL Server database. The setup is quite straight-forward, but the performance has been rather disappointing. I first tried to run the export to a file, the outcome is a 1.5G file with about 14 million rows, and it took about 7 minutes to run. But when I did the same export to SQL, the export takes about 2 days.

My best guess is, Essbase inserts one row at a time to SQL. Is there some way to force Essbase to do bulk insert instead?

Thanks, Best rgds,

Yaoyu

You don't mention what version you are on, but in 9.3.1 and higher you can use Batch insert. From the tech reference
DATAEXPORTENABLEBATCHINSERT
Specifies whether to use the batch-insert method, instead of the default row-insert method, when the DATAEXPORT calculation command is used to export Essbase data for direct insertion into a relational database.

The DATAEXPORTENABLEBATCHINSERT and DEXPSQLROWSIZE configuration settings apply to block storage databases only.

DATAEXPORTENABLEBATCHINSERT TRUE | FALSETRUE—Enables batch insert of exported data into a relational database

FALSE—(Default) Inserts exported data row-by-row into a relational database

Description

When DATAEXPORTENABLEBATCHINSERT is set to TRUE, Essbase determines whether the relational database and the ODBC driver permit batch insert. If they do, Essbase uses the batch-insert method, and, thus, performance is optimized.

Essbase determines the batch size; however, you can control the number of rows (from 2 to 1000) that are inserted at one time by using the DEXPSQLROWSIZE configuration setting.

If Essbase cannot determine whether the relational database and the ODBC driver support batch insert, it uses the row-insert method, and DEXPSQLROWSIZE (if set) is ignored.

When DATAEXPORTENABLEBATCHINSERT is set to FALSE, an INSERT command is called for each row of exported data, and, thus, performance is slowed.

Notes

If DATAEXPORTENABLEBATCHINSERT is set to TRUE and DEXPSQLROWSIZE is set to 1, batch insert is disabled (as a DEXPSQLROWSIZE setting of 1 inserts one row at a time).

When using DATAEXPORT to export data for direct insertion into a relational database:

The table to which the data is to be written must exist prior to the data export

Table and column names cannot contain spaces

Hi, Glenn,

Thanks for the reply, that is exactly the option I have been looking for. However, when I put DATAEXPORTENABLEBATCHINSERT TRUE in essbase.cfg, the data export fails.

Below is the essbase log I got.

I am running on Essbase 9.3.1.3, and SQL server 2005. SQL client I am using to create DSN is Microsoft's SQL server client. Is there something else I need to do on the SQL server side, or do I need some special SQL server client to have this batch insert working?

Thanks a lot, best regards,

Yaoyu

[Tue Jun 30 14:36:56 2009]Local/SolInput/Input/adminuser/Info(1021004)
Connection String is generated

[Tue Jun 30 14:36:56 2009]Local/SolInput/Input/adminuser/Info(1021041)
Connection String is [DSN=sasqld012;UID=...;PWD=...]

[Tue Jun 30 14:37:08 2009]Local/SolInput/Input/adminuser/Info(1021000)
Connection With SQL Database Server is Established

[Tue Jun 30 14:37:08 2009]Local/SolInput/Input/adminuser/Info(1021013)
ODBC Layer Error: [00000] ==> []

[Tue Jun 30 14:37:08 2009]Local/SolInput/Input/adminuser/Info(1021014)
ODBC Layer Error: Native Error code [1608451681]

[Tue Jun 30 14:37:08 2009]Local/SolInput/Input/adminuser/Error(1012085)
Unable to export data to SQL table [dbo.Raw_Data]. Check the Essbase server log and the system console to determine the cause of the problem.

[Tue Jun 30 14:37:08 2009]Local/SolInput/Input/adminuser/Info(1021013)
ODBC Layer Error: [S1010] ==> [[Microsoft][ODBC Driver Manager] Function sequence error]

[Tue Jun 30 14:37:08 2009]Local/SolInput/Input/adminuser/Info(1021014)
ODBC Layer Error: Native Error code [0]

[Tue Jun 30 14:37:08 2009]Local/SolInput/Input/adminuser/Info(1021013)
ODBC Layer Error: [00000] ==> []

[Tue Jun 30 14:37:08 2009]Local/SolInput/Input/adminuser/Info(1021014)
ODBC Layer Error: Native Error code [0]

[Tue Jun 30 14:37:08 2009]Local/SolInput/Input/adminuser/Info(1021013)
ODBC Layer Error: [00000] ==> []

[Tue Jun 30 14:37:08 2009]Local/SolInput/Input/adminuser/Info(1021014)
ODBC Layer Error: Native Error code [0]

[Tue Jun 30 14:37:08 2009]Local/SolInput/Input/adminuser/Info(1021002)
SQL Connection is Freed

[Tue Jun 30 14:37:08 2009]Local/SolInput/Input/adminuser/Warning(1080014)
Transaction [ 0x11a0001( 0x4a49f8d8.0xd9490 ) ] aborted due to status [1012085].

Hi
I am trying to come up with a similar approach - dataexport to relational
But I keep getting errors saying "Unable to update table,blah blah...."
Can you please guide me on the syntax for DataExport to Relational ?

First the table has to be created ahead of time. I suggest creating the export as a flat file to see the order of the columns then create the data source