1
Introducing Oracle Discoverer Administrator

Introducing Oracle Discoverer Administrator

This chapter introduces you to Oracle Discoverer Administrator, and includes the following topics:

• "What is Oracle Discoverer?"

• "What is Oracle Discoverer Administrator?"

• "What is your role as a Discoverer manager?"

• "Basic concepts for Discoverer managers"

• "What are the steps to a successful Discoverer implementation?"

• "What is involved in maintaining a Discoverer system?"

• "What are the new features in Discoverer Administrator Version 9.0.4?"

What is Oracle Discoverer?

Oracle Discoverer is a business intelligence toolset that comprises:

• Oracle Discoverer Administrator

• OracleAS Discoverer Plus

• OracleAS Discoverer Viewer

• Oracle Discoverer Desktop

  Figure 1-1 The Discoverer components

  
  
  Text description of the illustration discopie.gif

All of the Discoverer tools rely on the Discoverer 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.

What is Oracle Discoverer Administrator?

Oracle Discoverer Administrator is one of the components of Oracle Discoverer. Discoverer Administrator is a tool to hide the complexity of the database from business users, so they can answer business questions quickly and accurately using Oracle Discoverer.

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

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

• 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.

Oracle Discoverer Administrator is shipped as part of Oracle Developer Suite, which is a complete suite of application development and administration tools.

What is your role as a Discoverer manager?

As a Discoverer manager, you are responsible for:

• the initial implementation of the Discoverer system

• the ongoing administration and maintenance of the Discoverer system

To fulfill your role as a Discoverer manager, you need to understand how to design business areas that support your company's decision-makers. On the database side, you need to know what data is in the database, where it is located, how it is stored, and how it relates to other data. On the business side, you need to know what data the decision-makers require, what kinds of analysis is necessary, and how the final results should be presented for easy comprehension.

Basic concepts for Discoverer managers

Before you start using Discoverer Administrator, you will find it helpful to familiarize yourself with some basic concepts:

• "What is business intelligence?"

• "What are relational databases, OLTP systems, and data warehouses?"

• "What are the fundamental concepts behind a Discoverer system?"

• "How does Discoverer work?"

What is business intelligence?

Business intelligence is the ability to analyze data to answer business questions and predict future trends.

Oracle Discoverer is a great business intelligence tool because it enables users to analyze data in an ad hoc way. Instead of relying on IT specialists to pre-define queries and reports, Discoverer users can choose the data to analyze and can continue manipulating results until they have the necessary information to take business decisions. Oracle Discoverer also enables users to share the results of their data analysis with their colleagues in different formats (including charts and Excel spreadsheets).

What are relational databases, OLTP systems, and data warehouses?

A relational database 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 Discoverer provides business users with data analysis capabilities, regardless of whether the RDBMS was designed for an OLTP system or as a data warehouse.

What are the fundamental concepts behind a Discoverer system?

Before you design and implement a Discoverer system, you need to become familiar with some fundamental Discoverer concepts.

These fundamental concepts are described briefly in the sections below:

• "Introducing the End User Layer"

• "Introducing business areas"

• "Introducing folders and items"

• "Introducing workbooks and worksheets"

• "Introducing hierarchies and drills"

• "Introducing summary folders"

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

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 3, "Creating and maintaining End User Layers".

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 4, "Creating and maintaining business areas".

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:

• Chapter 5, "Creating and maintaining folders"

• Chapter 8, "Maintaining items and item classes"

• Chapter 10, "Creating and maintaining calculated items"

Introducing workbooks and worksheets

Oracle 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 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 the Oracle Application Server Discoverer Plus User's Guide.

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 (e.g. 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 (e.g. 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 12, "Creating and maintaining hierarchies".

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:

• Chapter 13, "Managing summary folders"

• Chapter 14, "Creating summary folders manually"

• Chapter 15, "Additional information about summary folders"

How does Discoverer work?

Users' requests for information from the database are in the form of worksheets.

When a user creates or opens a worksheet, Discoverer:

• converts the worksheet into the corresponding SQL statements (e.g. by converting folder names and item names to table names and column names respectively)

• sends the SQL statements to the database

• displays the result set that is returned from the database

In the case of Discoverer Plus and Discoverer Viewer, the SQL statements are routed to the database via Discoverer processes running on an application server machine.

What are the steps to a successful Discoverer implementation?

There are essentially six steps to the implementation of a Discoverer system, as shown in the flowchart below:

Figure 1-2 Discoverer implementation flowchart

Text description of the illustration ad_flow1.gif

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 a number of features to enhance the default analysis capabilities.

   Specifically, you can:

   • create optional and mandatory conditions to restrict the number of rows returned in a folder (for more information, see Chapter 11, "Creating and maintaining conditions")

   • create calculated items to relieve users of the task of creating complex calculations by providing them with ready-made computations (for more information, see Chapter 10, "Creating and maintaining calculated items")

   • create joins to combine folders that were not automatically joined when tables were loaded from the database (for more information, see Chapter 9, "Creating and maintaining joins")

   • combine folders into complex folders to completely hide joins and relational structures from users (for more information, see Chapter 5, "How to create complex folders")

   • create custom folders, to represent a result set returned by a SQL statement that you have entered as a folder with items (for more information, see Chapter 5, "How to create custom folders")

   • edit item names, descriptions, and other formatting information to make data easier to understand (for more information, see Chapter 8, "How to edit item properties")

   • create item classes to support lists of values, alternative sorts, and drill to detail (for more information, see Chapter 8, "What are item classes?")

   • create hierarchies to simplify drill-down operations (for more information, see Chapter 12, "Creating and maintaining hierarchies")

   • create summary tables (or let Discoverer automate summary management, or register existing summary tables) to maximize query performance (for more information, see Chapter 13, "Managing summary folders")

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".

What is involved in maintaining a Discoverer system?

Having implemented a Discoverer system, 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:

• adding new item classes, to support new lists of values, alternative sorts, and drill to detail (for more information, see Chapter 8, "Maintaining items and item classes")

• adding new joins, to combine folders that were not automatically joined when tables were loaded from the database and which users now need access to in the same worksheet (for more information, see Chapter 9, "Creating and maintaining joins")

• adding new optional and mandatory conditions, to restrict the number of rows returned in a folder (for more information, see Chapter 11, "Creating and maintaining conditions")

• adding new calculated items, to provide users with ready-made computations that were not initially required (for more information, see Chapter 10, "Creating and maintaining calculated items")

• adding new complex folders, to simplify query creation (for more information, see Chapter 5, "How to create complex folders")

• adding new custom folders, to meet users' requirements that cannot be met using conventional folders (for more information, see Chapter 5, "How to create custom folders")

• adding new hierarchies, to enable users to analyze data in new ways (for more information, see Chapter 12, "Creating and maintaining hierarchies")

• adding new summaries to resolve performance issues with particular queries (for more information see Chapter 13, "Managing summary folders")

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 6, "Controlling access to information".

What are the new features in Discoverer Administrator Version 9.0.4?

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

• SQL Optimization and the New Join Wizard - To improve query performance, Discoverer now optimizes the SQL that it generates. For more information, see "Creating and maintaining joins"

• Heterogeneous Data Services - Discoverer users can now take advantage of generic connectivity (a mechanism supported by the Oracle database Heterogeneous Services) to access ODBC or OLE DB (object linking and embedding database) databases. For more information, see "What are Heterogeneous Services?"