Oracle BI Publisher and BI Discoverer Integration
Oracle BI Publisher and BI Discoverer Integration
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.
Approximately 30 minutes.
This tutorial covers the following topics:
Place
the cursor over this icon to load and view all
the screenshots for this tutorial. (Caution: This action loads all screenshots
simultaneously, so response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over
an individual icon in the following steps to load and view only the screenshot
associated with that step. You can hide an individual screenshot by clicking
it.
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. |
Back
to Topic List
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. |
Back to Topic List
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.
|
Back to Topic
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:
Back to Topic List
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 click
Select 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 > Brian subfolder
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.
|
Back to Topic
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.
|
Back to Topic
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.
|
Back to Topic
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 Region from 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, select Sales Sum from the list,
select sum from the Calculation pull-down, select
the On Grouping option, 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.
|
Back to Topic
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:
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. |
|
Back to Topic
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.
Back to Topic List
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.
|
Back to Topic
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.
|
Back to Topic
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:
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.
|
Back to Topic List
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. |
Back to Topic List
|
To learn more about Oracle BI
Discoverer, refer to additional OBEs on the OTN
Web site. From this page, select Business Intelligence Start
> Oracle BI Discoverer |
|
To learn more about Oracle BI Publisher, refer to additional
OBEs on the OTN
Web site. From this page, select Business Intelligence Start
> Oracle BI Publisher |
Back to Topic List
Place the cursor over this icon to hide all screenshots.
|
Bookmark
