Wednesday, April 14, 2010

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.

BIP_Dashboard

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.

BIP_Dashboard2

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

Report_Link

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:

Report_Link2

After Rename:

Report_Link3 

 

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,

BI_Logical_SQL

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.

xml_data2

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.

xml_data3

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.

SQL_Data_Type

 

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.

BIEE_Datasource

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!

11 comments:

  1. Hi,

    I am trying to use charts in RTF templates and then run it as a concurrent program in EBS12.
    If I use the chart object in the RTF the report completes successfully but the chart is not displayed in the concurrent program output.
    If I use a image object in the RTF to display the chart then the report completes with warning and the following message exists in the OPP log:
    Caused by: oracle.xdo.parser.v2.XPathException: Extension function error: Error invoking 'chart_svg':'java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11GraphicsEnvironment'

    If I run the RTF template standalone then RTF works correctly. Any help would be great.

    ReplyDelete
  2. I replied to the post.
    http://bipconsulting.blogspot.com/2010/02/drill-down-to-detail-or-another-report.html

    ReplyDelete
  3. How to Paste a dashboard section from one page to another page??
    Can you please help with it because i have to add almost the section, properties with minor changes in links to every page of the dashboard..

    Thanks
    Swathi

    ReplyDelete
  4. Hi,
    I have java application from where i need to call BI publisher report, thought of using report url and putting Guest access to report folder so that i can see report generated without login to BI publisher but it looks to be security problem. is there any way i can set a security to access report other than SSo, LDAP, oracle database. Looking for your suggestion on this issue.
    Regards,
    Vijay

    ReplyDelete
  5. Hi,

    Can I have Multiple BI Answers Requests as Data sources in one Publisher Report, my BIP is 10.1.3.2. Thanks.

    ReplyDelete
  6. Multiple Answer requests is supported only with 10.1.3.4 or later.

    ReplyDelete
  7. Thanks Kanichiro, but it seems it allows multiple Answer Data Sources in a BIP, but will execute whichever is the last one in the list?

    Thanks,
    Madhu.

    ReplyDelete
  8. Can we remove the _ from the XML tags? Using answers request as data source. I'm creating a flex template and it doesnt recognise <_Query_Time_._Hour_>.

    ReplyDelete
  9. Halo

    I'm exploring bi publisher using tutorial, and i find that there are some samples for report layout locate in shared templates and also we should see some example of HR, Financial Report that locate in catalog->shared folders->samples, but in mine
    those samples not appear, i try to move my folder to the folder that could appear in catalog folder but didn't work, how to fix this?please help

    Thanks so much

    ReplyDelete
  10. Hi,
    I follow your blogs as well as your BIP channel on Utube. Great stuff I must say. I had worked on both 10g and 11g. Just a couple of questions -
    1. When bursting reports as email, can we customize the attachment/file names and pass it as parameters like we do with File bursting?
    2. Can we use Delivers to schedule BIP reports as iBots, we were using Java APIs in 10g, is there a better way in 11g? Or is it a best practice to use BIP to schedule and deliver reports?
    -Padma, Bangalore

    ReplyDelete
  11. Hi Mitra, in order to deliver BIP reports you need to use BIP's scheduler.

    Also, you want to try use 'OUTPUT_NAME' parameter to set the file name.

    ReplyDelete