Exploring the New Features of OracleBI Discoverer Plus

Exploring the New Features of OracleBI Discoverer Plus Relational

This lesson describes, with examples, how to create and edit reports by using the new or enhanced features of Oracle Business Intelligence Discoverer Plus Relational.

Topics

This lesson discusses the following new features with examples:

An Overview of OracleBI Discoverer Plus
Enhanced Connectivity and New Worksheet Wizard

Enhanced Interactivity

Enhanced Graph Features
Intuitive UI to Add a Calculation

Enhanced Sorting and Formatting Features

Enhanced Drill Features and Parameters to Analyze Data
Enhanced Printing
New Export Formats to Export a Worksheet
Enhanced Workbook Scheduling/ Sharing
Summary

For a typical learner, it will approximately take an hour to successfully complete all the tasks listed in this lesson.

In order to complete this module successfully, you should have:

1.

Worked with Discoverer Plus and Discoverer Viewer to create and view reports

2. Completed the hands-on exercise from the OBE on Discoverer Viewer

In order for this lesson to work successfully, you should have performed the following tasks:

1.

Installed the Oracle10g Database with sample schemas, because this OBE uses the SH (Sales History) schema

2.

Installed OracleBI Discoverer Plus and OracleBI Discoverer Viewer

3.

Installed OracleBI Discoverer Administrator to set up and access the EUL

4. Installed the OracleBI Sample workbooks
Note: These workbooks help you learn how to use the product. This OBE illustr
ates how to explore the new features of OracleBI Discoverer with the help of these sample workbooks. If you haven't already installed the OracleBI Sample Workbooks, the instructions to install these workbooks can be found here.

 

 

Move your mouse over this icon to show all screenshots. You can also move your mouse over each individual icon to see only the screenshot associated with it.


An Overview of OracleBI Discoverer Plus

The earlier versions of Discoverer supported querying only relational data sources. The current version of Discoverer supports querying both relational and multidimensional OLAP data sources. Discoverer Plus OLAP is used to query multidimensional OLAP data sources, whereas Discoverer Plus Relational is used to query the relational data sources. This lesson primarily covers the new features of Discoverer Plus Relational. Also, note that in this OBE, the terms Discoverer Plus and Discoverer Plus Relational are used interchangeably.

Using Discoverer Plus Relational, you can create, modify, and analyze reports. The current version of Discoverer Plus has a new look and feel, and an improved and interactive user interface with many new features. The new interface enables the BI analysts to create reports interactively with a few clicks. The new or enhanced features of Discoverer Plus include improved wizards, interactive item navigators, enhanced graph features, enhanced formatting features, cascading parameters, enhanced printing and export features, and so on.

In this lesson, you learn how to use the new features of OracleBI Discoverer Plus to create and modify reports interactively by using a relational data source and sample workbooks.

 

Back to Topic List

 

Enhanced Connectivity and New Worksheet Wizard

Back to Topic List

This topic demonstrates the above mentioned new features of Discoverer Plus. To understand these features, you will connect to Discoverer Plus, open an existing sample workbook, and also create a simple new worksheet by using the new Worksheet Wizard.

 

 

Connecting to Discoverer Plus using "Connect Directly" Option

Back to Subtopic List

In addition to the public and private connections supported in the earlier versions of Discoverer, you can also connect to Discoverer Plus by providing the login details directly without having to create a connection.
Note: Now, you can access both OLAP and relational data sources from the same connections page of OracleBI Discoverer.

 

1.

In the Web browser, enter the URL for Discoverer Plus. This will display the Connect to OracleBI Discoverer screen. The URL looks like the one given below:

http://<hostname>:<port>/discoverer/plus

Move your mouse over this icon to see the image
2.

Enter the login credentials for the bi_user in the Connect Directly section. Enter the username and password, and the database connect string as shown. Enter the EUL name in uppercase (BI_USER), and click Go. This will connect you to Discoverer Plus.

 


Move your mouse over this icon to see the image

Note: Alternately, you can choose from the list of predefined connections. You can also create a private connection by using the login credentials for the database that you want to connect to. Also, note that when you have a stand-alone BI installation, which is not associated with the infrastructure database of the Oracle Application Server, you will see only the Connect Directly option, not the connections. To associate the BI installation with the infrastructure database, contact the middle-tier administrator.

Opening an Existing Workbook

Back to Subtopic List

In this exercise you'll open an existing sample workbook. Observe the enhanced UI, which includes options such as search for workbook names, and so on.
Note: Open the same sample workbook as mentioned in this exercise, because you'll be adding a worksheet to this workbook in the next topic.

1.

When you connect to Discoverer Plus, the first step of the Workbook Wizard is displayed. Select the Open an Existing Workbook option, and click Browse.

Move your mouse over this icon to see the image

This will display the list of all workbooks.

 

2.

The Name Contains field can be used to filter the list of workbooks. Enter Sales in the Name Contains field to display only those workbooks, whose name contains Sales.

Move your mouse over this icon to see the image

Note: You can select to View all workbooks or scheduled workbooks, by selecting the appropriate options from the View drop-down list. Also, you can select the list of workbooks displayed as a Workbook Tree or User Tree as shown in the images below. Observe the improved interface.

Move your mouse over this icon to see the image

Move your mouse over this icon to see the image

3.

Expand the sign next to the workbook Sales &Profits by Time & Geography to see all the worksheets, and select the worksheet Sales Vs Profits By Year, and click Open. This will open the worksheet in Discoverer Plus.

Move your mouse over this icon to see the image

The worksheet is shown below. Observe the new look and feel.

Move your mouse over this icon to see the image

Creating a New Worksheet Using the New Worksheet Wizard

Back to Subtopic List

In this exercise, you will create a simple tabular worksheet by using the new Worksheet Wizard. This sheet will be added to the workbook that you opened in the previous topic.

Note: You can create a new workbook using the Workbook Wizard, from the File > New menu option. Also, you can select the option to Create a new Workbook (default option), when you connect to Discoverer Plus.

1.

Select Edit > Add Worksheet to invoke the new Worksheet Wizard. Deselect the Title, Text Area, and Graph options. Select Table as the worksheet layout, and click Next.

 

Move your mouse over this icon to see the image

Move your mouse over this icon to see the image

 


2.

In step 2 of the wizard, you select items to be included in the worksheet. Observe the new Search icon, which enables you to search for required folders. Click the Find icon.

Move your mouse over this icon to see the image

 

3 .

This opens a Find dialog box. Select Contains as the Search by criterion, and enter Profits in the Search For field. Click Go. This displays two folders Profits and Profits Analysis. Select the Profits Analysis folder. Click OK.

Move your mouse over this icon to see the image

4 .

You can see that Profits Analysis folder is selected in the step 2 of the wizard. Select the Sales Revenue, Profit, Year, and Region items by pressing and holding the Ctrl key. Click to move these items to the Selected list, and click Next.

Move your mouse over this icon to see the image

You can choose to display or hide the folders in the Selected list by clicking the Views icon.

Move your mouse over this icon to see the image

Also, note that you can create Conditions and Calculations from the respective pages while creating the worksheet. These can be defined later while you edit the worksheet too.

Move your mouse over this icon to see the image

 

5.

Step 3 of the Workbook Wizard is displayed. Retain the default options and click Finish.
Note: The next two steps of defining sorts and parameters are skipped here, because this is a simple worksheet. These can be defined later.

Move your mouse over this icon to see the image

 

6.

This is how the worksheet you created looks like:

Move your mouse over this icon to see the image

Note that the steps in creating a new workbook by using the Workbook Wizard are similar to these steps.

7.

Select the Edit > Rename Worksheet menu option. This displays the new Worksheet Properties dialog box. Enter the name of the worksheet as Regional Sales and Profits By Year-New and add a Description (optional). Click OK.

Move your mouse over this icon to see the image

Move your mouse over this icon to see the image

 

8 .

Click Save icon to save the workbook. Enter Sales&Profits By Time &Geography -XX as the name of the workbook, where XX are your initials, so that you do not overwrite the sample workbook. Click Save.

Move your mouse over this icon to see the image

 

 

 

Enhanced Interactivity

Back to Topic List

Discoverer Plus now has an improved and interactive interface to perform many actions such as pivoting and adding items, changing worksheet properties, and so on. These tasks can now be performed with few mouse clicks. In this exercise, you will use this interactive interface to edit the worksheet by:

Editing Worksheet Layout and Pivoting Items in the Worksheet

Back to Subtopic List

 

Open the worksheet Regional Sales and Profits By Year-New from the workbook Sales&Profits By Time &Geography -XX, that you created in the previous exercise, and perform the following steps.

1.

Select the Edit > Worksheet menu option. Open the Worksheet Layout page, select the option to display Page Items, and click OK.

Note: Observe the other options in this page to display graph, title, and text in the worksheet.

Move your mouse over this icon to see the image

Move your mouse over this icon to see the image

Now the Worksheet will look like this with an option to display the page items.

Move your mouse over this icon to see the image

2.

To arrange the items in this worksheet better, pivot the Year item to the page axis by dragging this item to the page axis. Alternately, you can select the appropriate option to pivot this item from the shortcut menu as shown in the image below.

Move your mouse over this icon to see the image

Similarly, move the Region item to place it before the Sales Revenue SUM item. After pivoting Year to the page axis, and placing Region before Sales Revenue SUM, this is how the worksheet will look:

Move your mouse over this icon to see the image

You can see that the worksheet items are arranged better now, and also that pivoting can be done interactively.


3 .

Add suitable data formats to the items in the worksheet. For example, you can apply currency formats to Sales Revenue SUM and Profit SUM items.

Note: You can apply the appropriate currency format by selecting the Format Data option from the shortcut menu. In the Format Data Dialog box, select appropriate currency options as shown below.

Move your mouse over this icon to see the image

After applying these formats, the worksheet will look like this:

Move your mouse over this icon to see the image

 

Changing Gridline Colors from Worksheet Properties

Back to Subtopic List

You can change the worksheet properties such as the name of the worksheet and the colors of the gridlines from the Worksheet Properties dialog box. This new functionality enables you to define these properties for each worksheet. To change the gridline colors, perform the following steps:

1.

Open the Worksheet Properties dialog box. This can be done from the shortcut menu which appears when you click the worksheet name.

Move your mouse over this icon to see the image

Alternately, this dialog box can be opened by selecting Edit > Rename Worksheet.


2.

Go to the Table Format tab, and click the Gridline Color drop-down list and select a color for the gridlines in the worksheet. Click OK.

 

Move your mouse over this icon to see the image

Note: Observe the enhanced interface and other options in this page to display gridlines, column headings, row numbers, and NULL values.


3 .

Now the worksheet looks like this: (Observe the change in the gridline color.)

Move your mouse over this icon to see the image

 

Adding or Removing Items in a Worksheet

Back to Subtopic List

You can now add items to the worksheet in an intuitive and interactive fashion in Discoverer Plus, without having to go through wizards. The new Discoverer Plus has item navigator with Available items pane, Selected Items pane, and other options to add (or remove) items to the worksheet. You can now add items to the worksheets interactively, by dragging and dropping the item on the worksheet, or using the tool bar above the Available Items pane, or from the right mouse menu of the item. Similarly, you can also remove the item by using any of these methods.
Note: The right mouse menu option is demonstrated here. You can also try other options.

1. Observe the item navigator with Available Items pane and Selected Items pane in the image below.

 

Move your mouse over this icon to see the image

 

 

2.

To add Prod Category item, you can right-click the item and select Add to Worksheet from the shortcut menu. You can also do this by selecting the required item to be added, and by clicking the
Add to Worksheet icon (highlighted in the image below). Alternately, you can drag the item to be added from the Available Items pane, and drop it on the worksheet.

Move your mouse over this icon to see the image


3 .

The worksheet with the newly added item is shown below. By default new items are added at the end. However, in the worksheet below, note that Prod Category is placed (pivoted) before Sales Revenue SUM.

Move your mouse over this icon to see the image

 

 

Note: Similarly , you can also remove the items from the worksheet interactively.

Move your mouse over this icon to see the image

Move your mouse over this icon to see the image

 

Enhanced Graph Features

Back to Topic List

Adding a graph to a worksheet helps end users to easily see the trends and abnormalities in the data. Discoverer Plus supports many graph types and subtypes (150+ types). In addition, Discoverer Plus has many new features such as graph styles, fitline options, and drill from graphs. Choosing the right type and style of graph, and the ability to drill from the graph helps to improve visualization to analyze data trends. So, these features will not only improve the look and feel of the graphs, but also improve data visualization.

In this exercise, you will add a graph to the worksheet, select appropriate style and fitline options for the graph, and you will also drill from the graph to visualize data better.

Adding a Graph to a Worksheet

Back to Subtopic List

To add a graph to the worksheet, perform the following steps:

1.

Before proceeding with this exercise, interchange the positions of Region and Year items. (That is, move Region to the page axis, and Year to top axis). The worksheet now looks like this:

Move your mouse over this icon to see the image

Select the Edit > Worksheet Layout menu option.
Note:
You can alternately select Edit > Worksheet and open the Worksheet Layout page.

Move your mouse over this icon to see the image

 

 

2.

Select the option to add a graph to the worksheet, and select the option to display the graph below the worksheet. Click OK.

Move your mouse over this icon to see the image

 

3.

The worksheet will now look like the image below. Edit the graph type and subtypes to improve the visualization. Right-click the graph in the worksheet, and select the Edit Graph option from the shortcut menu.
Note: Observe that a simple Bar graph is added to the worksheet. However, the graph type and subtypes added may differ based on the last graph added in the workbook.

Move your mouse over this icon to see the image

You can also edit a graph by clicking the Edit Graph icon on the toolbar.

 

4.

Open the Type tab. Select Bar as the graph type, Split Dual-Y Bar as the graph subtype, and click OK.

Move your mouse over this icon to see the image

The graph in the worksheet will now appear like this:

Move your mouse over this icon to see the image


 

Modifying the Graph Style and Using Fitline Options

Back to Subtopic List

You can further improve the look and feel of the graph by using new Graph Styles and Fitline options provided in the current version of Discoverer Plus.

1.

Click the Edit Graph icon on the toolbar, and go to the Styletab. From the Style drop-down list, select Regatta as the option and also select the option to display 3D Effect in the graph.

Move your mouse over this icon to see the image

 


2.

A sample of the graph style that you have selected is reflected in the page. Click OK.

Move your mouse over this icon to see the image

After applying this graph style, this is how the worksheet looks like:

Move your mouse over this icon to see the image


3.

To add Fitline options, click the Edit Graph icon and open the Plot Area tab. Select Linear as the Fitline option for Profit SUM and Sales Revenue SUM. Click OK.

Note:This option can be found in the table under the section Select options for the series displayed in your graph.

Move your mouse over this icon to see the image

The graph in the worksheet now shows the Fitline option added. Note that though sales revenues were raising, profits do not show this trend.

Move your mouse over this icon to see the image

Note: You can also change the series colors from the Plot Area tab, as shown in the screen below:

Move your mouse over this icon to see the image

The graph in the worksheet now looks like this:

Move your mouse over this icon to see the image

 

Drilling Using Graphs

Back to Subtopic List

Now Discoverer enables you to perform drills using graphs. For example, you can drill down from Year to Quarter from the graph to view Quarterly information. Follow the steps listed below to explore this feature.

1.

In the graph, click the legend for the year 2001 to view the quarterly information.

Move your mouse over this icon to see the image

 

 

2.

The worksheet and the graph now display quarterly information. Change the Fitline option for both Sales Revenue SUM and Profit SUM series to Exponential. You can see that the Profits and Sales Revenues have started increasing exponentially in the year 2001.

Move your mouse over this icon to see the image


 

Intuitive UI to Add Calculations to Worksheets

Back to Topic List

You can intuitively add a calculation to the worksheet. For example, you want to see the profit margins for all years by region. Follow the steps given below to add a calculation named My Profit Margin.

1.

You can click the icon for New Calculation (highlighted in the image). Alternately, open the Calculations page, right-click the My Calculations node, and select New Calculation option.

Move your mouse over this icon to see the image

 

 

2.

Enter My Profit Margin as the name for the calculation, and add a formula to calculate profit margin (Profit SUM/ Sales Revenue SUM) in the Calculation field. Click OK.

Move your mouse over this icon to see the image

The Calculation will be added automatically to the worksheet as shown below. You can also see this calculation added to the My Calculations node and also in the Selected Items pane.

Move your mouse over this icon to see the image


Note that the graph type used here is Line with a subtype Dual-Y Stacked Line.

 

3.

Change the data format for My Profit Margin to represent it as a percentage, without decimals.
Move your mouse over this icon to see the image

Now the worksheet looks like this:

Move your mouse over this icon to see the image

Note: To save the changes that you are making to the worksheet, you can save the workbook. Also, if you want to save the changes as a separate worksheet in the same workbook, then you can duplicate the worksheet, and give it an appropriate name before saving the workbook. For example, you can name the above worksheet as Regional Profit Margins By Year. Duplicating a worksheet can be done from the Edit > Duplicate Worksheet menu option as shown below:

Move your mouse over this icon to see the image

Move your mouse over this icon to see the image

 

Enhanced Sorting and Formatting Features

Back to Topic List

Discoverer Plus has improved features to sort and format the data in the worksheets. While creating the worksheets, you can choose to use the correct formats and sorts in the worksheets, which improves the visibility of the data for the end users. While earlier versions supported sorting, in the current version of Discoverer Plus, you can format the data better by using page break and line break options for group sorted items. Also, you can apply conditional and stoplight formats to the items in a worksheet. You can also add Title and/or Text Area to the worksheets too.

In this exercise you will perform the following tasks to know about these new features:

Sorting and Applying Line/Page Breaks on the Data in a Worksheet

Back to Subtopic List

 

1.

The image below displays a tabular worksheet, with Region pivoted to the top axis. This worksheet displays sales, profits, and profit margins for all the regions and years. To add a group sort on year (or in general, any sort on an item), you need just a single click. Select the item Year and click the group sort icon on the toolbar. You can also do the same from the shortcut menu of the item.
(Note that the worksheet layout is edited not to display page items and graph; and also font sizes for the items are changed.)

Move your mouse over this icon to see the image

 

The worksheet looks like this after applying a group sort on Year:

Move your mouse over this icon to see the image

 

2.

To apply line breaks or page breaks right-click Year and select Format Data option.

Move your mouse over this icon to see the image

 


3.

Observe the tabs, and open the Breaks tab. Select to insert a line break at each change of value, select the line width as 3, and select a color for the line break as shown in the image below. Click OK.

Move your mouse over this icon to see the image

Now the worksheet looks like this:

Move your mouse over this icon to see the image

Note: You can select from custom colors, or edit the colors to add your own combinations of colors. Similarly, you can define page breaks too. Also, note that page breaks can be applied only to tabular worksheets and not crosstabs.


4.

Similarly, add sorts on Sales Revenue SUM and Profit SUM to display these items in descending order.
Hint: Use the sort icon on the toolbar. Also, you can define sorts from Tools > Sort menu option.

Move your mouse over this icon to see the image


5 .

Now the worksheet looks as shown in the image below. Observe that Americas and Europe Regions are doing well in terms of the sales revenues and profits. You can also see that profit margins declined in the year 2000, and started rising again in the year 2001.

Move your mouse over this icon to see the image

 

Applying Conditional and Stoplight Formats

Back to Subtopic List

 

In the current version of Discoverer Plus, you can define conditional and stoplight formats. These new features enable you to apply formats to the data in the worksheets, based on the conditions or thresholds defined. Applying conditional formatting to items in the worksheet enables you to easily identify exception data. When applied on an item, the item is formatted as specified in the condition. Stoplight formatting enables you to quickly and easily define thresholds that automatically format worksheet data to indicate if a particular value falls into the Desirable, Acceptable, or Unacceptable ranges established by the thresholds defined.

Using these formats with correct thresholds improves the visibility and readability of the data, and makes the process of analyzing data easy. In this exercise, you will follow the steps listed below to create conditional and stoplight formats in Discoverer Plus. You will define a stoplight format on profits, and you will also define a conditional format on sales revenues.

1.

To define a stoplight format on Profit SUM, select this item and click the New Stoplight Format icon on the toolbar.

Move your mouse over this icon to see the image

 

 

 

 

2.

The New Stoplight Format dialog box appears. Deselect the check box to automatically generate the name, and enter Stoplight for Profit SUM as the name of the format. Enter 250000 in the Unacceptable field, and 1300000 in the Desirable field. Click OK.

Move your mouse over this icon to see the image

Note: You can also edit the default colors of red, green, and yellow to have your own combinations of colors to represent unacceptable, desirable, and other values.


3.

Now the worksheet with stoplights applied on Profit SUM looks like this:

 

Move your mouse over this icon to see the image

 

4 .

Now to define a conditional format on Sales Revenue SUM, select this item, and click the New Conditional Format icon on the toolbar. The New Conditional Format dialog box is displayed. Deselect the check box to automatically generate the name, and enter Conditional Format on Sales Revenue as the name. Select BETWEEN as the condition, and enter 5000000 and 17000000 in the two fields to define the range on values. To define the color formats, click Format.

Move your mouse over this icon to see the image

 

5 .

Select colors as shown for the Text and the Background from the color palettes, select 14 as the Size of the font, and click the icon to select bold as the style. Click OK.

Move your mouse over this icon to see the image

After applying both conditional and stoplight formats, the worksheet will appear as shown in the image below. Note that Americas and Europe regions are top contributors for the sales revenues and profits in all the years.

Move your mouse over this icon to see the image

Note: You can edit the stoplight or conditional formats from the Format > Conditional Formats menu option, which will open the Conditional Formats dialog box.

Move your mouse over this icon to see the image

 

6 .

Rename the sheet as Regions Contributing to Sales & Profits at a Glance and save the workbook.

Move your mouse over this icon to see the image

Adding a Text Area and a Title to a Worksheet

Back to Subtopic List

You can add Text Area and Title to the worksheet. Also, you can insert conditional and stoplight formatting metatokens in the Text Area or Title of the worksheet.

 

1.

Select the menu option Edit > Worksheet Layout, and select the options to add a Text Area and a Title to the worksheet as shown in the image below. Click OK.

Move your mouse over this icon to see the image

 


2.

Double-click the Title (found at the top of the worksheet), select Text and Background colors from the drop-down list. Select 16 as the font size, and select Bold as the style. Insert Sheet Name as title, and click OK.

Move your mouse over this icon to see the image

 

Now the worksheet name is displayed as title.

Move your mouse over this icon to see the image

3 .

Similarly, double-click the Text Area (found at the bottom of the worksheet), and from the Insert drop-down list select Conditional Formats to insert conditional format metatokens. Click OK. This helps in understanding the conditional and stoplight formats defined in the worksheet.

Move your mouse over this icon to see the image

The worksheet now looks like this.

Move your mouse over this icon to see the image

Note: You can further format the worksheet by adding formats on text, headings, and so on according to your requirements.

 

Enhanced Drill Features and Parameters to Analyze Data

Back to Topic List

Using Discoverer Plus, you can now perform enhanced drill operations such as drill to related items, drill to detail, and also drill to links. These features are supported in the current version, in addition to the drill up and drill down features supported earlier.

For example, in the worksheet that you created, you can drill to product category from Year, to see what product categories have contributed maximum to the sales revenues and profits in the Americas, Europe, and other regions. You can also see which product categories are having high profit margins. You can also drill to detail to see all the component rows that correspond to a summarized value. In addition, you can drill to links, that is static URLs (such as www.