cancel
Showing results for 
Search instead for 
Did you mean: 

Options to Extract SAP data from SQL Server to load in Oracle Database

laurie_mcginley
Participant
0 Kudos

We are looking at options to extract data from our SAP ECC6 system into our Oracle data warehouse. FYI, we do not use Oracle Warehouse Builder or SAP BW.

Currently we run several jobs overnight to extract (ABAP reports) data to a flat file where a process picks that up and runs a merge and sqlload into the Oracle database that is our data warehouse. This is taking the better part of 10+ hours now start to finish.

One option that was proposed was to have access from Oracle via heterogeneous services to the SQL Server database and select the data natively from the tables into the oracle staging database. Several problems seem to jump out at me there.... security, SAP "tables" such as BSEG which really can't be accessed natively, are only two.

Another option that was floated was to create views of the data we want to extract on the SQL database and, again using heterogeneous services, select the data via the views. This seems to need some SAP work to setup the views so they are known to the SAP data dictionary. Again, we may have a security issue with access to the SQL Server database outside of the SAP app. (Licensing is ok as we have separately licensed the SQL Server from Microsoft.

Another option I'm just now looking into is to use SAP to connect to the Oracle database via dbconnect I think. Again, just found out about this so am still looking into it.

I would appreciate all your thoughts and suggestions for this problem. Our data extracts/loads will only keep getting bigger and longer and our window to get it done nightly isn't getting bigger.

Thanks in advance for your ideas,

Laurie McGinley

Accepted Solutions (0)

Answers (1)

Answers (1)

markus_doehr2
Active Contributor
0 Kudos

> Another option I'm just now looking into is to use SAP to connect to the Oracle database via dbconnect I think. Again, just found out about this so am still looking into it.

>

> I would appreciate all your thoughts and suggestions for this problem. Our data extracts/loads will only keep getting bigger and longer and our window to get it done nightly isn't getting bigger.

The question is: can you implement a delta mechanism or do you really need to load all the data of all the tables you want to analyze every night? If you really need that, then the data need to be read on the source system and to be written in the target system. A direct database link is possible but that does not necessarily say it's faster.

Did you try to evaluate where the bottleneck is - where the time is being used during the night?

Markus

laurie_mcginley
Participant
0 Kudos

Hello Markus,

We are wanting to build the extracts to only pull delta data, but haven't had time or resources to look into just what the coding would need to be from the sap side. The question has always been, how do we determine a changed record in a table without timestamps or other identifying data to mark it as changed. So that is an ongoing request we would like to achieve.

The bottle neck at this time is occasional network time outs for the larger exports (BSEG and such). And then the process on the oracle side to identify those records in the export that are different from those in the warehouse (a merge function I think?). The extracts themselves take about 3 hours to complete. We have them broken into about 6 separate jobs/reports that extract all data from the selected tables. The rest of the time, from 10 to about 7am is the oracle processing to identify changed records and load the oracle tables.

What the developer would like is to have a heterogeneous connection to the SAP SQL Server database and a database link from Oracle to SAP and select directly. That won't work because of tables like BSEG, and because of security concerns. So the option to have views that the database link would connect to was the next option. The logic to identify the delta records would be on the Oracle side of the equation I believe.

Hopefully that gives you more information.

Thanks !!

Laurie