Dynamic Data Connections

I have had a long running email thread running between Dan and David over at Valspar and myself. They have built some impressive connectivity between their in house apps and BIP using web services. The crux of their problem has been that they have multiple databases that need the same report executed against them. Not such an unusual request as I have spoken to two customers in the last month with the same situation. Of course, you could create a report against each data connection and just run or call the appropriate report. Not too bad if you have two or three data connections but more than that and it becomes a maintenance nightmare having to update queries or layouts. Ideally you want to have just a single report definition on the BIP server and to dynamically set the connection to be used at runtime based on the user or system that the user is in.

A quick bit of digging and help from Shinji on the development team and I had an answer. Rather embarassingly, the solution has been around since the Oct 2010 rollup patch last year. Still, I grabbed the latest Jan 2011 patch - check out Note 797057.1 for the latest available patches. Once installed, I used the best web service testing tool I have yet to come across - SoapUI. Just point it at the WSDL and you can check out the available services and their parameters and then test them too.

The XML packet has a new dynamic data source entry. You can set you own custom JDBC connection or just specify an existing data source name thats defined on the server.

<pub:runReport>
 <pub:reportRequest>
  <pub:attributeFormat>xml</pub:attributeFormat>
  <pub:attributeTemplate>0</pub:attributeTemplate>
  <pub:byPassCache>true</pub:byPassCache>
  <pub:dynamicDataSource>
   <pub:JDBCDataSource>
   <pub:JDBCDriverClass></pub:JDBCDriverClass>
   <pub:JDBCDriverType></pub:JDBCDriverType>
   <pub:JDBCPassword></pub:JDBCPassword>
   <pub:JDBCURL></pub:JDBCURL>
   <pub:JDBCUserName></pub:JDBCUserName>
   <pub:dataSourceName>Conn1</pub:dataSourceName>
   </pub:JDBCDataSource>
  </pub:dynamicDataSource>
  <pub:reportAbsolutePath>/Test/Employee Report/Employee Report.xdo</pub:reportAbsolutePath>
 </pub:reportRequest>
 <pub:userID>Administrator</pub:userID>
 <pub:password>Administrator</pub:password>
</pub:runReport>

So I have Conn1 and Conn2 defined that are connections to different databases. I can just flip the name, make the WS call and get the appropriate dataset in my report. Just as an example, here's my web service call java code. Just a case of bringing in the BIP java libs to my java project.

publicReportServiceService = new PublicReportServiceService();
   PublicReportService publicReportService = publicReportServiceService.getPublicReportService_v11();
    String userID = "Administrator";
    String password = "Administrator";
    ReportRequest rr = new ReportRequest();
     rr.setAttributeFormat("xml");
     rr.setAttributeTemplate("1");
     rr.setByPassCache(true);
     rr.setReportAbsolutePath("/Test/Employee Report/Employee Report.xdo");
     rr.setReportOutputPath("c:\\temp\\output.xml");
   
BIPDataSource bipds = new BIPDataSource(); JDBCDataSource jds = new JDBCDataSource(); jds.setDataSourceName("Conn1"); bipds.setJDBCDataSource(jds); rr.setDynamicDataSource(bipds);
try { publicReportService.runReport(rr, userID, password); } catch (InvalidParametersException e) { e.printStackTrace(); }
catch (AccessDeniedException e) { e.printStackTrace(); }
catch (OperationFailedException e) { e.printStackTrace(); }
}

Note, Im no java whiz kid or whizzy old bloke, at least not unless Ive had a coffee. JDeveloper has a nice feature where you point it at the WSDL and it creates everything to support your calling code for you.

Couple of things to remember:

1. When you call the service, remember to set the bypass the cache option. Forget it and much scratching of your head and taking my name in vain will ensue.
2. My demo actually hit the same database but used two users, one accessed the base tables another views with the same name. For far too long I thought the connection swapping was not working. I was getting the same results for both users until I realized I was specifying the schema name for the table/view in my query e.g. select * from EMP.EMPLOYEES. So remember to have a generic query that will depend entirely on the connection.

Its a neat feature if you want to be able to switch connections and only define a single report and call it remotely. Now if you want the connection to be set dynamically based on the user and the report run via the user interface, thats going to be more tricky ... need to think about that one!

Comments:

We recently upgraded from BIP 10 to BIP 11 and we are having some issues setting up dynamic data connections. We have BIP 10 templates that we have upgraded to BIP 11 using the upgrade assistant tool. The generated reports from these templates seems to get their data from the data source defined in the BIP server instead of getting them from the data source we defined through the JDBCDataSource object.

I have described the details at the oracle forum (http://forums.oracle.com/forums/message.jspa?messageID=9759235#9759235). If you could take some time to check it out, I would greatly appreciate it. Hoping to hear any insights from you, thanks!

Posted by guest on July 27, 2011 at 07:38 PM MDT #

Hi,

i have a query, How to create a JDBC data source using BI publisher-11g-webservices and set permissions to JDBC datasources?
any idea on this? your help is very much appreciated.

thanks,
Senthil

Posted by Senthil on November 07, 2012 at 09:23 AM MST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today