Cheat Sheet for BI Publisher Integration with BIEE - Part 2

This is a follow up from the last post and today I’m going to talk about the following three integration points. 

• Embedding BI Publisher Reports into BI Dashboard
• Answers Request as Data Source for BI Publisher Report
• BI Server as Data Source for BI Publisher Report

Embedding BI Publisher Reports into Dashboard

There are two ways to embed BI Publisher reports within the Dashboard page. One is to directly embed the report output (e.g. PDF, HTML, etc) into the page.

Another is to list the BI Publisher reports link(s) so that the users can click the link and open the report in another window.

You can choose between the Embedded Content or Link option by selecting it under Display Mode in the Property.

When you select the Report Link option the report folder path will be shown as the list as default. Of course you can change this by clicking ‘Rename’ button in the Dashboard edit page.

Default:

After Rename:

 

 

View Latest Version will pick the latest version of all the previously run reports. So if your report might take some time and you’d rather want to display a pre-run report for your users then check this option.

Troubleshooting

Can’t see any BI Publisher report in the dialog

That means probably your BI Publisher server registration setting is not right. Open instanceconfig.xml file under %OracleBIData%/web/config folder and see all the BI Publisher URL addresses are set appropriately. Your BI Publisher server might be running on a different server machine or on a different J2EE instance with a different port number. Here is an sample of the BI Publisher registration in the configuration file.

 

<AdvancedReporting> <ReportingEngine>XmlP</ReportingEngine> <Volume>XmlP</Volume> <ServerURL>http://knishida-lap:9704/xmlpserver/services/XMLPService</ServerURL> <WebURL>http://knishida-lap:9704/xmlpserver</WebURL> <AdminURL>http://knishida-lap:9704/xmlpserver/servlet/admin</AdminURL> <AdminCredentialAlias>bipublisheradmin</AdminCredentialAlias> </AdvancedReporting>

Also, if your BI Publisher is running with SSL setting then make sure you set ‘https’ instead of ‘http. See this post for the SSL setup for BI Publisher.

Performance is so slow

We had a performance issue before when we used the report list option but the issue was addressed by a BI Publisher’s December patch set. If you hit this issue I’d recommend you to apply this patch set. The latest patch set is 9546699. Tim has posted the detail at his BI Publisher official blog.

 

Answers as Data Source

You can use BI Answers request as a data source for your report. This is a useful and the most popular option to build BI Publisher reports against data from BI Server. Especially when you have already developed BI Answers reports all you need to do is to point to one of the Answers reports and start building the report layout with BI Publisher’s RTF Template, I mean MS-Word.

Troubleshooting

I don’t see anything in the BI Catalog window.

When you have a problem like this these are the places where you want to check.

1. Presentation Server configuration in BI Publisher Administrator Web page
2. Make sure you test with the Administrator’s Username/Password to login to BI Answers
3. Make sure Presentation Server and Java Host are up and running
4. If your BIEE is setup with SSL then make sure you choose ‘https’ instead of ‘http as ‘Server Protocol’.

Best Practice

While it’s easy to just point to the Answers report to get the report data, I personally recommend a use of BI Server as a data source instead of BI Answers. Using BI Server as a data source requires you to write the BI logical query but you can either copy the sql queries from BI Answers ‘Advanced’ tab,

or use BI Publisher’s query builder so that you don’t need to type the SQL queries manually, though I do know that the query builder has some limitation to build complex SQL queries.

Why I’m recommending to use BI Server as a data source? There are two benefits of using BI Server as your data source over BI Answers.

1. You cannot control the XML element name, which BI Publisher automatically generates based on the BI Answer’s original column name (not the custom name). This ends up sometimes really a long XML element name, which is hard to deal with in RTF template development. (See below sample XML)
2. Having BI Server as the data source means you are directly accessing to the BI Server. Having BI Answers as the data source means you are accessing to BI Answers (Presentation Server) first then have BI Answers accessing to the BI Server. So the more layers involved the more the performance will have an impact.

Here is the sample XML data file generated by BI Answers.

Yes, it looks ugly. And it gets even worse when you have a case statement like you see in the above example. This long XML element name brings in a challenge when you develop a RTF template and want to use a condition with such XML element name. But, if I use the BI Server as a data source and copy and paste the query then the XML data can become like the below.

Now it makes your RTF template development and maintenance much easier. I used a SQL Query data type for the above, but of course you can use the Data Template, with which you can have even more control not only on the XML element name but also the XML structure.

 

BI Server (RPD) as Data Source - Direct Access

As I have already mentioned about this feature in the above section, this allows you to access directly to the BI Server and retrieve data for the BI Publisher reporting. With this option basically BI Publisher is acting like BI Answers. Both reporting tools are communicating to the BI Server similar way, though one is through ODBC and another is through JDBC.

Configuration

With the default installation you should see a sample BI Server JDBC connection called ‘Oracle BI EE’. You can either update this to suit to your environment or create a new one. You need hostname and port number information plus BI Server (RPD) Administrator’s username/password. The port number is not the OC4J or HTTP port number, it is the one for BI Server. You can find this port number from NQSConfig.INI file. Look for RPC_SERVICE_OR_PORT to find the port number. (e.g. RPC_SERVICE_OR_PORT = 9703)

If your BIEE is configured in clustering then the Connect String would be something like this.

jdbc:oraclebi://BI-CCS-01:9706/PrimaryCCS=BI-CCS-01;PrimaryCCSPort=9706;SecondaryCCS=BI-CCS-02;SecondaryCCSPort=9706

You can find the detail in the BIEE Deployment Guide.

Best Practice

SQL Query vs. Data Template

With this option you can use either SQL Query or Data Template as the data type. Though it’s easy and simple to use the SQL Query type but I’d recommend a use of Data Template because of the following reasons.

• You have more control on the data structure
• You can use all the aggregate functions to do the aggregated calculation
• You can combine multiple data from different data sources

Proxy Authentication

If you have implemented Row Level security in the RPD then you want to check ‘Use Proxy Authentication’ check box. This option allows BI Publisher to send the session user name to the data source so that the data source (BI Server in this case) can be aware of it.

Conclusion

That’s pretty much it. But I know that some of you might still have a problem in this integration setup. And things can get a bit more complicated when SSL or HA/Clustering configuration come into a picture. I’ve helped a lot of folks at our clients to troubleshoot or fix their issues to make the integration work. Some projects went super fine without any trouble in this integration but some didn’t. But the key is whether if you understand how it works and the integration architecture well. Once you understand it then you know where to look at when the problem arise or at least you can guess where could be wrong.

If you are interested in someone to come in to provide a health check or troubleshoot let me know. We have many consultants who have done this in complex environments so we can help!