Lower number of JDBC channels strategy

[Former Member]

Hello Experts,

I have been asked to optimize this scenario:

There are 15 databases from which master data and sales data is queried and uploaded to BW using asynchronous interfaces with daily scheduled JDBC sender adapters. The original developers decided to segment the queries for each type of master data into 11 separate queries according to certain parameters, mainly to facilitate mappings to enrich the data. There is a restriction: We cannot use stored procedures in the database, so the solution must be implemented using SQL Queries only.

As a result we have 15 databases x 10 master data objects x 11 subdivisions: 1650 different JDBC channels, plus some other channels for sales data. And there is one set of the repository objects necessary for each of those channels. So you can imagine the level of redundancy in the repository too.

What would be the best strategy to reduce the number of JDBCchannels (and possibly repo objects)?

Is there a way to implement some kind of dynamic or intelligent query?

Thanks in advance for your kind responses!

-Sam.

[VijayKonam · Accepted solution]

Is there a way where you can get the complete required data from a single database and split it at the mapping level based on the data/keys? May be thats one way you can look at the optimization which might ofcourse require BPM.

VJ

[Former Member]

Thanks guys.

Implemented the scenario as Sunil described.

We will be migrating the existing interfaces to use this scenario.

There was an (irrational) fear of using BPM due to supposed excessive machine resources use by that component. This has proved not to be the case in recent tests we made.

-Sam.

[Former Member]

Hi Sam,

Since your landscape got 15 different database there will be minimum 15 JDBC communication channel as the parameters are different from each other.

The perferfect design will be a

JDBC (asyn- for triggering) > BPM> JDBC (syn - to get the required data from other DB)> Transformation (if required)> ABAP proxy ( syn to upload Master Data into SAP , here you can logically separate the backend transcations using different Blocks) ---> Send back the status to client(use JDBC asyn channel)

Regards,

Sunil.

[Former Member]

you can achieve it in two ways

1) ask DBA to design queries using joins/unions...

2)make it BI synchronous sender (ABAP proxy to JDBC) and pass the query as string, in which you need to define only 15 channels of different database.

[JoelTrinidade]

Hi Samuel,

Your is a classical case of carrying the burden of the past . You can optimize but then that would be redesigning the way your scenarios work.

If you want to go with the same architecture that you have now then at the most then you have to do some performance tweaking with the help of your basis.

Regards

joel