This module describes how to use Oracle Business Intelligence
Discoverer Viewer (OracleBI Discoverer Viewer) to open and view relational workbooks
created by using Discoverer Plus or Discoverer Desktop. This module also describes
how you can perform operations, such as sorting, pivoting, formatting, drilling,
stoplighting, and exporting on worksheets.
Place the cursor on this icon to display all the screenshots in the tutorial.
You can also place the cursor on each individual icon in the following steps
to see only the screenshot that is associated with that step.
Prerequisites
In order for this lessons to work successfully, the
OracleBI Sample workbooks must be installed.
The OracleBI Sample workbooks help you learn how to
use the product, and this lessons illustrate key features of OracleBI Discoverer
Viewer with the help of these sample workbooks. If the Sample workbooks are
not yet installed, follow the instructions found here.
From the
Connect To drop-down list, you can select the following options based
on whether you want to connect to a standard Discoverer End User Layer
(EUL), an Oracle Applications EUL, or an OLAP catalog, respectively.
OracleBI Discoverer
Oracle Applications
OracleBI Discoverer for OLAP
3.
If you want to connect to a standard Discoverer EUL, you must specify
the username, password, database connect string, the EUL name, and the
locale.
4.
If you want to connect to an Oracle Applications EUL, you must specify
the username, password, database connect string, EUL name, and Oracle
Applications responsibility.
5.
If you want to connect to an OLAP catalog to perform multidimensional
analysis, you must specify the username, password, database connect string,
and locale.
6.
Select OracleBI Discoverer from the Connect To drop-down
list.
7.
Enter bi_user as the username.
8.
Enter bi_user as the password.
9.
Enter the appropriate database connect string.
10.
Enter BI_USER as the End User Layer name. (Note: The EUL
name should be entered in uppercase letters.)
Select Database Workbooks from the Search drop-down
list and then enter Profits in the text box next to it. Click
Go.
This displays a list of workbooks with names that contain Profits.
2.
To display a list of worksheets that a workbook contains, you click the
plus() sign on the left
side of the workbook name. Click the plus ()
sign on the left side of the Sales & Profits by Time & Geography
workbook.
The workbook has four worksheets in it.
3.
You can focus in on a workbook to list only that workbook along with
the list of worksheets it contains, on the Worksheet List page. To focus
in on a workbook, click the
icon next to the workbook name. Click the
icon next to Sales & Profits by Time & Geography workbook.
4.
To open a worksheet, click the
sign next to the workbook name, and then click the worksheet name in the
list that is displayed. Click the Regional Sales & Profits by Year
worksheet to open it.
The Query Progress page appears indicating that the query is in progress,
and finally the worksheet opens. Click the Workbooks link to go
back to the Worksheet List page.
Opening a scheduled workbook
You can schedule long-running reports to run overnight or
at a time when there is relatively low network and resource usage. You can also
schedule workbooks to run repetitively. For example, you can schedule a workbook
to run every Sunday at 12 midnight, so that the report is ready when you come
on Monday morning.
To open a scheduled workbook, select Scheduled Workbooks
from the Search drop-down list, and then click Go.
A list of all the scheduled workbooks is displayed.
Opening a shared
scheduled workbook
Not only can the process of running large queries or of
content that is required to be periodically refreshed be automated, Discoverer
can amplify these benefits by enabling you to share these scheduled workbooks
with other users.
You can share the results of a scheduled workbook with
another user by using Discoverer Plus. After the scheduled workbook is
ready with the results, it is also displayed on the Worksheet List page
along with other workbooks. In the screenshot, the Sales & Profits
by Time & Geography - Scheduled workbook has been scheduled and
shared as is clear from the owner's name.
Items in a worksheet are placed on the Page axis, Top
axis, or the Side axis. You can pivot the worksheet items to any of these axes
depending on how you want the data to be displayed.
1.
Select All Workbooks, and then click Go.
2.
Click the icon next
to the Sales & Profits by Time & Geography workbook. The
list of worksheets it contains is displayed.
3.
Click the Regional Sales & Profits by Year worksheet to open
it.
4.
The worksheet displays the Sales Revenue and Profit for each region per
year. You want to view the same information for all years per region.
To do this, you can move the Region item to the page axis and the Year
item to the top axis. To pivot the items, click Layout.
5.
Click More to display the Table Layout page.
6.
Click the
icon beside the Year item to move the Year item to the top axis. Click
the icon beside
the Region item to move the Region item to the page axis.
Click Apply. The worksheet now displays the Sales Revenue and
Profit data for all years per Region.
7.
Select Europe from the Region drop-down list to view the Sales
Revenue and Profit data for Europe.
Numeric items in a worksheet with tabular layout can
be sorted in ascending or descending order. Character data can be sorted in
alphabetical order.
1.
Click Layout, and then move the Region item to the top
axis on the right of the Year item. (Select Move > Region > Left
of Sales Revenue SUM > Go).
To sort worksheet items, click Sort. Click More
to display the Multiple Sort page. Select Year from the Column
Header drop-down in the First Sort group of drop-down lists. Select A
- Z as the sort order. Select Group under Sort Type.
2.
In the Second Sort group of drop-down lists, select Sales Revenue
SUM from the Column Header drop-down list and Descending from
the Sort Order drop-down list.
Click Apply. From the worksheet data, you can infer that the Americas
region has the largest Sales Revenue SUM value for all the years.
You can apply different formatting to worksheet items
or values of worksheet items to highlight them in a worksheet. Formatting includes
applying bold, italic, or underline styles and changing background or font color.
1.
Click Format to display the formatting toolbar.
2.
The formatting toolbar appears.
To format an item, select the item from the worksheet, and then click
the appropriate button on the formatting toolbar. To select an item, click
any of the values of that item.
3.
Click inside the Sales Revenue SUM and the Profit SUM columns
while pressing and holding the Shift key to select both columns.
Now click icon on the
formatting toolbar. You can select the entire column by clicking the bar
below the item heading.
4.
The values in the Sales Revenue SUM and Profit SUM columns appear in
bold.
Similarly, you can change the background color and font color.
Creating Conditional Formats and Applying
Stoplights
Applying conditional formatting to items in the worksheet
enables you to display and easily identify exception data. When applied on an
item, the item is formatted as specified.
Stoplight formatting enables you to quickly and easily define thresholds that
automatically format worksheet data to indicate whether a particular value falls
into the Desirable, Acceptable, or Unacceptable ranges established by the thresholds
defined.
1.
On the formatting toolbar, click Create Conditional
Format.
2.
The Create Conditional Cell Format page appears. Specify Profit SUM
Format as the name for the conditional format. Under Selections, select
Profit SUM from the Item drop-down list. Select Greater Than
(>) from the Operator drop-down list. Enter 1500000 in the
value field.
3.
Under Format, select a background color and a font color from the respective
color palette.
Click Apply.
4.
The Profit SUM values that are greater than the specified value appear
with the formatting specified.
5.
You can also apply stoplight formatting to categorize the worksheet values
into Desirable, Acceptable, or Unacceptable values. Click Stoplight
from the Tools section.
6.
The Stoplight toolbar appears. Select Sales Revenue SUM from
the Format drop-down list.
7.
In the Unacceptable field, enter 1000000.
8.
In the Desirable field, enter 9500000.
9.
Click Go. The values in the Sales Revenue SUM column are formatted
as Unacceptable, Acceptable, or Desirable.
10.
Save the workbook. To save the workbook, click Save under Actions.
To save the workbook under a different name, you can click Save As. After
you have made changes to a worksheet, if you want to restore the original
copy, click Revert to saved.
You can dynamically filter data in a worksheet by creating
parameters in Discoverer Plus. When you open the worksheet in Discoverer Viewer,
you are prompted to select a value for the parameter. You can also set up cascading
parameters, wherein the available values for the second parameter depend on
the values that you select for the first parameter. For example, if you have
two parameters (one for Region and another for Country), you can set up the
parameters in such a way that the list of countries that are available will
be based on the value that was selected for the parameter set for Region.
1.
Open the Profit Margins by Country worksheet.
The Parameters Needed page appears.
2.
Click the icon
beside the region parameter. The "Search and Select: <parameter
name>" page appears. Notice that Americas is already selected.
This is the default value of the parameter that was defined while creating
the parameter in Discoverer. Click Select.
3.
Click the icon
beside the country parameter. The "Search and Select: <parameter
name>" page appears. Notice that the list of countries displayed
in the Available Values pane are the countries that belong to the Americas
region — the value selected for the region parameter.
Click Cancel.
4.
Click the icon
beside the region parameter. On the "Search and Select: <parameter
name>" page, move Americas to the available list and move
Asia to the Selected list.
Click Select.
5.
Now click the
icon beside the country parameter. The list of countries that appear in
the Available list are the countries that belong to the Asia region —
the value selected for the region parameter. This is because the two parameters
that are set up are chained together, so that the value of the first parameter
is used to filter the values for the second parameter.
Click Cancel.
6.
Select Americas as the value for the region parameter and Canada,
United States of America as the value for the country parameter.
7.
Click Go. The worksheet displays data for the countries Canada
and United States of America in the Americas region.
Drilling
You can perform drill operations in a worksheet to see detailed
or summarized data. You can drill down on an item in a worksheet to see the
next level of data. For example, you can drill down from the Year item to display
quarterly information. To see summarized data, you can drill up from an item
to one level above it. For example, you can drill up from Product Subcategory
to Product Category to display summarized information for product categories.
Drill hierarchies need to be defined by the Discoverer manager in the EUL, for
you to perform Drill operations.
Open the Regional Profit Margins worksheet by
selecting the worksheet name from the Worksheets list.
2.
Swap the Region and Year items. To do this, click Layout. Select
Swap > Year > With Region (Americas,...).
Click Go.
3.
The worksheet now displays the Sales, Profit, and Profit Margin information
for the Americas Region for all years.
You can see that the Profit Margin gradually decreased through the years
1998-2000. But increased in the year 2001. Now you may want to drill down
to display quarterly information.
4.
Click beside the
Year item, and then select Quarter from the list of items displayed.
This list of items is the one that you can drill down to. The Discoverer
manager has defined this hierarchy, which enables you to drill down.
The worksheet now displays quarterly information also. From the worksheet
data, you can identify that there was an increase of around 9% in Profit
Margin from Q4 2000 to Q1 2001.
5.
Now you may want to analyze how each channel of sales has performed over
these years. You can drill to the Channel Class item from Quarter, even
though there is no hierarchy created to satisfy such a drill. This feature
is called "drill to a related item". Click the
icon beside the Quarter item, and then select Drill to Related.
A list of items that you can drill down to is displayed. Select Channel
Class from the list.
The worksheet now displays Channel information also.
6.
Not all rows are displayed because the current setting allows only 25
rows and 25 columns to be displayed. To display all the rows, you need
to increase the rows displayed per page. To do this, click the Rows
and Columns link under Tools. Enter 100 as the value for Rows,
and then click Go.
7.
Now swap Quarter and Channel Class items. To do this, click the Layout
link under Tools, and then select Move > Channel Class >
Left of Quarter. Click Go.
Now you can analyze the profit generated through each sales channel for
each quarter of every year.
8.
Observe the values for the year 2001. Compared with that in the previous
years, the Others channel has shown an increased contribution for this
year. This is more evident if you drill up from Quarter to Year.
9.
Click the icon beside
the Quarter item, and select Year.
10.
Now the worksheet displays yearly information about performance of each
channel. Clearly, the Others channel showed an increase in growth by around
6% between the years 2000 and 2001.
The visual presentation of data helps end users
to easily see trends and abnormalities in their data. In addition to providing
a powerful and visual representation of the data from your database, graphs
also provide a quick and easy way to analyze data over time and over different
geographies.
1.
Open the Sales & Profits by Time & Geography
workbook. Open the Sales Vs Profits by Year worksheet.
This worksheet displays Sales Revenue SUM and Profit SUM for each region
for all years.
2.
Scroll down to display the graph, and then select Americas for
the Region page item. The graph displays the Sales Revenue SUM and Profit
SUM for Americas region.
Notice that in the year 2000, the Profit declined even though the sales
increased.
3.
Now, you may want to drill down to quarterly information for the year
2001. Click the year 2001 on the X-axis of the graph.
The graph now displays quarterly information about Sales and Profits
for the year 2001.
4.
In the worksheet, click the
icon beside Quarter, and then select Channel Class from the Drill
to Related list.
The worksheet now shows quarterly sales and profits information for each
channel.
5.
Drill and replace Quarter with Year. Click the
icon beside Quarter, and then select Year.
The worksheet and graph now display yearly sales and profit information
for all channels.
6.
You can also change the graph type and other settings for the graph.
Click Graph under Tools.
The graph toolbar appears.
7.
Select Bar as the graph type, Dual-Y Bar as the graph subtype,
and click Go.
8.
Deselect the 3D Effect checkbox.
9.
Enter 600 as the graph width and 500 as the graph height,
and then click Go.
10.
Compare the bars for 2000, Others and 2001, Others.
Discoverer Viewer enables you to print your worksheets and
graphs with high fidelity and control. You can use Discoverer Viewer's print
options and page setup to specify how your worksheet should print. A preview
of the worksheet as it would be printed helps you to change your print options.
Discoverer Viewer utilizes the popular Adobe Acrobat ® PDF format to generate
a high fidelity output of your worksheet, which you can then print.
1.
Click Printable Page under Actions.
The Printable Page Options page appears.
2.
Click Preview Sample to preview the page to be printed.
This generates a sample PDF document.
3.
Click Open PDF to view sample. You need to have Acrobat Reader
installed on your machine to see the sample PDF.
4.
You can change the print and page settings by clicking Print Options.
The Printable Page Options page appears.
5.
Click Page Setup.
You can change settings related to the page size, page margins, scaling,
and column width here.
6.
After you have changed the setting according to your requirements, click
Printable PDF.
7.
The PDF file is generated. Click Click to view or save. The PDF
file appears in a different window.
Click File > Print to print the PDF file.
8.
Click Return to worksheet to close the
preview page.
You can export a Discoverer worksheet to numerous formats.
Some of the common formats are HTML and Microsoft Excel. In addition, there
are two new formats that you can export a Discoverer worksheet to: Portable
Document Format (.pdf) and Web Query for Microsoft Excel 2000+ (.iqy).
There are two ways in which you can export a Discoverer worksheet
to Excel format. You can export a table or a crosstab worksheet to Excel format.
Also, you can export a crosstab worksheet to Microsoft Excel Pivot Table format.
Exporting to Microsoft Excel Pivot format creates an Excel Pivot table that
preserves the layout in your Discoverer worksheet. You can then toggle values
of the page items and pivot the items by modifying the pivot table.
1.
To export to Excel format, click Export under
Actions.
The Choose Export Type page appears, which lists all the formats that
you can export a Discoverer worksheet to.
2.
Select Microsoft Excel Workbook (*.xls) from the "Use the
drop-down list to specify the export file format." List
Click Export.
3.
The Export Ready page appears. Click the Click to view or save
button.
The worksheet is exported to Excel format, and it opens in your browser
window.
4.
Click Return to worksheet to close the Export Wizard.
5.
Now click Export, under Actions.
The Choose Export Type page appears, which lists all the formats that
you can export a Discoverer worksheet to.
6.
Select Microsoft Excel Workbook with Pivot Table (*.xls) from
the "Use the drop-down list to specify the export file format."
List
Click Export.
7.
The Export Ready page appears. Click the Click to view or save
button.
The worksheet is exported to Excel format, and it opens in your browser
window.
8.
The Excel workbook that is displayed has three worksheets. Clickthe
Sales Vs Profits by Year_Base0 worksheet.
This worksheet contains the entire data of the Discoverer query.
9.
Now Click the Sales Vs Profits by Year worksheet. This worksheet
contains the Excel Pivot table with the same layout as the Discoverer
worksheet you exported.
10.
Click the Region drop-down list. All the values of the Region
item are displayed. Select Americas from the list, and then click
OK.
The worksheet displays the data for the Americas region.
11.
Now click the Sales Vs Profits by Year_Static worksheet.
This worksheet is the current snapshot of the Discoverer worksheet at
the time of exporting.
Exporting a Discoverer worksheet to portable document format
(PDF) enables high fidelity printing of Discoverer worksheets and graphs.
1.
To export to PDF format, click Export under Actions.
The Choose Export Type page appears, which lists all the formats that
you can export a Discoverer worksheet to.
2.
Select Portable Document Format (PDF) (*.pdf) from the "Use
the drop-down list to specify the export file format." List
Click Export.
3.
The Export Ready page appears. Click the Click to view or save
button.
The worksheet is exported to PDF format, and it opens in your browser
window. You must have the Acrobat Reader plug-in installed to view the
exported PDF document. Observe that the formatting of the Discoverer worksheet
is retained as it is.
4.
Click Return to worksheet to close the Export Wizard.
Microsoft Excel Web Query format is an external data format
in Microsoft Excel that enables you to dynamically include data from an Internet
or intranet URL (for example, a Discoverer worksheet) in a Microsoft Excel worksheet.
You can export your Discoverer worksheet to Microsoft Excel Web Query format.
When you open the exported file in Microsoft Excel, it uses a reference to the
Discoverer worksheet so that the data can be refreshed automatically in the
Excel spreadsheet.
1.
To export to Excel Web Query format, click Export
under Actions.
The Choose Export Type page appears, which lists all the formats that
you can export a Discoverer worksheet to.
2.
Select Web Query for Microsoft Excel 2000+ (*.iqy) from the "Use
the drop-down list to specify the export file format." List
.
3.
Select "Yes, require Excel users to enter connection information"
under 'Do you want to Prompt Excel users for connection information?'
so that the user information does not get saved. Everytime the user opens
the Excel worksheet, the username, password, and database information
will be prompted for.
4.
In the Page Items section, you can specify whether or not Excel should
prompt the user for the value of the page items. You can select the items
for which you want Excel to prompt for values and leave the others unselected.
While exporting, the current values for those items are preserved. Select
Region, and then click Export.
5.
The Export Ready page appears. Click the Click to view or save
button.
The worksheet is exported to Excel Web Query format, and it opens with
Excel.
6.
Excel prompts you for the username, password, database information, and
all the page item values one after the other.
7.
After you have supplied all these values, the data is fetched and displayed
in the Excel worksheet.
In Discoverer Viewer, you can specify preferences to control
how the query is run and how the data is displayed. You can navigate to the
Preferences page from anywhere in Discoverer Viewer, by clicking the Preferences
button or Preferences link. You can specify the values for the following preferences
on this page.
Query Governor
Measure Unit
Axis Label
Summary Data
Fan Trap Detection
To change preferences, perform the following steps:
1.
Click Preferences at the top of the page. The
Preferences page appears.
2.
Set the option values by selecting the appropriate check boxes or option
buttons. Enter values in the fields where applicable. Click Apply to update
the changes.
Just like you invoke pages of any other Web site, Discoverer
Viewer workbooks and worksheets can be invoked through a URL from the Web browser.
Copy the URL from the browser's address bar when the worksheet is displayed
and paste it on your personal portal. When the worksheet is invoked from the
URL, the database is queried and the latest information is displayed.
When a Discoverer worksheet is displayed, you can use the
bookmarking facility of your browser to bookmark the URL of the worksheet. Later,
you can launch the worksheet by selecting the bookmark.
After you have prepared a Discoverer worksheet, you can e-mail
it to your audience who want to study the data and perform analysis. You can
e-mail your worksheet as an attachment in any of the common formats such as
HTML, Microsoft Excel, Adobe Acrobat PDF, or even Web Query for Excel.
1.
To e-mail a worksheet, click Send as email under
Actions.
The "Choose attachment type" page appears.
2.
Select the attachment type as Portable Document Format PDF (*.pdf),
and then click Next.
The Send Email page appears.
3.
Fill out the sender, recipient, and subject information. Type any message
that you want to add in the body field, and then click Finish.
The SMTP host name should be specified in the middle tier earlier.
Summary
In this lesson, you should have learned how to use Discoverer
Viewer to open and view relational workbooks. You should have also learned various
features of Discoverer Viewer.
Bookmark
OracleBI Discoverer
) sign on the left
side of the workbook name. Click the plus (
icon next to the workbook name. Click the 
icon beside the Year item to move the Year item to the top axis. Click
the 
icon beside
the Region item to move the Region item to the page axis.
icon on the
formatting toolbar. You can select the entire column by clicking the bar
below the item heading.
icon
beside the region parameter. The "Search and Select: <parameter
name>" page appears. Notice that Americas is already selected.
This is the default value of the parameter that was defined while creating
the parameter in Discoverer. Click Select.
beside the
Year item, and then select Quarter from the list of items displayed.
This list of items is the one that you can drill down to. The Discoverer
manager has defined this hierarchy, which enables you to drill down.
