Oracle BI Publisher and BI Discoverer Integration

Purpose

This tutorial covers integration features of Oracle BI Publisher (version 10.1.3.3) and Oracle BI Discoverer (version 10.1.2.3). You will learn how to access Discoverer Worksheet content as a data source for BI Publisher Desktop and BI Publisher Enterprise.

Overview

Oracle BI Publisher, release 10.1.3.3, provides several integration points for Oracle BI Discoverer, release 10.1.2.2. This integration functionality enables BI Publisher to access Discoverer worksheet content as a data source for BI Publisher reports.

Since the focus of this tutorial is the use of BI Publisher against Discoverer data, a short overview of the Discoverer worksheet environment provides some context for those unfamiliar with Oracle BI Discoverer.

What Is a Discoverer Worksheet?

A Discoverer worksheet is a container for an Oracle BI Discoverer report. The data for Discoverer reports may come from either Oracle relational or Oracle OLAP sources.

The Discoverer worksheet environment is ideal for creating reports that enable ad hoc and end-user analysis. Data elements, including measures, dimensions, and dimension members, may be dragged to and from the worksheet area to create tables, crosstabs, and graphs of business intelligence data.

Several worksheets may be collected together in a Discoverer workbook. In order to access the worksheets in a workbook, a Discoverer public connection must be created for the user who owns the workbook. This public connection is used as the connection mechanism to display the associated worksheets as data sources for BI Publisher.

The following Discoverer worksheet is used as the data source for BI Publisher reports that are created in this lesson.

For more information on Oracle BI Discoverer, see the Related information section.

Focus of This Tutorial

In this tutorial, you will use three integration features:

1.	Extend MS Word formatting and layouts to Discoverer worksheets by using BI Publisher Desktop.
2.	View reports of Discoverer data in BI Publisher Enterprise.
3.	Schedule, share, and distribute Discoverer reports by using BI Publisher Enterprise.

Prerequisites

Before starting this tutorial, you should:

1.	Have access to or have Installed Oracle Application Server 10.1.2.2. Then download and install the one-off Discoverer Patch. Go to the Oracle MetaLink ( http://metalink.oracle.com/ ) site to download patch # 5648158.
2.	Have access to or have Installed Oracle BI Discoverer 10.1.2.3.
3.	Have access to or have Installed Oracle BI Publisher Enterprise and BI Publisher Desktop, version 10.1.3.3. Note: BI Publisher Desktop requires a current installation of MS Word.
4.	Set up Discoverer Web Services and define its authorized users. Then, establish the BI Publisher Security Configuration by using BI Publisher Enterprise Security Center.

Connect to BI Discoverer from BI Publisher

As stated previously in the Prerequisites section, before you can connect to Discoverer from BI Publisher, you must first establish the Discoverer Web services and set up its authorized users. Then, the BI Publisher security configuration must be completed.
Finally, you configure BI Publisher to point to Discoverer as a data source by using the Integration feature found in BI Publisher Enterprise's Admin tab. Here, you provide the Discoverer server connection information and authentication for the Web services user.

Use the following steps:

1.	Launch BI Publisher Enterprise. A. In the address line of your browser, enter the BI Publisher Enterprise server string. B. In the Log In window, enter the appropriate username and password. C. Click Sign In.
2.	Select the Admin tab, and click the Oracle BI Discoverer link in the Integration section.
3.	On the Oracle BI Discoverer page, select or specify the following: A. Select http for the server protocol. Then, enter the server IP address and port number for the Discoverer Server. B. For the administrator username and password, enter the appropriate information for an authorized Discoverer Web services user. C. In the URL Suffix box, specify the Discoverer Web Services endpoint. D. Click Apply to save the connection information. Note: BI Publisher must be restarted in order to validate the Discoverer connection information.

Create a Report of Discoverer Data by Using BI Publisher Desktop

BI Publisher Desktop is an add-in to Microsoft Word. Once you install BI Publisher Desktop, MS Word can be used as the front end for any Oracle BI Publisher data source, including Discoverer.

You can apply all of the rich formatting and layout capabilities of MS Word to your Discoverer worksheets, and apply multiple templates per Discoverer worksheet. Then, you can save and view the resulting report in any of the following formats: PDF, RTF, XLS, PPT, or XML.

In this topic, you create a BI Publisher report from a Discoverer Worksheet and view it in PDF format. Perform the following tasks:

	Select a Discoverer Worksheet as a Data Source
	Create a BI Publisher Template
	Preview the BI Publisher Report
	Modify the Template
	Add Charts to the Template

Select a Discoverer Worksheet as a Data Source

1.	Launch MS Word. The Oracle BI Publisher menu and toolbar are displayed.
2.	Log on to BI Publisher: A. From the Oracle BI Publisher menu, select Log On. B. In the BI Publisher Login window, enter the appropriate information for the username, password, and BI Publisher Report Server. C. Click Login. Result: The Open Template window appears.
3.	In the Open Template window, select a connection for Discoverer by performing the following: A. Select Discoverer from the Workspace drop-down list. Result: The Select Data Connection window appears. B. In the Select Data Connection window, select the desired Discoverer public connection, and clickSelect Connection. In the illustration, the BIP connection is selected. Result: A list of workspaces that are associated with the Discoverer connection appear as drillable folders in the Open Template navigation pane. C. Select the desired workbook folder to display its associated worksheets in the Reports list. For example, the BIPI_CrosstabLayouts workbook contains three worksheets. D. Double-click the desired worksheet, and, in the Save As Oracle BI Publisher Report window, select the BI Publisher user folder location for the report. In the example, the ItemsOnTop worksheet was selected, and the My Folders > Briansubfolder is selected as the BI Publisher report location. E. Click Save. This action saves a description of the Discoverer worksheet in the BI Publisher catalog. It also automatically loads the data elements of the worksheet as the source for the current report in BI Publisher Desktop. Your MS Word document is automatically prepared for report template development, using the Discoverer worksheet contents as the data source. The report name is automatically generated using the format Workbook.Worksheet from the Discoverer connection.

Create a BI Publisher Template

Once the Discoverer worksheet is set up as the data source for a report, the next task is to create a template for the report. Once you have created the template, you can preview or publish the resulting report.

In this task, you use the Template Builder menu to create a report template for the Discoverer worksheet data. Follow these steps:

1.	On the Template Builder toolbar, select Insert > Table Wizard.
2.	In the opening step of the Table Wizard, select Table as the report format, and click Next.
3.	In Step 2 of the wizard, accept ROWSET/ROW as the Grouping Field value, and click Next.
4.	In Step 3 of the wizard, select all of the available items (Year, City, Region, Profit Sum, Sales Sum) by clicking the Select All button (>>). Then, click Next.
5.	In Step 4 of the wizard, select Region in the Group By box. Then, click Finish. Note: If you had selected Next in Step 4, the following two steps of the wizard enable you to specify sort order and customized labels for the table. Result: A simple BI Publisher report template is inserted into the MS Word document.

Preview the BI Publisher Report

You can preview BI Publisher Desktop reports in any of the following formats: PDF, HTML, EXCEL, RTF, and PowerPoint.

In this task, you preview the report in PDF format:

1.	On the Template Builder toolbar, select Preview > PDF. In order to preview the report in PDF, BI Publisher requires that you first save the template locally in RTF format. Therefore, the following window appears. Click OK. Result: The Save As window appears.
2.	In the Save As window, specify both the location and the name of the template.
3.	Click Save to save the template and display the report preview. The report appears in PDF format. You can scroll through the report by using the normal features of your PDF editor.
4.	Close the PDF preview.

Modify the Template

In this task, you apply MS Word formatting and layout techniques to the template in order to enhance the display of the report. In addition, you add new data fields to the template to enhance the report content.

1.	Create a page header for the report: A. On the MS Word menu bar, select View > Header Footer. In the Header box, enter the following title for the report: Regional Sales by Year & City. Center the text within the header box, and make the text bold. B. Add any other desired formatting to the header. For example, a graphic image that displays "Oracle Business Intelligence" is inserted into the header, just above the report title. C. Add page numbers to the report footer by using the Header and Footer toolbar. For example: D. Click Close on the Header and Footer toolbar.
2.	Modify the text formatting of both the Group By header and the table body. A. In the report, select the Group By header (Region). Then, apply the Heading 2 format to the header as shown below. B. Next, select the table body and change the text in table body to 10 point, Arial. The template should now look something like this:
3.	Between the Group By header and the table, add an additional header that displays the sales figure for each region. A. Add a blank line between the Group By header (Region) and the table body. Then, enter the text "Revenue for " in the line under the header. Note: The cursor should be located just to the right of the space that follows "Revenue for ". B. Select Insert > Field from the Template Builder menu. Then, in the Field window, select Regionfrom the list, and click Insert. Result: A new field is inserted at the cursor location. Click Close to close the Field window. C. After the new Region field, add the following text: " is $". D. Once again, select Insert > Field from the Template Builder menu. In the Field window, selectSales Sum from the list, select sum from the Calculation pull-down, select the On Groupingoption, and click Insert. Result: A new field is added to the template. Click Close to exit the Field window. E. Double-click the sum Sales_SUM field to display the BI Publisher Properties window. Then, select the #,##0.00 option from the Format drop-down list. Click OK to apply the format. The additional group header should now look like this:
4.	Preview the report in PDF format by using the same technique described in Preview the BI Publisher Report. The report should look like this: Scroll down to view the footer formatting. Close the PDF preview.

Add Charts to the Template

In this task, you add two charts to the template: one focusing on Sales and the other on Profit.

1.	Add the first chart for Sales. A. Place your cursor at the beginning of the line that contains the group ROW by Region field and press Enter to add a new line above the table. B. From the Template Builder menu, select Insert > Chart. In the Chart window, perform the following: Drag Sales Sum from the Data box to the Values box. A representation of the chart appears in the preview box. Drag Region to the Labels box. Note: The Series box is enabled when a value is placed in the Labels box. Drag Year to the Series box. Enter Sales in the Title box. C. Click Preview to update the graph inside the preview box. Note: You can change the graph Type and Style to modify the look and feel of your chart. In addition, you can modify the appearance of the graph by changing the 3D and Gradient options. D. Click OK to insert the chart. The template should now include the chart, like this: E. Resize and center the chart. To do this, select the chart and use the grabber controls to reduce its size. Then, click the center tool on the MS Word Formatting toolbar. The resulting size and position of the chart should look something like this:
2.	Add a second chart to the template: A. Select the Sales chart, and click the right mouse key once. This action places the cursor insertion point just to the right of the chart. B. From the Template Builder menu, select Insert > Chart. In the Chart window, perform the following: Drag Profit Sum from the Data box to the Values box. Drag Region to the Labels box. Drag Year to the Series box. Enter Profit in the Title box. Click OK. Result: A second chart appears in the report template. Note: The chart is the same size as the first chart before you reduced it. Since it cannot fit on the same line, the chart is pushed down to the next line. C. Resize the new chart so that it is approximately the same size as the first. As you do this, the second chart automatically appears on the same line as the first, just to its right. The template should now look something like this:
3.	Preview the modified report. A. From the Template Builder menu, select Preview > PDF. B. Scroll through the report, and you see that the charts appear only at the top of the report, and not on each page. C. Close the PDF preview.

View Discoverer Reports by Using BI Publisher Enterprise

When a BI Publisher template is ready for production, you make the report available to BI Publisher Enterprise by uploading the template.

In this topic, you upload the template and view the report in BI Publisher Enterprise.

	Upload the Template
	View the Report in BI Publisher Enterprise

Upload the Template

Use the following steps to upload the template from BI Publisher Desktop to BI Publisher Enterprise:

1.	From the Oracle BI Publisher menu, on the MS Word menu bar (not the Template Builder toolbar), select Upload Template As.
2.	In the "Upload as new" window, enter an appropriate name for the template, and click OK. Note: If you modify the template later, you can upload the changes to BI Publisher Enterprise by selecting Update Layout Template from the Oracle BI Publisher menu on the MS Word menu bar. This option is added to the menu after you upload the template.
3.	After the template has been successfully uploaded, a confirmation message appears: Click OK.
4.	Exit MS Word.

View the Report in BI Publisher Enterprise

Follow these steps to access and view the report.

1.	Launch BI Publisher Enterprise, enter the appropriate login information, and click Sign In.
2.	Click the appropriate folder link to locate your report. In this case My Folders > Brian.
3.	Click the report link. In this case, ...ItemsOnTopAxis. Result: The template name for the report appears.
4.	The report can be viewed in the following formats: Click View to display the report.

Schedule Discoverer Reports by Using BI Publisher Enterprise

Reports based on Discoverer data can also be scheduled by using the BI Publisher Enterprise Schedule feature. In this topic, you learn how to schedule and distribute reports of Discoverer data.
The Schedule feature enables specification of the following information:

	Report parameters, such as template name and output format.
	Job properties, such as job name, report locale, and report time
	Notification parameters, if the email delivery option is enabled.
	Run-time options.
	Destination delivery options.

Follow these steps to schedule the report of Discoverer data:

1.	With the report open in BI Publisher Enterprise, click the Schedule button.
2.	In the Schedule Report window, specify or select the following: A. In the Report Parameters section, select the desired Template and Format for the report. B. In the Job Properties section, specify an appropriate job name for the scheduling job, and select the other options that meet your specifications. C. In the Notification section, enter one or more email addresses and specify when you want a notification to be sent. Note: This section is optional. D. In the Time section, select the appropriate run time. In the example, Run Monthly is selected. Then, specify the secondary options for the selected run time. E. In the Destination section, select the desired option. If you have more than one choice configured for a destination type, then you may select from among those choices. F. Click Submit.
3.	Once your report job has been scheduled, you can choose to suspend, resume, or delete the job at any time.

Summary

This course showed you how to:

	Connect to BI Discoverer from BI Publisher.
	Create a report of Discoverer data by using BI Publisher Desktop.
	View Discoverer reports by using BI Publisher Enterprise.
	Schedule Discoverer reports by using BI Publisher Enterprise.