How to Run a Report with BI Publisher Web Service?

This is a follow up post from the last post ‘How to use BI Publisher Web Service with JDeveloper 11G’. Today, I want to talk about how to run a BI Publisher report with the Web Service within a Java Servlet. This post is assuming that you have already generated Java Web Service Proxy with JDeveloper, which creates a set of Java classes/methods that are required to call BI Publisher Web Service APIs. If you haven’t please take a look at ‘How to use BI Publisher Web Service with JDeveloper 11G’ first.

Which Method to run Report?

You can use a runReport() or runReportInSession() method to have BI Publisher Enterprise server to run a report and get the report output. The report output will be returned in binary format so you need to take care of handling the binary format appropriately in order to display or locate the report output.

Here are the definitions of runReport() and runReportInSession().

• ReportResponse runReport(Request request, String username, String password);
• ReportResponse runReport(Request request, String sessionToken);

While with runReport() method you need to always pass username and password, with runReportInSession() you can just pass a session token after you get with login() method. (See ‘How to use BI Publisher Web Service with JDeveloper 11G’ for the detail of the login() method.)

I’m going to use the runReportInSession() for my example below.

How to Call runReportInSession()?

First of all you need to create a ReportRequest object, with which you can set a report that you want to run. You can also set a report layout template name, report output format type such as ‘pdf, ‘html’, etc, and locale (language and territory) information. Once the ReportRequest is set then you can pass it to the runReportInSession() method along with the session token. The runReportInSession() willl return a ReportResponse object, which contains the report output in a binary format along with the report meta-data information such as content type, language information.

Here is a visualized process flow of the runReport() method.

 

So basically there are three steps.

1. Create ReportRequest object
2. Call runReportInSession() method
3. Get a report from ReportResponse object

Create ReportRequest Object

You can set the following information when you create the ReportRequest object

• Absolute Path to the Report
• Report output format
• Locale (Language and Country/Territory)
• Template name

Function	Method	Description
Output format	void setAttributeFormat()	You can set a desired report output format such as ‘pdf’, ‘rtf’, ‘excel’, etc.
Locale	void setAttributeLocale()	You can set a user’s preferred report locale such as ‘en-US’ for English/America, ‘fr-FR’ for French/France. Based on this setting BI Publisher Enterprise will decide which language translation to use for the specified template if available.
Template Names	void setAttributeTemplate()	You can set a desired template among available templates that are registered for the specified report.
Absolute Path to the Report	void setReportAbsolutePath()	You can set an absolute path for the report that you want to run.

Example:

ReportRequest req = new ReportRequest(); //Instantiate ReportRequest object req.setAttributeFormat("pdf"); //Set a report output format req.setAttributeLocale("en-US"); //Set a report locale req.setAttributeTemplate("Simple"); //Set a layout template name req.setReportAbsolutePath("/HR Manager/Employee Salary Report/Employee Salary Report.xdo"); //Set a report path

 

Call runReportInSession()

You can call the runReportInSession() method through the publicReportService Java Web proxy client object. It returns a ReportResponse object.

Example:

ReportResponse res = new ReportResponse(); //Instantiate ReportResponse object res = publicReportService.runReportInSession(req, sid);

 

Get a Reprot from ReportResponse Object

Here is a list of the methods that you can use to retrieve information from the ReportResponse object. And the most you care would be the getReprotBytes(), which returns a report output in a binary format.

Function	Method	Description
Report Content	byte[] getReportBytes()	You can get the report content itself. It returns the report in a form of binary so you need to take care of a proper encoding to retrieve the data and handle it appropriately.
Report Content Type	String getReportContentType()	You can get the report’s content type. For example if the report output is generated in a PDF format then it will return ‘application/pdf’.
Report Locale	String getReportLocale()	You can get a locale information of the generated report.

Example:

response.setContentType(res.getReportContentType()); // Set content type to HttpServletResponse byte[] binaryBytes = res.getReportBytes(); // Get report content from ReportResponse

Troubleshoot

You might not get any report content from the ReportResponse object. If that’s the case you want to add the following code to set a data chunk size to ‘-1’. I had to set this before in order to get the report output successfully, but now it’s working fine without the setting with my latest BI Publisher Server environment. (March ‘10 patchset).

ReportRequest req = new ReportRequest(); //Instantiate ReportRequest object req. setSizeOfDataChunkDownload (-1); //download all

Conclusion

Now you know how to call BI Publisher Web Service to run a report. My example is trying to get the report output and display it in a browser, but of course you can display it as part of your application UI or locate it on a file system.

Here is my whole source code. Again, I’m using a Java Servlet to exercise my code with BI Publisher Web Service.

package project1; import com.oracle.xmlns.oxp.service.publicreportservice.AccessDeniedException; import com.oracle.xmlns.oxp.service.publicreportservice.AccessDeniedException_Exception; import com.oracle.xmlns.oxp.service.publicreportservice.DeliveryRequest; import com.oracle.xmlns.oxp.service.publicreportservice.InvalidParametersException; import com.oracle.xmlns.oxp.service.publicreportservice.InvalidParametersException_Exception; import com.oracle.xmlns.oxp.service.publicreportservice.LocalDeliveryOption; import com.oracle.xmlns.oxp.service.publicreportservice.OperationFailedException; import com.oracle.xmlns.oxp.service.publicreportservice.OperationFailedException_Exception; import com.oracle.xmlns.oxp.service.publicreportservice.PublicReportService; import com.oracle.xmlns.oxp.service.publicreportservice.PublicReportServiceClient; import com.oracle.xmlns.oxp.service.publicreportservice.PublicReportServiceService; import com.oracle.xmlns.oxp.service.publicreportservice.ReportRequest; import com.oracle.xmlns.oxp.service.publicreportservice.ReportResponse; import com.oracle.xmlns.oxp.service.publicreportservice.ScheduleRequest; import java.io.IOException; import java.io.OutputStream; import java.io.PrintWriter; import java.net.URL; import javax.servlet.*; import javax.servlet.http.*; import javax.xml.namespace.QName; public class BipServletTest extends HttpServlet {     private static final String CONTENT_TYPE =         "text/html; charset=windows-1252";     private static PublicReportServiceService publicReportServiceService;     public void init(ServletConfig config) throws ServletException {         super.init(config);     }     public void doGet(HttpServletRequest request,                       HttpServletResponse response) throws ServletException,                                                            IOException {         response.setContentType(CONTENT_TYPE);         OutputStream os = response.getOutputStream();         //PrintWriter out = response.getWriter();         String sid = "";         publicReportServiceService = new PublicReportServiceService(new URL("http://knishida-lap.us.oracle.com:7001/xmlpserver/services/PublicReportService"),                                                new QName("http://xmlns.oracle.com/oxp/service/PublicReportService",                                                          "PublicReportServiceService"));         PublicReportService publicReportService =             publicReportServiceService.getPublicReportService();         //To get a session id        try {             sid = this.getSessionID("Administrator", "Administrator", publicReportService);         } catch (AccessDeniedException_Exception e) {             System.out.println("invalid user");         }         //To generate a report and display it         ReportResponse res = this.getReportInSession(publicReportService, sid);         byte[] binaryBytes = res.getReportBytes();         os.write(binaryBytes);         response.setContentType(res.getReportContentType());     }     public String getSessionID(String username, String password, PublicReportService publicReportService) throws AccessDeniedException_Exception {         String sid = publicReportService.login(username, password);         return sid;     }     public ReportResponse getReportInSession(PublicReportService publicReportService,                                              String sid) {         ReportRequest req = new ReportRequest();         ReportResponse res = new ReportResponse();         req.setAttributeFormat("pdf");         req.setAttributeLocale("en-US");         req.setAttributeTemplate("Simple");         req.setReportAbsolutePath("/HR Manager/Employee Salary Report/Employee Salary Report.xdo");         //req. setSizeOfDataChunkDownload (-1); //download all         try {             res = publicReportService.runReportInSession(req, sid);             System.out.println(res.getReportContentType());                    } catch (Exception e) {             {                 System.out.println(e);             }         }         return res;     }  }

I’ll talk about how to schedule a BI Publisher report with the Web Service tomorrow, so stay tuned!

How to use BI Publisher Web Service with JDeveloper 11G?

How can I call BI Publisher Web Service from my Java application with JDeveloper?

BI Publisher provides a set of Web Service APIs, with which you can do pretty much everything you can do from the UI such as running reports, scheduling reports, creating/editing/deleting reports, etc. With JDeveloper you can easily integrate the BI Publisher Web Service with your Java application and start developing your code.

I wrote a ‘BI Publisher Web Service development guide for Java developers with JDeveloper’ paper quite a while ago, but never talked about this topic on this blog. And there have been some changes since then. Now JDeveloper is 11G and BI Publisher has added more APIs and fixed some issues we had before.

So I’ll spend the next couple of posts talking about this topic, hope it will be useful for Java Developers out there who want to integrate with BI Publisher to run and manage BI Publisher reports within your application.

Environment

Here is my environment information:

• BI Publisher 10.1.3.4.1 with March Patch Set (9546699)
• JDeveloper 11.1.1.2
• WebLogic Server 11G (10.3.2)

Get BI Publisher WSDL File

First, you need to get a WSDL file first before start anything. You can obtain your BI Publisher Server’s WSDL file by submitting the following URL from your browser.

http://hostname:port/xmlpserver/services/PublicReportService?WSDL

Note: Somehow I couldn’t get this with my BI Publisher running on a OC4J. I don’t know why but keep getting an error message. Looks many people are just fine or it worked fine after upgraded or something. Since I didn’t have enough time to keep debugging I’ve just simply moved to my another BI Publisher Server running on WebLogic 11g Server, which is working perfect, thanks god!

Once you get the WSDL file then save it to your local system.

Create a Web Service Proxy

Now, in order to integrate with Web Service within Java application you need to create a Web Service Proxy. There is a UI wizard to generate this with JDeveloper. In the New Gallery (right click on Project and ‘New’), select Web Server Proxy under Business Tier/Web Services and follow the steps with the default options until finish. When you are asked to provide a WSDL then use the WSDL you just saved at above.

In my example I have chosen ‘JAX-WS Style’ as Client Style.

Once you click the ‘Finish’ button it automatically generates all the required Java classes to provide you a set of Java APIs each of which corresponds with each Web Service API. Also it generates the Proxy class called ‘PublicReportServiceService’ like the below.

public class PublicReportServiceClient {   @WebServiceRef   private static PublicReportServiceService publicReportServiceService;   public static void main(String [] args) {     publicReportServiceService = new PublicReportServiceService();     PublicReportService publicReportService = publicReportServiceService.getPublicReportService();     // Add your code to call the desired methods.   } }

Now you can start writing your own code with the BI Publisher Web Service corresponding Java methods after the ‘Add your code to call….’ line.

Call BI Publisher Web Service

First, I’m going to use a simple API called ‘login(String username, String password)’ to test if the web service integration works. This API accepts two parameters, one is username and another is password. It passes those two values to the BI Publisher Server and returns a session token if the combination of the username and password is valid. You can use this session token later for any following actions such as running a report, scheduling a report, etc, without needing to be authenticated at every communication with the Server.

Anyway, so this ‘login()’ API is one of the BI Publisher Web Service APIs and I can call it through the Web Server Proxy. In the example below ‘publicReportService’ is the proxy, once this is instantiated then I can call it through the proxy ‘publicReportService’.

With the below example, I’m setting ‘Administrator’ for the username and ‘Administrator’ for the password, and trying to print the returned session token in the standard out.

public class PublicReportServiceClient {   @WebServiceRef   private static PublicReportServiceService publicReportServiceService;   public static void main(String [] args) throws AccessDeniedException_Exception,                                                   MalformedURLException {     publicReportServiceService = new PublicReportServiceService();     PublicReportService publicReportService = publicReportServiceService.getPublicReportService();     String sessionid = publicReportService.login("Administrator", "Administrator");     System.out.println(sessionid);       } }

When I run this program I get a session token like ‘6691223D7D070473ABA31D91AAB89’, which means the login was successful and I can use this token later when I call other BI Publisher Web Service APIs.

How to Change Endpoint?

Now, as long as I’m developing and testing in this instance I would be fine. But what if I need to migrate my code to a QA or Production and I need to run the BI Publisher calls against a different BI Publisher Server?

When you create the Web Server Proxy with JDeveloper it generates it with a default endpoint URL setting, which is retrieved from the WSDL file. That means, every time I run the BI Publisher Web Service it goes against a BI Publisher server with the endpoint URL. So I want to dynamically change the endpoint URL address instead of hard-coding it somewhere.

With 10G JDeveloper it generated an API that I can use to set the endpoint URL address. But with 11G JDeveloper I need to do that in a slight different way, which is to set that as part of the proxy construction. With my example, I can set that when I initiate ‘publicReportServiceService’. I need to provide a URL, which is the endpoint, and a QName, which consists of namespace and localname. My example, those are something like following.

• Endpoint URL: http://knishida-lap.us.oracle.com:7001/xmlpserver/services/PublicReportService
• Namespace: http://xmlns.oracle.com/oxp/service/PublicReportService
• Localname: PublicReportServiceService

You can find these information from your BI Publisher Server WSDL file, or you just need to change the hostname and port number of the example above. And you can set those values something like below.

Before:

publicReportServiceService = new PublicReportServiceService();

After:

//publicReportServiceService = new PublicReportServiceService(); publicReportServiceService = new PublicReportServiceService(new URL("http://knishida-lap.us.oracle.com:7001/xmlpserver/services/PublicReportService"), new QName("http://xmlns.oracle.com/oxp/service/PublicReportService", "PublicReportServiceService"));

Now you have a control of which BI Publisher Server instance you want to communicate with the Web Service calls. Great!

Test with Java Servlet

With the above example I was testing with a Java application. But for the next couple of posts I want to use a Java Servlet to demonstrate other APIs. In case you are not familiar with Java Servlet, don’t worry, you can create it with JDeveloper pretty easily. Just select ‘HTTP Servlet’ under ‘Web Tier/Servlet’ in the ‘New Gallery’ menu and follow the wizard steps.

Now I created a Java Servlet to test called ‘BipServletTest’ under the same project. I’m going to use this Servlet to go over the next couple of posts to introduce other BI Publisher Web Service APIs such as runReport, scheduleReport, etc, so stay tuned!

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!

Cheat Sheet for BI Publisher Integration with BIEE - Part 1

Do you still have BIEE and BI Publisher integration not working ?

This integration of BI Publisher and BIEE has been there for quite a while and I’m sure many of you have done this and enjoying the integration. But still today, I hear from the fields that some people out there having some challenge to set it up. Though all the configuration steps required for the integration are all documented in Oracle BI EE Infrastructure Installation and Configuration guide, I wanted to go through all the integration points briefly and talk about the best practice and configuration tips.

This post is rather for someone who have already gone though the Installation guide yet still facing some problem. This is not an introduction and I'm not covering the detail of the configuration steps so I can’t express enough but please make sure you have already gone through the documentation where you can find the detail information.

 

Security

You can integrate BI Server security model as BI Publisher’s security model. It is strongly recommended to use the BI Server as the BI Publisher’s security model if you need to integrate those two products.

Before you setup:

• Before you try to integrate the BI Server security make sure you have enabled ‘Super’ user in the BI Publisher’s Security page so that you can always login even when the security integration didn’t work.
• Test the connect string under JDBC data source before you use that for the Security Model setting.

Points to note:

• Once the integration is done now you manage all the users and groups in BI Server (RPD). That means you cannot add any user or group from BI Publisher’s Administrator web UI.
• All the groups of the BI Server will be shown as Roles in BI Publisher’s security page. In the Security page you need to assign appropriate report folders and data sources to each necessary role so that the users in the group can access to appropriate reports and run.
• You need to create those XMLP_xxx groups in BI Server (RPD). These groups should be already there if you have installed the BIEE suite, but may not with a standalone version or upgraded from the older releases. if you don’t see them then create them as groups and assign users to those groups to provide appropriate BI Publisher functionality.
• The port number for the JDBC connection is not 9704, it’s 9703 if you use the default configuration of BIEE. 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)
• There is not cascading group concept for the BI Publisher’s pre-built privilege (role) such as XMLP_ADMIN. For example, if you want to provide an Administrator role to a user A then you need to directly assign the user to the role called ‘XMLP_ADMIN’. Assigning a group, which belongs to ‘XMLP_ADMIN’, to the user A will not provide the Administrator’s role to the user.
• Though the XMLP_xxx roles don’t support the cascading group concept but other roles does, which you use to assign the users to appropriate report folders and data sources.

Troubleshoot:

Can’t Login!

Once you have setup BI Publisher to use BIEE’s security model you should be able to loging to BI Publisher with the BIEE user. However, there is one thing to note that BI Publisher has a different rule on which letters can be used for the password from the one for BIEE. So if you are using a special character in the BIEE password and getting a login error this is where you want to check.

Also make sure that the Connect String for the BI Server is correct.

 

Access to BI Publisher from BIEE - More Products

Though you can always access to BI Publisher from its own login page you can also access it from BIEE’s web UI by navigating from ‘More Products/BI Publisher’ menu.

Troubleshoot:

Getting an error when select BI Publisher from the menu!

This integration is the trickiest one based on what I have been seeing at our customer projects. In order for this to work you need to add Publisher Administrator credentials to Oracle BI Presentation Services Credential Store by using BI Server’s cryptotool.

When BI Presentation Services establishes a connection with BI Publisher it uses BIP's Administrator username and password stored with the alias 'bipublisheradmin' in the BI Presentation Services Credential Store. The Credential Store can be a XML file and the default file is credentialstore.xml, which can be found at %OracleBIData_Home%\web\config. The password is encrypted and stored in the Creadentail Store file.

In order to create (or update) the Credential Store file you can use a command line utility called cryptotools, which can be found at %ORACLEBI_HOME%\web\bin for Windows. This tool will add a pair of username and password into a creadentialstore.xml file.

The steps required for this are documented in the Installation and Configuration guide. But here is an example of how you can run the cryptotools utility to store the credentials.

Example:

Assuming your OBI admin user is, 'Administrator' and its password is 'Administrator', you can run the cryptotools utility to add 'bipublisheradmin' user alias like following.

C:\OracleBIData\web\config>cryptotools credstore -add -inFile credentialstore.xml -alias bipublisheradmin -username Administrator -password Administrator -passphrase admin

Once you run the above command you'll be asked the following two questions, answer 'y' to the both questions.

 

>Do you want to write the passphrase to the xml? y/n (n): y >File "credentialstore.xml" exists. Do you want to overwrite it? y/n (y):

Every time you change the Administrator’s password you need to run the cryptotools utility to store the new credentials to the credential store.

 

Tomorrow!

I will talk about other integration points that allow you to use BI Answers or BI Server as data source tomorrow, stay tuned!

Running Siebel Reports with Parameters

“I want to select or pick a value before running the reports and filter the reporting data for Siebel reporting.”

This is a very common requirement in reporting in general, which is to provide a parameter (or prompt) to the users, have them specify a value, and filter the returned data in the reports. With BI Publisher Server standalone UI, you can quickly create a parameter and run the reports. But how can we do with Siebel reporting ?

In Siebel you can select a particular row in the view window and run a report which will contain only the data related to the row. For example, in Account View, when you select a particular account and run the ‘Account List’ report it will show you only the selected account related information in the generated report. Also, Siebel reporting filters the data based on the user’s position, so depending on who run the report they will get a different set of the data.

These are all handled by Integration Objects, the reporting data extraction layer. But how about filtering the data even further ? With the above example, when I get a data related to a specific account I still want to filter the data so that I can see only the accounts who have purchased iPhone in the last quarter by passing a product name as a parameter value.

Siebel Reporting with BI Publisher had added this capability with 8.1.1.1, which is called ‘Parameterized Reporting’. Now you can provide a drop down box or free text form where the users can select or type a value and filter the reporting data result based on the parameter value.

There is only one limitation. You can’t make this parameter values dynamic. It’s common that you want to run a query to the database and retrieve the parameter values, like the way you do with BI Publisher Enterprise Server UI. But with Siebel this option is not available today. So basically you need to define the parameter values beforehand within the RTF template.

Anyway, today, one of our consultants, Shankar Duvvuri, who is our new BI Publisher expert especially in Siebel integration, has written an example scenario about how to setup and use the parameterized reports in Siebel and wants to share with you. So, Enjoy!

How to Create Parameterized Report

In this example, we want to create a report that shows revenue data and provide a drop down box parameter to the users so that they can select which quarter they want to see the revenue data for. So we are going to need a drop down box with values of ‘Q1’, ‘Q2’, ‘Q3’, and ‘Q4’ and have the users select one of the values before they run the report to filter the revenue data. So if a user selects a ‘Q1’ then only the revenue in Q1 will be displayed in the generated report.

 

Create Parameter in RTF Template

First, we need to create a parameter, which displays a pick-list of quarters (Q1,Q2,Q3,Q4) to the user, within the RTF template. Here is an example.

 

<?param@begin:Qrt; 'Q1', 'string', 'Q1,Q2,Q3,Q4'?>

You can either type this in the RTF template as text or use the text-form and type the above syntax inside the text-form.

In the above example, we are creating a parameter called Qrt with pick list values of: Q1,Q2,Q3 and Q4. The default value of the pick list is set to Q1. The parameters can be added anywhere in the template as either form field or free text. For more details about syntax and other types of parameters (like free text) refer to the Siebel Reports Guide.

If you want to make the parameter to be a free text form where the users can type the value instead of selecting from a list, then you can just ignore the parameter value section. So it would be something like below.

 <?param@begin:Qrt;'Q1';'string'?>

For free text, we are declaring Qrt as the parameter name with Q1 as the default. At run time this would display the parameter with name Qrt and default value Q1, which then can be changed to any value of users choice.

 

Use the Parameter to Filter Data in RTF Template

Once you have created the parameter as above, now you can use it to filter the data, to condition, or just to print the value. To reference the parameter in the report just prefix a $ before the name of the parameter.

In order to filter the data you can use the parameter inside the ‘for-each’ clause.

 

<?for-each:ListOf_ssRevenue[Quarter=$Qrt]?>

Having a default value in the template helps us simulate the testing with BI Publisher MS-Word Add-in. You can manually change the default value to test a different scenario for your condition or calculation.

Test the Report

Once the RTF template is ready, you can upload it to the Siebel Server using the Web Client.

If the report already exists then we need to delete the report first and then click on the Upload Files button. (Due to a defect we can’t override the existing template.)

Once the upload is done, run the report in the associated view. You will get a pop-up window (parameters applet) like below, which shows the newly added parameter.

Choose the one of the value and click ‘Submit’ button, you will get another window where you can select the report output type. Select the output type and submit, then you will get a report that only contains only the values that are related to the selected parameter value.

Enabling SSL for BI Publisher Standalone

How to enable SSL (Secure Sockets Layer) for my BI Publisher Server installed with OC4J standalone ?

SSL is a cryptographic protocols that provides a encrypted therefore secured communications over internet. This can be enabled for your BI Publisher Server to secure the communication between the server and the clients.

I’m sure many of you have installed BI Publisher as a part of Oracle BI EE suite or as just a BI Publisher Server standalone following the standard installation with the installer. The standard installation installs an embedded OC4J server and deploy BI Publisher Server on the OC4J server. In this deployment architecture you don’t have any HTTP server. OC4J Server directly interacts with clients http requests and returns response to the clients for the BI Publisher Server.

Today, I will talk about how to enable the SSL for such configuration, which is that BI Publisher Server standalone deployed on OC4J standalone. I will focus only on enabling SSL communication.

Here is a list of the brief steps that you need to perform.

• Create a Keystore
• Create and Update secure-web-site.xml
• Register the secure-web-site.xml in server.xml
• Test

Create a Keystore

A keystore stores certificates, including the certificates of all trusted parties, for use by a program. Through its keystore, an entity such as OC4J (for example) can authenticate other parties, as well as authenticate itself to other parties. In Java, a keystore is a java.security.KeyStore instance that you can create and manipulate using the keytool utility that is provided with the Sun Microsystems JDK. The underlying physical manifestation of this object is a file.

Here are the steps to create the keystore.

1. Change the directory to ORACLE_HOME/j2ee.

2. Create a keystore with an RSA private/public keypair using the keytool command. In this example, we generate a keystore to reside in a file named mykeystore, which has a password of 123456 and is valid for 365 days, using the RSA key pair generation algorithm with the following syntax:

% keytool -genkey -keyalg "RSA" -keystore mykeystore -storepass 123456 \ -validity 21

Note:

The keystore option sets the filename where the keys are stored.

The storepass option sets the password for protecting the keystore.

The validity option sets number of days the certificate is valid.

The keytool prompts you for more information, as follows:

What is your first and last name? [Unknown]: Kan Nishida What is the name of your organizational unit? [Unknown]: Consulting What is the name of your organization? [Unknown]: Oracle What is the name of your City or Locality? [Unknown]: New York What is the name of your State or Province? [Unknown]: NY What is the two-letter country code for this unit? [Unknown]: US Is <CN=Kan Nishida, OU=Consulting, O=Oracle, L=New York, ST=NY, C=US> correct? [no]: yes Enter key password for <mykey> (RETURN if same as keystore password): Always press RETURN for the key password. In the OC4J 10.1.3 implementation, the keystore password must be the same as the key entry password.

The mykeystore file is created in the current directory. The default alias of the key is mykey.

Create and Update secure-web-site.xml file

1. If you do not have a secure-web-site.xml file, copy the default-web-site.xml to ORACLE_HOME/j2ee/home/config/secure-web-site.xml (by convention).
   
2. Open the secure-web-site.xml and add secure="true" to the <web-site> element, as follows:
   
   

   <web-site port="4446" display-name="Default OracleAS Containers for J2EE Web Site" secure="true" > ... </web-site>

   
   
3. Change the <web-site> port setting to some available port. For example, port="4443". (To use the default of 443, you have to be a super user.)
   
4. Add the following under the <web-site> element to define the keystore and password.
   

   <ssl-config keystore="your_keystore" keystore-password="your_password" />

   
   Where your_keystore is the path to the keystore—either absolute, or relative to ORACLE_HOME/j2ee/home/config (where the Web site XML file is located)—and your_password is the keystore password. For example:
   

   <!-- Enable SSL --> <ssl-config keystore="../../keystore" keystore-password="123456"/>

Register secure-web-site.xml file in server.xml

Add the following line in server.xml:

<web-site path="./secure-web-site.xml" />

Save the changes and restart OC4J server to reflect the changes.

Access to BI Publisher Server for Testing

Now all the settings are done. Let’s test. You should be able to use ‘https’ to access to your BI Publisher.

https://hostname:port/xmlpserver where the port number is the one you set in the secure-web-site.xml file. e.g. https://knishida-lap.us.oracle3.com:4446/xmlpserver

Now OC4J listens for SSL requests on one port (in this example 4446) and non-SSL requests on another (the default is 9704). You can disable either SSL requests or non-SSL requests, by commenting out the appropriate *-web-site.xml pointer in the server.xml configuration file:

<web-site path="./secure-web-site.xml" /> - comment this to remove SSL <default-site path="./default-web-site.xml" /> - comment this to remove non-SSL

Conclusion

Well, it’s pretty simple right?. But of course if you want to really secure then you can do more in the configuration. Also if you have a http server involved, which is a typical architecture with Oracle Application Server, then it requires extra steps. You can take a look at OC4J Security Guide 10.1.3 for the detail. Also, if you need to integrate with Oracle BI EE, which is SSL enabled then there will be more steps, though they are documented in the Oracle BI EE Deployment Guide.

How to Migrate BI Publisher Reports to Production?

How can I migrate all the reports developed at a development instance to a testing or production instance with BI Publisher Server ?

This is very typical thing you as an administrator or reports developer would concern. The good news is, migrating BI Publisher reports from one instance to anther is pretty simple. All the reports are stored in a BI Publisher repository, which is typically on a file system or inside Oracle XML database. So, all you need to do is to copy the BI Publisher repository from the source system and paste it to the target system. Here is a diagram that shows the migration.

Of course you need to have BI Publisher server installed and up-and-running at the target system before the migration. Either if your repository is on the file system or XML DB, this process would be pretty much the same. I’ll talk about the process assuming that the repository is on the file system in this post.

What is BI Publisher Repository?

So what is really the BI Publisher repository. It stores the following types of contents and information.

• Reports
• Server Configuration
• Translation

BI Publisher Repository Screenshot

It not only stores the reports but also contains all the configuration related information. Let’s take a look at the detail for each. 

1. Reports

Under ‘Reports’ folder in the repository all the reports are organized in a folder based structure, which is in the same way that you see in the BI Publisher Enterprise server’s reports list UI page. Each report has its own folder, which contains one report definition file (.xdo) and report templates such as .rtf or .pdf.

1.1. Reports Definition

All the information for the BI Publisher reports are stored in the reports definition files which has a file extension as ‘.xdo’ It stores the information like sql query, report name, parameter name, list of values, template name, reports translation, bursting setting, report property, etc.

1.2. Reports Template

When you work on reports development most of the work is done within the report layout templates such as RTF Templates, PDF Templates, to design the reports layout and formatting. Once you complete the development you upload them to the BI Publisher Enterprise Server and register them as layout templates for each report. These uploaded report templates are also stored in the BI Publisher repository and resides along with the reports definition file (.xdo) in a same report folder.

2. Enterprise Server Configuration

All the BI Publisher server configuration related information is also stored in the BI Publisher repository. The configuration information includes the following.

• Server Configuration
• Security
• Scheduler
• Delivery
• Data Source

These information is stored under Admin folder within the repository.

3. Translation

There are two types of the translation. One is a translation for the BI Publisher Server product UI. BI Publisher Server supports 28 languages translations (includes English) for its UI. All the translated strings are stored in a set of XLIFF files and each language has its own XLIFF file to store the translation. These translations are located under Admin/Translation.

And another is a translation for the reports. If you are a French and open a report you want to see all the text are written in French. If you are a German you expect the same way. BI Publisher separates the translation from the report so you don’t need to create multiple reports for each language. Instead, you can just have one report and multiple translation XML files (XLIFF format), which contains only the translatable strings. These translations are located along with each report.

Now you get an idea of the BI Publisher Repository. Let’s start the migration.

 

Migration Process Overview

Before you start the migration, again, ensure the BI Publisher Server exists at a target instance! The migration process has two parts.

1. Migrate BI Publisher repository
2. Perform Post-Migration Configuration

1 Migrate BI Publisher Repository

First, we need to obtain the repository from the source instance. Then locate it to the target BI Publisher repository.

1.1 Obtain BI Publisher Repository from a Source Instance

The screen below shows the root directory of the BI Publisher repository

1. Identify the BI Publisher Repository at the source instance
   You can find this information in a BI Publisher’s system configuration file, which can be found at %J2EE_HOME%/applications/xmlpserver/xmlpserver/WEB-INF/xmlp-server-config.xml (e.g. C:\OracleBI\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF)

<?xml version="1.0" encoding="UTF-8"?> <xmlpConfig xmlns="http://xmlns.oracle.com/oxp/xmlp"> <resource> <file path="C:\OracleBI\xmlp\XMLP"/> </resource> </xmlpConfig>

1. Go to the BI Publisher Repository folder
   e.g. C:\OracleBI\xmlp\XMLP
2. Copy all the folders except ‘DemoFiles’ under the above folder. (If you want to migrate a set of demo XML data files you can copy the ‘DemoFiles’ folder as well.
3. Save the folders onto your local machine or check them into your source control system

1.2 Locate BI Publisher Repository Content to a Target Instance

1. Login to the target instance machine
2. Go to the BI Publisher Repository directory.
3. Copy the source repository folders, which are the ones you have copied at the above steps, to this folder.

1.3 Validate the Repository Migration

1. Restart your target J2EE server, such as Oracle OC4J server.
2. Login to the Target BI Publisher Enterprise Server

Once you have logged in, you should see all the directories and reports migrated from the source instance under Reports tab in the new target BI Publisher Enterprise Server.

Post-Migration Task at Target Instance

After the BI Publisher Repository has been migrated successfully you want to perform a post-migration task to review a target specific information. Often that the development application instance doesn’t share the same information such as data source, delivery system, scheduling information, etc with the target instance.

For example, when you have a project where you develop financial reports out of a financial database, usually you would have one financial database for development, one for Testing, and one for Production. And you would expect that each of the BI Publisher Enterprise Server instance would talk to each financial database but not share the same database among multiple BI Publisher servers. This is just an example for a data source but it can be applied to other settings such as follows.

• Data Source
• Security
• Scheduler
• Delivery

When you have completed the migration process all the above settings are just the copies from the source system so you want to review the settings and update them appropriately so that they can be specific to a target system.

When you do this review and need to update any of the information, it is strongly recommended that you do it through the BI Publisher Enterprise Server Admin UI, not through the manual file modification. Just to make sure you don’t accidentally broken the system.