Switching Data Sources Dynamically

Maraprik

Summary

Switching Data Sources Dynamically

Content

Hey Gurus,

BIP 12c Oracle Business Intelligence 12.2.1.2.0

DB - oracle 12c

We have multiple DBs for multiple distributors. The tables are common.

Requirement is to switch between DBs dynamically. Is there a way to have a drop down of sources where in the user can select the sources and the reports point to a certain DB. they have a custom made reporting system which does this

What are the alternatives to give users this functionality.

Please see if you can figure something out

Regards

Maraprik

Hey brajesh,

Appreciate your quick reply.

Q1. yes we have different DBs where the same tables exist. we have 16 distributors whose data centers reside at different locations.

Q2. yes we have access to all DBs and if we dont, we can get the access

Q3. We can have DB links.

i was not sure if we can have a drop down of DB links as a parameter which can be used to toggle between DBs. there is a .Net based reporting system in place which allows users to do this. this is why they are particular about this functionality.

Can we have a similar functionality using any other medium. Please do let me know.

Regards

Brajesh Shukla-95078

If I understand your requirement correctly...you want to run report with same tables which is lying in the different database? If I am correct...could you please provide below details

1. Do you have all the database access you are talking about? In simple word your report definition will store in one database and actual data is distributed in different databases. So from the database where you have defined the report will have access to all distributed database?

2. If point 1 answer is yes. then how you are accessing ?. If you are accessing distributed database through DB link then we can pass DB link as a parameter in report and can connect to distributed database dynamically 

Regards,

Brajesh

Brajesh Shukla-95078

Hi ,

To access other database in oracle is possible through DB link...you can follow below steps if it helps

1. Can have DB link to all the distributed database....lets say DBLINK1, DBLINK2, DBLINK3, DBLINK4,......etc

2. Create list of value as a parameter where you can select distributor name...lets say Distributor-1,Distributor-2, Distributor-3, Distributor-4....etc.

3. For selected parameter value in step two you can have mapping or table which will store mapping between Distributor and their DBLINK...

   Distributor-1 --> DBLINK1

   Distributor-2 --> DBLINK2

   Distributor-3 --> DBLINK3

   Distributor-4 --> DBLINK4

    .

     etc...

4. In the before report trigger execute the sql using DBLINK and populate one global temporary table

5. Create Data set for that global temporary table where you can pick the records.

6. For that data set create your report.

We did same think using Oracle Report and all equivalent functionality is available using BI Publisher so I believe we can achieve this.

Regards,

Brajesh

Maraprik

Hey Brajesh,

thanks for the response.

can you please elaborate on the

'

4. In the before report trigger execute the sql using DBLINK and populate one global temporary table

5. Create Data set for that global temporary table where you can pick the records.

'

I did not completely understand this.

Regards

Brajesh Shukla-95078

Hi ,

I was talking about the event triggers in BI Publisher. Using the trigger you can register the database package. and in the database package you can select data from different data sources and populate one singe global temporary table and use that table to show the data in report. Hope I am clear now?

https://docs.oracle.com/cd/E28280_01/bi.1111/e22258/add_event_trig.htm#BIPDM334

Maraprik

Thanks Brajesh,

I will try this and get back to you. Appreciate your response.