cancel
Showing results for 
Search instead for 
Did you mean: 

Data from BW to Oracle.

Former Member
0 Kudos

Hi All,

I am new into BW and i know how to get the data from my applications backend i.e ORACLE to BW system for reporting purpose. I have a scenario where user can edit this information through IP. I want this modified data to go back to ORACLE DB so that i can use this in my webdynpro application for further processing.

So how can i achieve the transfer of data from BW to ORACLE DB in real time.

Hope i am clear and will get some solution for this.

thanks & regards,

Manoj

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Dear Manoj,

We did a retraction from BW to an Oracle database.

These are the steps you have to go through:

SAP BW side:

1) invite the Oracle database in the protocol.ora file where your SAP database is running.

This allows to establish a connection from your Oracle DB to the SAP BW database

2) grant select privileges on the tables you would like to extract to a dedicated database user

Oracle DB:

3) create a database link to the SAP BW database using the TNS connection and the dedicated user

4) loggon with a user that can use the db link

5) create necessary views by building the SELECT statements (the views are stored on the

Oracle db server wich is the ONLY invited client)

6) use the views in i.e. INSERT as SELECT Statements to load the data via the db link to the Oracle tables

Steps 1 and 2 needs support form SAP Basis

hope it's clear

bye yukonkid

Former Member
0 Kudos

Hello Manoj,

iam not sure whether your company got open hub service available as it is an addon service its costly if you guys dnt have open hub then follow benard procedurei think he is talking about DBconnect or else search for DBCONNECT in the forum .you can connect your BW to Oracle using dbconnect.

Hope it helps.

Thanks & Regards

Jai

Former Member
0 Kudos

@Jai

no, it's not DBCONNECT.

Database links are an ORACLE feature that comes for free if you have Oracle on both sides.

Main steps for the implementation part:

BW side:

1. you have to invite the Oracle database in the protocol.ora file of the MY_BW database.

The host is the server where the local Oracle database instance is running.

protocol.ora:

...
TCP.INVITED_NODES= (myORACLEserver,...other hosts...)
...

2. Define a database user in the BW database MY_BW_USER

3. Grant SELECT privileges for the user MY_BW_USER that is used in the link:

grant select on my_bw_table to my_bw_user;

Oracle side:

1. database link

CREATE DATABASE LINK "MY_BW.WORLD"  CONNECT TO "MY_BW_USER" 
    IDENTIFIED BY "my_bw_user_password"  
    USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = mySAPBWServer.com)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = MY_BW)(GLOBAL_NAME = MY_BW.WORLD))
  )' 
;

2. Create a view with the SELECT to extract from BW via the link (listed after the @):

Create view view_extract_from_bw as
select * from my_bw_table@MY_BW.WORLD

You can do any transformations in the SELECT part to fit the data into the local Oracle table later in the insert.

3. You will pull the data on the Oracle DB side from the BW database via the view that inturn uses the link

to the BW Oracle system:

insert into local_Oracle_table select * from view_extract_from_bw

You see some support is needed from Basis guys to establish the link. But once implemented

you have a interface from your local Oracle to the BW database.

bye

yk

Edited by: Bernd Boecker on Jul 2, 2008 1:52 PM

Former Member
0 Kudos

Hi Bernd,

Thanks for the solution. Well i didn't get chance to check the solution. I will try this approach and will let you know if something does not work.

I hope you will not mind if i mark the question as answered after trying the solution.

I will get back to you soon.

thanks & regards,

Manoj

Answers (2)

Answers (2)

Former Member
0 Kudos

Hello Bernd,

Thanks for sharing your knowledge of oracle connection to bw with us . i wish i can give you 10 points buts its manoj who opened this thread i hope this one will help him and give you points and close the thread.

Thanks & Regards

Jai

Former Member
0 Kudos

Hi Manoj..

I hope u must be knowing about the openhub concept in BW.. using that u can export the data to the flat file and then u can use that flat file to be loaded in Oracle..

Or u can try the XML export as well..

Thanks

Former Member
0 Kudos

Hi Murali,

Thanks for your reply. But sorry to say that i have no idea about open hub in BW. Could you please elaborate more on this. One more thing is that i want this transfer in real time. I mean say for example when the user clicks on save on the UI the data needs to go into both BW and as well as to the ORACLE.

thanks & regards,

Manoj

Former Member
0 Kudos

Hi,

Is there any solution to this requirement?

thanks & regards,

Manoj

Former Member
0 Kudos

Hi Manoj,

If u use the Openhub concept(Infospoke).. u can export the data as flat file and then u can move this flat file to Oracle..

U can use this flat file as source to ur oracle.. and then load the data in oracle..

I dont think that u can do the combined activity at the same.. u can load like that.. as loading to BW and Oracle at the same time..

I hope this is clear to u.... U can wait for some more openions as welll..

for the documentation on Open Hub.. u can search the forum..

Thanks

Assign points if thishelps