JDBC-->PI7.0-->SAPBI with Proxy

[Former Member]

Hi Experts,

I have a requirement like this : JDBC(Sql Server)>PI7.0>SAPBI.

I need a suggestions from the above requirement. For this requirement I am planning to use sender as a JDBC Adapter and Reciver as Proxy. I have some questions on this requirement.

1) I need to extract data with more than 15 tables of Sql server data base. Is it possible to extract all data at a time, If yes plase give me some tips.

2) How to combined all 10 extracted tables data into as a Proxy. Please provide some inputs. or it better each table as a one XML file like this....

Lastly one more question is, what is best process for the above requirement either Proxy or IDOc's.

Thanks in advance.

Regards,

Chandra.

[Former Member]

Hi Chandra,

In addition to the above responses you can follow the approach as:

1. Discuss with your DB team and find out what is the average number of records to be selected from each table on each run of the interface.

2a. If the number of records are large then its better to go for separate interface for separate tables.

2b. If the number of records are limited say 15-20 then you can work either with a single staging table or a stored procecdure after discussing with the DB team what they can agree to.

3a. In case of a single staging table:

i. Create a data type similar to the staging table fields.

ii. Generate a proxy accordingly.

[http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/frameset.htm]

3b. In case of stored procedure

i. Ask the DB team to include a single Select querry with in the Stored Procedure, and also to include a update querry to update the read records once called by Stored Procedure/ DELETE the read records.

ii. In the JDBC sender adapter in the field Query SQL Statement write the querry as as: EXEC "Stored Procedure Name" ("Parameters if any").

iii. In this case your Sender Data type structure should be same as the structure being called by the Select query with in the stored proc. or in other words the sequence followed in the Select query should be same in the sender DT.

iv. Generate a proxy accordingly.

[http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/frameset.htm]

Note: Proxy is always better in this case upon IDOC. If asked by business or BI team you can as well go with RFC keeping in consideration the load/no. of records. In case of bigger messages Proxy is always better being a adapter less communication.

Hope this helps you.

Regards,

Rahul Grover

[rajasekhar_reddy14]

>

> 1) I need to extract data with more than 15 tables of Sql server data base. Is it possible to extract all data at a time, If yes plase give me some tips.

JDBC->PI>ABAP proxy is right approach.

> 2) How to combined all 10 extracted tables data into as a Proxy. Please provide some inputs. or it better each table as a one XML file like this....

>

create intermediate table in data base level it self, this intermediate table will hold data of 10 tables, then retrieve data from intermediate table.

or

go with stored procedure.

> Lastly one more question is, what is best process for the above requirement either Proxy or IDOc's.

>

go with ABAP Proxy.

Regards,

Raj

[Former Member]

Hi Raj,

Thanks for your suggestions.

Once we collect all the tablesu2019 data either use of intermediate table or stored procedure. Then I need a suggestion on sending of all collected data from PI to BI as Proxy. But How to handle all of these data used a proxy. Could you please give me some steps wise all of requirement. And also I have one more question, I need to extract all the updated data on Daily basis from Sql server data base to PI and BI Systems. Please help how to achieve the above requirement.

If possible could please provide all activities on steps wise.

Thanks in Advance.

Regards,

Chandra

[rajasekhar_reddy14]

Hi,

Intermediate Table Approch:

as i already mentioned you have to create one more table in data base, ask you data base team , if they are okay to create new table to store data about remaing tables then it is very simple, follow standard JDBC scenario approch.

some times DB team will not accept for this.

or

write a stored procedute to retrieve data from multiple tables and you have to create data type structure in PI for this , standard strcuire row followed by all colums names..

search in sdn jdbc sender scenarios, you will find ton of links.

still you need inputs post here.

Regards,

Raj

[abhay_rajhans2]

Hi Chandra,

1) I need to extract all the updated data on Daily basis from Sql server data base to PI and BI Systems.

As suggested by Raja, request the database team to create a table on SQL server. In this table you can create a extra field. When ever you read a record from the table mark the field as read (Pass value in this field as 'X'.) Using Availability Time Planning you can schedule the JDBC adapter to particular time and it will read the table and fetch the entry where the extra field value is blank (Not X).

2) Then I need a suggestion on sending of all collected data from PI to BI as Proxy. But How to handle all of these data used a proxy.

If you are using one table then it will be very easy as you just need to pass the data in a simple structure to ABAP proxy.

If you not using single table then in the data type structure in PI create nodes and subnodes. Each node can contain data for one table.