3 Introduction to Administering Discoverer with a Relational Data Source

This chapter introduces you to administering Oracle Business Intelligence Discoverer with a relational data source, and includes the following topics:

3.1 What is a relational data source, an OLTP system, and a data warehouse?

A relational data source is a database that stores data in tables that are composed of rows and columns that contain data values. The overall structure of a relational database management system (RDBMS) can be set up in any number of ways, depending on how the system will be used.

A typical RDBMS is designed for online transaction processing (OLTP), with the main objective of storing vast quantities of transaction data as efficiently as possible. OLTP system design is primarily concerned with getting data into an RDBMS. An OLTP system contains the information that a business uses on a day-to-day basis. The information in an RDBMS designed for an OLTP system is typically process-oriented, current, and subject to change.

A data warehouse is an RDBMS with a structure designed to facilitate data analysis, rather than simply efficient storage of information. Data warehouse design is primarily concerned with getting data out of an RDBMS. The information in a data warehouse is typically subject-oriented, historical, and static.

Oracle Business Intelligence Discoverer provides business users with data analysis capabilities, regardless of whether the RDBMS was designed for an OLTP system or as a data warehouse.

3.2 What is Oracle Business Intelligence Discoverer Administrator?

Oracle Business Intelligence Discoverer Administrator is one of the components of Oracle Business Intelligence Discoverer.

Discoverer Administrator is a tool to hide the complexity of a data source from business users, so they can answer business questions quickly and accurately using Oracle Business Intelligence Discoverer.

Discoverer Administrator's wizard-style interfaces enable you to:

  • set up and maintain the End User Layer (EUL)

    The EUL is a set of database tables that contain information (or metadata) about the other tables and views in the database. For more information, see "Introducing the End User Layer".

  • control access to information

  • create conditions and calculations for Discoverer end users to include in their worksheets

Users of Discoverer Administrator are called Discoverer managers.

Discoverer Administrator is shipped as part of Oracle Business Intelligence Tools, which is a collection of business intelligence tools.

3.3 What are the fundamental concepts behind a Discoverer system when using a relational data source?

Before you design and implement a Discoverer system when using a relational data source, you must become familiar with some fundamental Discoverer concepts.

These fundamental concepts are described briefly in the sections below:

Each of these sections is only a brief description, but includes a cross-reference to other chapters in this manual where you can find more information.

3.3.1 Introducing the End User Layer

The End User Layer (EUL) insulates Discoverer end users from the complexity and physical structure of the database. The EUL provides an intuitive, business-focused view of the database that you can tailor to suit each Discoverer end user or user group. The EUL enables Discoverer end users to focus on business issues instead of data access issues. It helps Discoverer end users produce queries by generating SQL and provides a rich set of default settings to aid report building.

The metalayer structure of the EUL preserves the data integrity of the database. Whatever the Discoverer manager or the Discoverer end user does with Discoverer, it affects only the metadata in the EUL and not the database.

The EUL is a collection of approximately 50 tables in the database. These are the only tables that can be modified through Discoverer Administrator. Business areas are defined in Discoverer Administrator using the EUL database tables. Discoverer provides read-only access to the application database.

For more information about the EUL, see Chapter 4, "Creating and Maintaining End User Layers".

3.3.2 Introducing business areas

Typically, no single user (or group of users) is interested in all the information in the database. The users are much more likely to be interested in a subset of the information that is connected in some way to the job that they do. Using Discoverer Administrator, you create one or more business areas as containers of related information.

Having created a business area, you load the database tables containing the related information into that business area.

For more information about business areas, see Chapter 5, "Creating and Maintaining Business Areas".

3.3.3 Introducing folders and items

The tables and views you load into a business area are presented to Discoverer end users as folders. The columns within a table or view are presented as items.

Often the database tables and columns have names that users will not find meaningful. Using Discoverer Administrator, you can make the names of folders and items more helpful than the names of the tables and columns on which they are based.

The folders in a business area do not have to be based directly on database tables or views. You can create complex folders that contain items based on columns from multiple tables or views. You can also create custom folders based on SQL statements you write yourself.

Similarly, the items in a business area do not have to be based directly on columns. You can create calculated items that perform calculations on several columns, or that make use of the analytic functions available within the Oracle database.

For more information about folders and items, see:

3.3.4 Introducing workbooks and worksheets

Oracle Business Intelligence Discoverer end users analyze information by including items in worksheets and using Discoverer's data analysis and charting wizards to find the information they are interested in. Discoverer worksheets are grouped into workbooks. A workbook can be stored on the file system (in Discoverer Desktop only) or in the database.

In some cases, you will want to restrict Discoverer end users to analyzing information in worksheets that have been created for them. In other situations, it will be more appropriate to allow end users to create their own worksheets. Discoverer Administrator enables you to decide which end users can create their own workbooks, and which end users can only use workbooks that have been created for them.

For more information about workbooks and worksheets, see Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus.

3.3.5 Introducing hierarchies and drills

Hierarchies are logical relationships between items that enable users to drill up and down to view information in more or less detail. To analyze information effectively, Discoverer end users will want to:

  • drill down to see more detail about a particular piece of information (for example, if the sales total for a specific region is disappointing, an end user will typically want to drill into the region's sales total figure to see which cities within that region have under-performed)

  • drill up to see how the detail data contributes to information at a higher level (for example, when looking at the sales figure for a particular city, an end user will typically want to drill up to see the total sales figure for the region)

When you load tables into a business area, Discoverer automatically creates default date hierarchies for date items. Often you will want to create your own hierarchies for other items as well.

For more information about hierarchies, see Chapter 13, "Creating and Maintaining Hierarchies".

3.3.6 Introducing summary folders

Summary folders are a representation of queried data that has been saved for reuse.

You create summary folders with Discoverer Administrator to improve query response time for end users. The response time of a query is improved because the query accesses pre-aggregated and pre-joined data rather than accessing the database tables. You can also direct Discoverer to use summary folders based on tables containing summary data that have been created by another application. These tables are known as external summary tables.

For more information about summary folders, see:

3.4 What is your role as Discoverer manager when using a relational data source?

As a Discoverer manager, you are responsible for:

  • the initial implementation of the Discoverer system

  • the ongoing administration and maintenance of the Discoverer system

3.5 What are the steps to a successful Discoverer implementation with a relational data source?

Figure 3-1 shows the steps for implementing a Discoverer system with a relational data source.

Figure 3-1 Discoverer implementation flowchart (with a relational data source)

Surrounding text describes Figure 3-1 .

These six steps are described in more detail below.

  1. Identify users' requirements

    For a Discoverer implementation to be successful, it must meet users' requirements. To find out what those requirements are, conduct interviews with key users and ask them questions like:

    • what information do you use now?

    • what information would you like to see?

    • how would you like the information presented?

    As a starting point, why not review the reports and information sources that users are currently using? You will quickly see how using Discoverer will give users both access to the information they currently use and the ability to analyze that information in new and powerful ways.

    Remember that users' requirements typically change over time. Often the biggest changes are requested by users when a system has been rolled out. When users see what Discoverer can do for them, they soon have suggestions for other areas where it could be useful.

    Try and anticipate changing requirements. After all, a successful system often starts by meeting a subset of requirements and is then modified over time based on user feedback.

  2. Create an EUL (mandatory if one does not exist already).

    An EUL must exist before you can create a business area. If an EUL does not already exist, you must create one.

  3. Create a business area and load data into it (mandatory).

    Having identified users' requirements, you will have a good idea of the information that users need to access. For example, one group of users might want to access sales information, another group might want to access manufacturing information, and so on.

    In Discoverer, you group information with a common business purpose into a business area. Having created a business area, you must specify which database tables and views hold that information. You do this by 'loading' the tables and views into the business area.

  4. Refine the structure of the business area so that users can view data in the most flexible and understandable way.

    The default settings and contents of a business area are sufficient to enable users to access and analyze data. However, Discoverer Administrator provides you with several features to enhance the default analysis capabilities.

    Specifically, you can:

  5. Grant business area access to users or roles (mandatory).

    Having identified users' requirements, you will have a good idea of which users (and groups of users) need access to which information.

    In some cases, different users will want access to the same information. For example, information about an employee might be required by the employee's manager, payroll staff, and users in the Human Resources department.

    In other cases, it is appropriate for only one group of users to have access to the information. For example, information about an engineering project is invaluable for a project manager but of no interest to payroll staff.

    Keeping users' information requirements in mind, you can grant users access to the business area.

    Note that Discoverer users (whether end users or managers) never compromise the security of the underlying database. Users cannot see information in Discoverer to which they do not already have sufficient database privileges to access. In other words, all Discoverer security and privileges are additional to the database security mechanisms.

  6. Deploy Discoverer

    Users' requirements will determine which of the Discoverer components you decide to make available in your company.

    When identifying their requirements, you will probably have realized that some users want the ability to create their own worksheets, while other users simply want to use worksheets that have been created for them. In addition, some users will want to run Discoverer using a Web browser, using either a Java applet user interface or an HTML user interface.

    Use the table below as a guide to decide which Discoverer components to deploy in your organization.

    User requirement Plus Viewer Desktop
    Install and run Discoverer on a PC running Windows No No Yes
    Run Discoverer using a Web browser Yes Yes No
    Build new worksheets Yes No Yes
    Save workbooks to the file system No No Yes
    Customize Discoverer user interface No Yes No

    Other factors will probably also influence your decision, including network performance and security issues.

    Having decided which Discoverer components to deploy, refer to the appropriate documentation for specific installation or configuration instructions. For more information, see "Related Documents".

3.6 What is involved in maintaining a Discoverer system with a relational data source?

Having implemented a Discoverer system with a relational data source, you will probably find that a small amount of ongoing maintenance is required to make sure that Discoverer continues to meet users' requirements.

Typically, you will continue to refine business areas by:

In addition to the above, you will probably have to change which users have access to which business areas and the operations that individual users can perform in those business areas. For example:

  • when a new user joins, you will have to grant them access to the business areas they need to do their job

  • when an existing user changes jobs or departments, you might have to grant them access to new business areas, or revoke their access from previous business areas

    For more information, see Chapter 7, "Controlling Access to Information".

3.7 What are the prerequisites for using Discoverer Administrator?

This section describes the system and data access prerequisites for using Oracle Business Intelligence Discoverer Administrator and contains the following topics:

3.7.1 What are the system prerequisites?

Before you can use Discoverer Administrator:

  • A suitable database must be installed and available. An Oracle Enterprise Edition database will support the use of materialized views to improve the performance of summary folders.

  • Discoverer Administrator must have been installed on a PC, typically as part of a full Oracle Classic Suite installation.

Before end users can use Discoverer, either one or both of the following must have been installed:

3.7.2 What are the data access prerequisites?

To create and maintain a Discoverer system using Discoverer Administrator, you will require certain Discoverer privileges and database privileges:

To use a Discoverer system, end users will require certain Discoverer and database privileges:

  • access to at least one EUL

  • access to at least one business area in the EUL

  • the SELECT database privilege (granted either directly to database users or through a database role) on the tables on which folders in a business area are based

  • to take advantage of the following Discoverer features, users need specific privileges

3.8 About Discoverer using database parameters

Discoverer uses several database parameters to take advantage of functionality that is available in the database. Database parameters need to be set to recommended values to achieve the expected outcome. You can specify database parameter values in the initialization file of the Oracle DBMS, the INIT<SID>.ORA file. For example, the value you specify for timed_statistics will affect Discoverer's query prediction feature.

The following table lists the database parameters referenced in this guide, that are used by Discoverer.

Database parameter Possible result if value is not set Description Recommended value
timed_statistics No query prediction given. Related to enabling query prediction. For more information, see "How to verify and change the timed_statistics parameter for query prediction". TRUE
optimizer_mode No query prediction given. Related to enabling query prediction. For more information, see "How to verify and change the optimizer_mode parameter for query prediction". CHOOSE
optimizer_index_cost_adj Slower queries. Related to tuning the way the Cost-Based Optimizer uses indexes. For more information, see the Oracle11g documentation. For more information, see your database administrator.
optimizer_index_caching Slower queries. Related to tuning the way the Cost-Based Optimizer uses indexes. For more information, see the Oracle11g documentation. For more information, see your database administrator.
global_names Invalid connect strings. Related to setting up generic connectivity. For more information, see "About setting up generic connectivity for Discoverer". FALSE
failover_mode Database processes not relocated to alternative backup component on database failure. Related to enabling support for Transparent Application Failover. For more information, see "How to enable Discoverer support for Transparent Application Failover". FAILOVER_MODE=<type><retries>
type Database processes not relocated to alternative backup component on database failure. Sub-parameter of the failover_mode parameter. For more information, see "How to enable Discoverer support for Transparent Application Failover". (TYPE=SELECT)
retries Database processes not relocated to alternative backup component on database failure. Sub-parameter of the failover_mode parameter. For more information, see "How to enable Discoverer support for Transparent Application Failover". (RETRIES=5)

3.9 How to start Discoverer Administrator

To start Discoverer Administrator:

  1. From the Windows Start menu, choose Programs | Oracle Business Intelligence Tools - <BI_TOOLS_HOME_NAME> | Discoverer Administrator to display the Connect to Oracle Business Intelligence Discoverer Administrator dialog.

    Figure 3-2 Connect to Oracle Business Intelligence Discoverer Administrator dialog

    Surrounding text describes Figure 3-2 .

  2. Enter the username of the database user with which you want to start Discoverer Administrator in the Username field

  3. Enter the password of the database user with which you want to start Discoverer Administrator in the Password field.

    Passwords are case-sensitive in Oracle databases (Enterprise Edition release 11.1 or later).

  4. Specify the database to connect to in the Connect field, using the following guidelines:

    • if you are logging onto your default Oracle database, do not enter anything in the Connect field

    • if you are logging onto a different Oracle database, specify the name of the database (if you are not sure which name to use, contact your database administrator)

  5. Click the Connect button to start Discoverer Administrator and connect to the database.

    What you see next depends on your Discoverer system:

Notes

  • The Connect to Oracle Business Intelligence Discoverer Administrator dialog might contain the Oracle Applications User check box. Select this check box if you want to use Discoverer Administrator to manage an EUL for use with Oracle Applications. For more information about using Discoverer with Oracle Applications, see Chapter 17, "Using Discoverer with Oracle Applications".

  • You always start Discoverer Administrator in your default EUL. Your default EUL is the one specified on the "Options dialog: Connection tab". If you want to change the EUL you are working in, you must change your default EUL and then reconnect to Discoverer Administrator (for more information, see "How to view or change the default End User Layer").

  • When you use Discoverer Administrator, you should make sure that no more than one current connection exists against a particular EUL. When you have a single connection against an EUL, you avoid the risk of unknowingly making conflicting changes to EUL objects.

3.10 What is the Workarea?

The Workarea is your view into the End User Layer. The Workarea is where you maintain the EUL by creating and editing:

  • business areas and folders and items

  • hierarchies

  • item classes

  • summary folders

The Workarea window is displayed within the Discoverer Administrator main window. You can open more than one Workarea window at a time, which is useful when you want to copy objects between business areas. Note however that all Workarea windows contain the same business areas.

3.10.1 About the tabs in the Workarea window

The Workarea window contains four tabs:

  • The Data tab displays the structure and content of each business area. The Data tab enables you to:

    • create calculated items

    • create complex and custom folders

    • create joins

    • create conditions

    • create new business areas, folders, and items

    • modify object properties

    Figure 3-3 Workarea window: Data tab

    Surrounding text describes Figure 3-3 .

    For more information about the Data tab and the icons displayed on it, see "Workarea: Data tab"

  • The Hierarchies tab displays the hierarchies within each business area. The Hierarchies tab enables you to:

    • create new hierarchies

    • review the content and organization of existing hierarchies

    • view the hierarchy templates supplied with Discoverer Administrator

    The Show button on the Hierarchies tab enables you to specify the hierarchies that are displayed.

    Figure 3-4 Workarea window: Hierarchies tab

    Surrounding text describes Figure 3-4 .

    For more information about the Hierarchies tab and the icons displayed on it, see "Workarea: Data tab".

  • The Item Classes tab displays the item classes within each business area. The Item Classes tab enables you to:

    • create new item classes

    • view the list of values associated with an item class (if there is one)

    • view items that use each item class

    • identify the item classes that have drill to detail and alternative sort attributes, and whether those options are active

    The Show button on the Item Classes tab enables you to specify the item classes that are displayed.

    Figure 3-5 Workarea window: Item Classes tab

    Surrounding text describes Figure 3-5 .

    For more information about the Item Classes tab and the icons displayed on it, see "Workarea: Data tab".

  • The Summaries tab displays the summary folders within each business area. The Summaries tab enables you to:

    • create new summary folders

    • review the organization and definition of summary folders

    • refresh summary folders

    Figure 3-6 Workarea window: Summaries tab

    Surrounding text describes Figure 3-6 .

    For more information about the Summaries tab and the icons displayed on it, see "Workarea: Data tab".

3.10.2 About the context sensitive menus in the Workarea window

If you click the right-mouse button when working with Discoverer Administrator, a popup menu is displayed. In the Workarea window, the contents of this popup menu are the commands most frequently used with the currently selected object.

If no object is currently selected, the popup menu displays commands for working with a business area in general and commands appropriate to the current tab.

3.10.3 About the Administration Tasklist

When you first start Discoverer Administrator, the Administration Tasklist is displayed on top of the main Discoverer Administrator window.

Figure 3-7 Administration Tasklist

Surrounding text describes Figure 3-7 .

Use the Administration Tasklist in two ways:

  • as a reminder of the basic steps involved in preparing a business area

  • as a shortcut method of displaying the dialogs associated with the listed tasks

3.11 What are the new features in Discoverer Administrator?

Discoverer Administrator Version 11.1.1.1.0 contains the following new and improved features:

  • Certification with Oracle 11g (version 11.1) Enterprise Edition Database

    Consideration has been made for Discoverer certification with the Oracle 11g (version 11.1) Enterprise Edition database.

3.12 About Discoverer Administrator documentation and online help

Discoverer Administrator is supplied with online Help and documentation.

The Discoverer Administrator Help System gives you context sensitive access to reference information from the Administration Guide in HTML format.

To start the Help System either click Help in a Discoverer dialog or choose Help | Help Topics.

  • the Help button on Discoverer Administrator dialogs displays detailed context sensitive help on the fields in the dialog

  • the Help | Help topics menu option displays the contents of the Oracle Business Intelligence Discoverer Administrator help system, including the context sensitive dialog help

  • the Help | Manuals menu option displays a list of available Discoverer manuals (including the Oracle Business Intelligence Discoverer Administrator error messages file)

To find a topic in the Help System:

  • click the Contents icon at the top of each help page or choose Help | Help Topics (to see a list of the topics in the help system)

  • click the Index icon at the top of every help page to see a list of index entries

To view (and print) the Administration Guide in PDF format, use the Oracle Developer Suite documentation CD.

Tip: To search for words or phrases, use the Administration Guide in PDF format.

Additional information about Discoverer (for example, whitepapers, best practices, tutorials) is available on the Oracle Technology Network at www.oracle.com/technology.