Creating Business Intelligence Dashboard Using Discoverer Portlets
Creating a Business Intelligence Dashboard Using Discoverer
Portlets
This tutorial explains the steps in creating a Business Intelligence
(BI) dashboard by publishing Discoverer portlets on a portal page, using OracleAS
Portal.
Approximately 60 minutes
This tutorial covers the following topics:
Place the cursor over this icon to load and view all
the screenshots for this tutorial. (Caution: Because this action loads all screenshots
simultaneously, response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over
each individual icon in the following steps to load and view only the screenshot
associated with that step.
Before starting this tutorial, you should have:
| 1. |
Installed Oracle10g Database
with sample schemas, because this OBE uses the SH (Sales History) schema.
|
| 2. |
Installed Oracle Business Intelligence (with the components
Discoverer Plus Relational, Discoverer Plus OLAP, and Discoverer Viewer) |
| 3. |
Installed the
OracleBI Sample workbooks.
Note: This OBE illustrates
how to publish the BI content on the executive dashboard using the sample
workbooks. If you haven't already installed
the OracleBI Sample Workbooks, the instructions to install these can
be found
here.
|
| 4. |
Installed Oracle Application Server (Infrastructure,
and OracleAS Portal), and associated OracleBI Discoverer with the Infrastructure
database of the Oracle Application Server. |
| 5. |
Created public connections to the bi_user schema
(relational), and Scott schema (OLAP), (where you have installed the sample
workbooks) to publish portlets. |
| 6. |
Registered Discoverer Portlet Provider with OracleAS
Portal. |
Back to Topic
List
Oracle Business Intelligence Discoverer is an intuitive ad-hoc
query, reporting, analysis, and Web-publishing tool that provides business users
with immediate access to information from data marts, data warehouses, online
transaction processing (OLTP) systems, online analytical processing (OLAP) systems,
and Oracle Applications. Oracle Application Server Portal is the industry's
most complete and pre-integrated out-of-the-box portal solution, which enables
companies to quickly build, administer, and deploy enterprise portals.
The integration of OracleBI Discoverer with OracleAS
Portal enables the BI content to be shared with a wide spectrum of users such
as customers, company executives, and partners. This integration especially
helps the BI Analysts and/or BI developers to create executive dashboards (portal
pages), which consist of reports to monitor and understand the company performance
at a glance. Also, executive dashboards can be tailored to the needs of the
executives who use them. For example, the executives in the Sales department
would like to see the reports such as profit margins by product category in
each region, or the other sales trends; where as the executives in the HR department
would like to see the reports such as employee leave balance for the year, average
head count in a year, attrition rate in the company, and so on. In this tutorial,
you will learn how to create such executive dashboards by publishing Discoverer
reports on a portal page.
Back to Topic List
Electronics & More sells several categories of electronic
products such as computer hardware, peripherals, cameras, and camcorders. The
company does high volumes of business, operates a number of stores worldwide,
and sells its products through several channels, including the Internet. The
company loads data from the transactional systems into its data warehouse regularly.
BI analysts generate reports on a regular basis. These include
reports on sales and profits by time, geography, and distribution channels through
which its sales are delivered. The top executives have realized that, though
the company achieved record sales and profits in the year 1998, profit margins
for the years 1999 and 2000 reached alarmingly low levels, though sales revenues
continued to rise.
The executives want to perform an analysis on the same. So
they asked the BI analysts to create an executive dashboard, so that the sales
executives such as Vice President - Sales, regional sales managers, and others
can glance through these reports to understand and analyze the sales and profits
trends. You are one of the BI analysts for this company,
and want to know how to publish the Discoverer reports on the BI dashboard,
to be used by the sales executives. You will follow the steps given in the OBE
to publish the data in the sample workbooks to the BI dashboard.
A sample BI dashboard created with
Discoverer portlets is shown here, and the topics in this OBE will guide you
to create a similar one:
Back to Topic List
To begin the activity of publishing the BI content on a portal
page, you first need to add Discoverer portlets, to the regions defined in the
portal page.
Note: It is assumed that
the learner has the basic working knowledge of OracleAS Portal, and knows how
to create portal page groups, pages, and regions in portal pages. Also, for
proceeding with this exercise, you should have associated OracleBI Discoverer
with the Infrastructure database of the Oracle Application Server, and you should
have registered the Discoverer Portlet Provider with OracleAS Portal.
About Discoverer Portlet Provider: Portlets
form the fundamental building blocks of a portal page. Portlets
are areas within a portal page that display the information from a datasource.
The portlets in a portal page are provided by entities known as portlet
providers, which are registered with OracleAS
Portal. A portlet provider is an entity that owns and manages one or more portlets.
OracleBI Discoverer is a portlet provider to OracleAS Portal. It provides three
types of Discoverer portlets:
 |
Discoverer List of Worksheets
Portlet: This portlet contains the names
of (or the URL links to) Discoverer workbooks and the worksheets that
are created using Discoverer Plus (or Discoverer Desktop). The availability
of the Discoverer workbooks to the user depends on the Discoverer connection
(the database and user credentials) that is used to publish the portlet.
When you click a worksheet link, it opens the selected worksheet in Discoverer
Viewer for further analysis. |
|
Discoverer Worksheet Portlet:
A worksheet portlet allows you to place the actual worksheet content on
a portal page. A worksheet portlet can contain data (a table or a crosstab
report), a graph, or both.
|
|
Discoverer Gauges Portlet:
This is a new type of portlet provided by
the current version of OracleBI Discoverer. Discoverer Gauges portlets
typically display a range of values.
More information on gauges is given in the topic
Publishing Discoverer Gauges Portlet. |
Follow the steps listed below to add a Discoverer Portlet
to the portal page:
| 1. |
In the Web browser, enter
the URL for OracleAS Portal. The URL looks like the one given below:
http://<Host name>:<Port>/pls/portal
On the home page, click the Login
link (highlighted in the image below)
|
| 2. |
Log in to OracleAS Portal using the SSO
user (bi_user here) credentials.
Note: Contact the portal administrator
for the SSO user credentials.
|
| 3. |
The default page for this user is displayed.
Click the
Edit link to view the page in edit mode. The Graphical
view of the portal page is displayed.
Note: The Default page can be set by the Portal administrator,
by editing the SSO user profile. The page layout may slightly vary from
the image shown here, based on how the portal administrator has created
this page while doing the setup. A sample page is used in the screens,
and also this OBE concentrates more on the steps for publishing Discoverer
portlets, rather than designing portal pages.
Also, note that when you click the Edit
link on the portal page, the Graphical
view of the page is displayed. You can also edit the page from the Layout
view of the page. A portion of the BI dashboard is shown below. Observe
the options for Editing Views ( highlighted
in the image):
|
| 4. |
Note that this page has already regions
added for portlets. Click the add portlet icon 
in a desired region to add portlet to that region.
Note: The region to which the portlet
will be added is highlighted in the image below:
Note: You can add regions to the
left, right, below, or above a region. To do this, use the add region
(Left, Right, Below , and Above) icons 
available for a region. Also, you can edit a region using this icon
 , and you can
set the type of region to contain Portlets, or Items and so on. Observe
the image below:
|
| 5. |
When you click the add portlet icon of a
region, the
Available Portlets page is displayed. On the Available
Portlets page, click the link Portlet
Staging Area. This will display a list of newly registered providers.
|
| 6. |
Click the link for the Discoverer Portlet
Provider (named as
Discoverer Provider in the image)
that has been registered for you.
Note: Take the help of your Discoverer
manager or portal administrator, for the name of the Discoverer Portlet
Provider registered for you.
|
| 7. |
This will display the three types of portlets offered
by Discoverer. Click the link for a Discoverer portlet to add it to
the Selected Portlets list. In the
screen shown below, a Discoverer List of
Worksheets portlet has been selected to be added to the portal
page.
Note: Similarly, you can add more
regions, and different types of Discoverer portlets (such as Worksheet
portlets, and Gauges portlets) in those regions depending on how you
want to design the dashboard. The Layout
view of the sample dashboard page after adding the Discoverer portlets
is shown below. You can create a similar page by adding different types
of Discoverer portlets following the steps listed in this topic.
|
Back to Topic List
You have just completed the activity of adding Discoverer
portlets. You should have observed that the portlets will not display any data
because they are not defined. You can define (publish) these portlets by editing
the defaults for the portlets. Follow the steps listed below, to edit the defaults
for a List of Worksheets portlet.
Note: To proceed with this exercise, the Discoverer manager
should have created a public connection to the database where sample workbooks
are installed. In the exercise below, a public connection bi_user
is used, which connects to a relational database where the sample workbooks
are installed. Also, note that the steps for publishing data from an OLAP datasource
also remain the same, except that you need to create a public connection to
the OLAP database where samples are installed.
| 1. |
Click the Edit
Defaults icon 
or the Edit Defaults link on the List
of Worksheets portlet (highlighted in the image) from the Graphical
view of the page. This will invoke the Edit
Portlet Defaults wizard.
|
| 2. |
In the first step of the Edit
Portlet Defaults wizard, define the Database
Connections to be used to display the
data in the portlet. Select the public connection (bi_user
is used here) created by the Discoverer manager for you as the Publisher's
Connection.
|
| 3. |
Accept the default values for the Users
Logged In and User Not Logged In
sections, and click Next.
|
| 4. |
In the next step, select All
Worksheets from Selected Workbooks from the Worksheet
Selections drop-down list, and also select the option Allow
the User to Change the Selected Workbooks (see the images below).
Note: You can also select to display
only Specific Worksheets. Click the
 sign next to
the workbook to see all the worksheets in a particular workbook, and
select the desired ones. You can also focus on a particular workbook,
to view the worksheets in that workbook, by clicking the focus icon
 next to the workbook
name.
|
| 5. |
Select Sales &Profits
by Time & Geography, and Sales&Profits
by Time, Geography, & Channel workbooks from the list. Click
Next.
This will display all the worksheets from the selected sample workbook
in the List of Worksheets portlet.
|
| 6. |
The Portlet Options
page of the wizard is displayed. In the Display
Options section, enter Electronics
and More Sales Worksheets as the Portlet
Title, select the option Show
All (to show all the worksheets), and deselect the check boxes
Description and Creation
Date not to display these columns in the list.
|
| 7. |
In the Analyze Options
section, select the options
Allow saving changes made in Viewer, and
Launch Worksheet Viewer in a new window.
Click Next.
Note: These options will enable you
to open the worksheet in a new window when you click the Analyze
link after the portlet is published, and will also allow the users to
analyze, edit, and save the worksheets.
|
| 8. |
In the next step, from the Refresh
Options page, select to refresh the portlet once every day and
click Next.
|
| 9. |
Review Portlet Defaults
in the next step, and click Finish.
Note: To change any of the options
that you have selected, use the Back
link.
|
| 10. |
Click the Refresh Page link
in the portlet to display data. Note that the portlet may take some
time to fetch the data.
The list of worksheets portlet is displayed as shown below:
|
Back to Topic List
In this exercise, you will follow the steps given below
to publish the content of a Discoverer Worksheet on portlet.
Note: If you have not already added a Discoverer Worksheet
portlet, you can do so by following the steps mentioned in the topic
Adding Discoverer Portlets.
| 1. |
Click the
Edit Defaults icon
or the Edit Defaults link on the worksheets
portlet (highlighted in the image) from the Graphical
view of the page. This will invoke the Edit
Portlet Defaults wizard for the worksheet portlet.
|
| 2. |
In the first step of the Edit
Portlet Defaults wizard, define the Database
Connections to be used to display the
data in the portlet. Select the public connection created by
the Discoverer manager for you as the Publisher's
Connection. Accept the default connections for the Users
Logged In and User Not Logged In
sections, and click Next.
|
| 3. |
In the next step, select the worksheet to be displayed.
Expand the node for the Sales &Profits
by Time & Geography workbook,
and select the Profit Margins by Country
worksheet. Click
Next.
|
| 4. |
The Parameter Values
page appears. The default value for the Region
parameter (Americas) is displayed
in the drop down list. Click the the search icon  beside
the drop-down list for the
Country parameter (highlighted in the image below). This will
display the list of countries in the Americas
region.
Note:
This step will be skipped if the worksheet selected does not contain
any parameters.
|
| 5. |
Move Canada and United
States Of America from the Available
Values list to the Selected Values
list by using the move icon  .
Click Select.
|
| 6. |
This will take you back to the Parameter
Values page of the wizard. Note that the values that you selected
for the parameters are reflected in this page. Select the option to
Allow logged in users to customize parameter values.
Click Next.
Note: This OBE does not concentrate
on generic Discoverer portlet parameters. However, you can also map
Discoverer worksheet parameters with generic Discoverer portlet parameters
in the Page Parameter Mapping section.
A generic Discoverer portlet parameter can be defined using OracleAS
Portal, and can be used by multiple Discoverer portlets on the same
portal page. For example, you might create two Discoverer Worksheet
portlets in a page to display two different worksheets that both contain
a Year parameter. You can map the Year parameter in both the worksheet
portlets to the same generic portlet parameter. If a user selects 2001
as the value for year, both the Discoverer Worksheet portlets are updated
to display the data for the year 2001. Observe the images shown below,
to understand the behavior of these parameters:
|
| 7. |
This will take you to the Portlet
Options page of the wizard. In the Display
Options section, enter Profit Margins
by Country as the Portlet Title,
and select Both as the Portlet
Content option, to display both crosstab and graph in the portlet.
Also, deselect the options Title and
Text Area of the worksheet not to
display these in the portlet.
Note: Alternately, you can choose
to display Title and Text Area of the worksheet too.
Observe the other Display Options
to set the number of rows
and columns to be displayed in the portlet .
|
| 8. |
You can also set Analyze Options,
to allow the users perform further analysis using Discoverer Viewer.
In the Analyze Options section of
the Portlet Options page, select options
to Allow user to analyze the worksheet in
Viewer,
Allow saving changes made in Viewer, and also to Launch
Worksheet Viewer in a new window. Click
Next.
|
| 9. |
In the next step, from the Refresh
Options page, select to refresh the portlet once every day and
click Next.
|
| 10. |
Review Portlet Defaults
in the next step, and click Finish.
Note: To change any of the options
that you have selected, use the Back
link.
|
| 11. |
Click the Refresh Page
link in the portlet to display data. Note that the portlet may take
some time to fetch the data.
The worksheet portlet is displayed as shown below:
Note: Similarly,
you can publish other worksheet data following these steps. Publish
the Regional Sales and Profits by
Year worksheet from the same workbook
following the steps listed here.
Similarly, you can also publish an OLAP worksheet Product
Category Performance from the sample OLAP workbook Corporate
Profitability using a public connection to the Scott's schema,
where OLAP sample workbooks are installed. Check with the portal administrator
for the name of public connection to the OLAP datasource.
Also, note that you can customize the look and feel of the worksheet
data as well as the graph to suit your requirements. Customizing Discoverer
portlets is covered later in this OBE.
|
Back to Topic List
Gauges are typically used to depict a range of data
values graphically on an executive dashboard. The value ranges depict the key
performance indicators (KPIs) for a business, hence gauges are very useful and
are widely used in executive dashboards. 
Gauges provide business-critical information
from back-end systems to the managers and executives, who drive organizational
performance, just like the way automobile-critical
information is presented to the driver, by the
gauges in the dashboard such as speedometer, fuelmeter, tachometer, and so on.
Gauges on an executive dashboard provide sharper and more convenient visibility
into factors ranging from profitability to customer satisfaction, on which monthly,
quarterly, annual, and long-term corporate performance depends. The current
version of Discoverer supports gauges portlets, there by enabling the BI analysts
and developers to build BI dashboards, which help in monitoring business performance
(business performance management). The image here shows a sample gauge.
Follow the steps given below, to publish a gauges portlet.
Note that the first few steps remain the same as those for publishing other
types of portlets.
| 1. |
Click the
Edit Defaults icon
or the Edit Defaults link on the List
of Worksheets portlet (highlighted in the image) from the Graphical
view of the page. This will invoke the Edit
Portlet Defaults wizard.
|
| 2. |
In the first step of the wizard, define
the Database Connections
to be used to display the data in the portlet. Select the public
connection created by the Discoverer manager for you as the Publisher's
Connection. Accept the default connections for the Users
Logged In and User Not Logged In
sections, and click Next.
|
| 3. |
In the next step, select the worksheet for which you
want to define the gauge(s). Expand the node for the Sales
&Profits by Time & Geography workbook,
and select the (crosstab) worksheet Regional
Profit Margins. Click Next.
Note: Gauges can be defined for crosstabs
only.
|
| 4. |
In this step, from the Gauge
page of the wizard, define options for the gauge portlet. The
crosstab that you selected to define gauge(s) is displayed in the
Data Options section. Click the Hide
Crosstab link to hide the crosstab, and display only the gauge
in the portlet.
Note that only a portion of the Gauge page
of the wizard is shown here.
|
| 5. |
Select Profit SUM as
the Measure (drop-down
list) from the Data for Gauge section.
Observe the components of a Gauge, depicted as an image in the Data
for Gauge section (highlighted in the image below).
Also,
select Column
Items, and Row Items to be
represented in the gauges portlet. Click this icon beside
the Column Items , and move Sales
Revenue SUM from the Available Values
list to the Selected Values list.
Click Apply. Observe the images shown
below:
Similarly, select Americas, Asia, Europe,
and
Oceania as the Row Items.
Note:
The number of gauges in the gauge set is determined by the number of
row and column items selected (that is, number of gauges = number of
row items X number of column items).
|
| 6. |
Also, select
Default as the option
from the Minimum Value and Maximum
Value drop-down
lists in
the Range
section.
Note: You can use one of the following options for
defining the maximum and minimum values.
Default: Use this option when you
want to go by the default values.
Map to measure: Use this option when
you have two measures in the crosstab, and you want to select the second
measure.
Specify: Use this option when you
want all the gauges in the set to have the same maximum/minimum values.
|
| 7. |
Now specify the thresholds for gauges. Select the option
to Enable Thresholds, and also select
the option to define Two Thresholds.
Select Specify from the drop-down
lists for both Low Threshold and High
Threshold to define these values. Enter 400000
as the value for Low Threshold and
1500000 as
the value for High threshold.
Take the help of the images shown here:
|
| 8. |
In the Display Options
section, define the Gauge Width/Height
as 200, select Arrow
as the Indicator Type, and also select
the option to Apply Threshold Colors to Arrow.
Also, in the Customization
section, select the options to Allow
Users to Customize Data Options, and Allow
User to Customize Display Options. Click
Next.
See the image below to take a look at all the Gauge
options that you selected:
|
| 9. |
The Portlet Options
page of the wizard is displayed. In the Display
Options section, enter Profits by
Region as the Portlet Title.
Deselect the check boxes for Title
and Text Area, not to display these
from the worksheet as the Portlet Content.
Also, from the Analyze Options section,
select Allow user to analyze worksheet in
Viewer, and Launch Worksheet in a
new window options. Click Next.
|
| 10. |
The Refresh Options
page of the wizard is displayed. Select to refresh data once every day,
and click Next.
Note: You can also change the First
Refresh Time for the portlet, if you want to refresh the portlet
at a specific time. By default, the system time is reflected in this
field.
|
| 11. |
Review Portlet Defaults
that you selected in all the steps, and click Finish.
You can also change these options, by using the Back
link.
|
| 12. |
Click the Refresh Page
link in the portlet to display data. Note that the portlet may take
some time to fetch the data and display the gauges.
The gauges portlet that you have defined is displayed as shown below:
Note: You can customize to reduce
the size of the gauges, so that they fit better in the portal page.
Customization is covered in the next topic. You can publish some more
gauges portlet following the steps given in this topic. Use the Sales
Revenues by Year worksheet to create another gauges portlet,
select the Sales Revenue SUM as the
Measure as well as the Column
Item, and years 1998, 1999, 2000,
and
2001 as the Row Items.
|
Back to Topic List
After publishing the portlets, administrators or end
users (in this scenario, Sales Executives) may want to customize the Discoverer
reports published on a dashboard, to change the look and feel, and other options.
In this topic, you will perform the steps listed, to customize the Discoverer
portlets that you published on BI dashboard.
Note: However, this
topic doesn't concentrate on customizing database connections because, this
type of customization of portlets is more appropriate in the following scenarios:
when the data (in the workbooks/worksheets) differs based on single sign-on
(SSO) username, or if the data differs based on database username. In other
words, when the data is striped based on either the SSO user name or the database
username (Virtual Private Database). In these cases, a separate query will be
run per user. However, both these scenarios are beyond the scope of discussion
for this OBE.
Customizing Discoverer
List of Worksheets Portlet
| 1. |
To customize the Discoverer
List of Worksheets portlet (named Electronics
and More Sales Worksheets), click the Customize
link on the portlet. The Customize Portlet
screen is displayed.
Note that the Update
button is not shown in the Database Connections
section. If you want to allow the end users to change the database
connection, then you have to select one of the following options, while
editing defaults for the portlet:
Display different data using the Publisher's
connection: If the data striping
is based on SSO user
Display different data by allowing users
to customize database connection: If the data striping is based
on database user
These options are highlighted in the image below, which displays a portion
of the first step of Edit Portlet Defaults
wizard. However, as mentioned earlier, this OBE concentrates on other
customization aspects of Discoverer portlets.
|
| 2. |
Click Update
in the List of Worksheets section.
This will display the list of all workbooks available. Deselect the
Sales & Profits by Time, Geography, &
Channel checkbox, not to display this workbook in the list. Click
Continue. This will take you back
to the Customize Portlet screen.
Note: End users can change the list
of workbooks to be displayed in the list, only if you have selected
the Allow users to change selected workbooks
option
, while editing the defaults for the portlet.
|
| 3. |
In the Portlet Options
section, click Update. This will display
all the portlet options that you can customize. Change the Portlet
Title to Electronics and More Sales
Worksheets-Relational. Observe the other options. Click Continue.
|
| 4. |
In the Refresh Options
section of the Customize Portlet screen,
click the link Refresh Portlet Now,
so that the portlet reflects the customizations that you have done.
Click Apply.
After the customization, the list of worksheets portlet will appear
as shown below:
Note: You can also click
the Restore Default Customizations
link on the Customize
Portlet screen, if you do not want
the customizations that you have done to be reflected in the portlet.
|
Back to Topic
Customizing Discoverer
Worksheet Portlet
| 1. |
The first step for customizing any Discoverer
portlet remains the same as that for list of worksheets portlet. Click
the Customize link on the portlet
to display the Customize Portlet screen
for the Profit Margins by Country
worksheet portlet that you have published before.
|
| 2. |
Click Update
in the Parameters section, to customize
parameter values for this worksheet portlet.
Note: End users will be allowed to
perform parameter customizations, only if you have selected the Allow
logged in users to customize parameter
values option, while editing the defaults for the portlet.
|
| 3. |
The Parameter Values
screen appears. Click the search icon
beside Country parameter, and remove
Canada from the Selected
Values list, to see the data for United
States of America only. Click Select.
See the images below:
This will take you back to the Parameter
Values screen. Click Continue.
Note: You can also select a different
value for the Region parameter (such
as Europe) and select the countries in Europe, for which you want to
see the data.
|
| 4. |
Click Update in the
Format section, to change the worksheet
portlet formats. The Format page appears.
Click the Format link.
Holding the Shift key down, click
the bars below the column headers for Sales
Revenue SUM, and Profit
| |
Bookmark
