Chapter 5: OracleAS Discoverer 10g | Oracle Application Server 10g Web Development (Oracle Press)

OracleAS Discoverer 10 g is an end- user ad hoc query, reporting, analysis, and web-publishing tool. It opens up the analytic capabilities of the Oracle databases within your organization to all levels of end users without the need for any training in SQL or extended resource usage from your IT department. OracleAS Discoverer 10 g is the tool to enable users to gain insight into their business and operations and make informed business decisions using up-to-the-second data. All of these features would be of little value if the tool itself had a large learning curve (hindering the productivity of the end users) or required huge database resources ( hindering the ability of the database to provide information to those who need it). Luckily, OracleAS Discoverer 10 g has been designed with these factors in mind.

Fewer developers and end users work with OracleAS Discoverer 10 g than with other Oracle tools such as Oracle Forms and Oracle Reports , and that s a shame because it s an incredibly powerful product. As you ll see in this chapter, OracleAS Discoverer 10 g gives end users the ability to define and run complex reports with advanced features such as graphing, data pivoting, drill- downs , parameters, and correlated subqueries (just to name a few), all without requiring the end users to have advanced knowledge of SQL and all of its nuances . On top of all of these features, OracleAS Discoverer 10 g contains a full web-based development environment (unlike Oracle Forms and Oracle Reports) that gives Discoverer developers the ability to create and run Oracle Discoverer Workbooks and Worksheets on the web via their web browser. This eliminates many of the licensing and maintenance costs of maintaining the client machines previously needed for the Oracle Discoverer Desktop. Since OracleAS Discoverer Plus (the web-based Discoverer development tool) and OracleAS Discoverer Viewer (the tool for viewing Discoverer reports over the web) are true collaborative environments, the creation and sharing of Oracle Discoverer Reports between users within a department or organization is a very simple process. OracleAS Discoverer 10 g can be integrated with Oracle Application Server 10 g ˜s Identity Management features, including Single-Sign On.

The Discoverer End-User Layer

All of this amazing functionality comes at a cost, albeit a small one. Before end users can begin to exploit the features of OracleAS Discoverer 10 g just outlined and manipulate the data in your databases via Discoverer Workbooks, Worksheets, and reports, a set of tables and views, called an End-User Layer, needs to be built into the database. What is an End-User Layer? From Oracle s documentation:

The End User Layer (EUL) is the metadata (i.e., data about the actual data in a database) that is simple and easy for Discoverer end users to understand. You use Discoverer Administrator to create, customize, and maintain this view for your users so they can easily access data in Discoverer. You must have access to at least one EUL in order to use Discoverer. Access is granted using the Privileges dialog.

When using OracleAS Discoverer Viewer (the web-based tool to view Discoverer Workbooks and Worksheets over the Web, discussed in Chapter 6), the first thing you are prompted for is the Discoverer connection you would like to begin working with. The OracleAS Discoverer connection is made up of a username, a password, a connect string, and an End-User Layer (Discoverer connections also include a responsibility and security group , but these fields are of use only to Oracle s E-Business Suite, formerly knows as Oracle Financials). The End-User Layer is required before you begin working with Oracle Discoverer.

Oracle Discoverer Administrator

Building the EUL is the only part in the process that cannot be performed over the Web. The tool used to build the EULs, called Oracle Discoverer Administrator, is part of the Oracle10 g Internet Development Suite. This tool can also modify and delete EULs. To start the Oracle Discoverer Administrator on Windows, follow this sequence:

Start Oracle Developer Suite “ <ORACLE_HOME> Discoverer Administrator Oracle Discoverer Administrator

To start the Oracle Discoverer Administrator on Unix, enter these commands:

 export ORACLE_HOME = <directory where developer suite is installed> cd $ORACLE_HOME/bin . ./dis51adm.sh

The username you specify here may or may not have an EUL built already. If it does, you will connect to it and be given the option of modifying it. If it does not, you will be presented with the option of building one (Figure 5-1). The user must exist in the database before attempting to connect.

Figure 5-1: The dialog to create an EUL

Click Create an EUL. The first page of the Create EUL Wizard (Figure 5-2) allows you to specify who will own the EUL tables and views, whether they are available to all users in the database, and if the EUL is intended for Oracle Applications (also known as E-Business Suite).

Figure 5-2: The first page of the Create EUL Wizard

Tip	If you logged in as a user with CREATE USER privileges, the radio button next to Create A New User will be available. You can create a new user to own the EUL tables and views at this point, if you wish.

If the Grant Access To PUBLIC check box is selected, the new EUL will be accessible to all users in the current database. Clear this check box to specify that the EUL owner is the only database user that is able to access data through this EUL. Clicking Finish here will begin the process of creating the tables and views that make up the EUL.

The Discoverer EUL tables and views now exist, but they are not populated with any data. We now have to begin the process of specifying what database objects will be accessed through OracleAS Discoverer 10 g. After that task is completed, Discoverer will populate the newly created tables and views with metadata. After you click Close, Oracle Discoverer Administrator reconnects as the user specified and a Load Wizard page is displayed.

You are prompted to either create a new business area or open an existing one. What, exactly, is a business area? From Oracle s documentation:

Business areas are conceptual groupings of tables and/or views designed to match Discoverer end users specific data requirements.

You can put any schema object you like into a business area, but business areas are intended to group tables and views that end users would normally reference together. As you will see later, we can define relationships within the business area that give Discoverer end users greater functionality, and obviously, those relations cannot be created between schema objects not included in the business area. There is a one-to-many relationship between EULs and business areas: namely, that an EUL consists of more business areas and that a business area can belong to only one EUL. Creating the business area involves these things:

Specifying what schema objects will be in the business area
Whether joins are created between master/detail tables when the business area is populated
Whether data hierarchies are created and whether default aggregate on numeric items are computed in the business area
Naming the business area

The business area can be modified later, so the choices made here are not permanent. The second page of the Load Wizard (Figure 5-3) specifies what schemas will be used. If any links are defined in your database, they will be available to you in the drop-down box on the top of the dialog box.

Figure 5-3: The second step of the Load Wizard

Note	It s important to note that even though all schemas in the database are listed here, only those tables and views you have been granted privileges on will be available for you in the next page of the wizard, where individual tables and views are selected.

Clicking Next takes you to the third step in the wizard (Figure 5-4). Here is where you will select the individual tables and views that will make up your business area. Views have an eye icon next to them.

Figure 5-4: The third step of the Load Wizard

Click Next to display the fourth page of the Load Wizard (Figure 5-5). Here s where things really start to get interesting. This page is where you can define some of the advanced features of OracleAS Discoverer 10 g. This page has five check boxes along the left side of the wizard:

Figure 5-5: The fourth page of the Load Wizard

Yes, create joins from Use this check box to create joins on items in the business area.
Primary/foreign key constraints Use this option to create joins on primary and foreign keys when primary and foreign key constraints are defined in the database. It saves you having to create the joins manually, and Discoverer will always create joins that are correct (i.e., the master and detail relationships are specified the right way round).
Matching column names Use this option to create joins on matching column names in the data name. As the number of columns that are loaded increases, the amount of processing required also increases . A side effect of doing this is that the joins created must be checked after the load to make sure they are valid. The master-detail folder relationship must be verified as being the correct way round, and the join condition must be verified as being valid (e.g., not created accidentally by two different columns using the same name).
Summaries based on folders that are created Use this option to use automated summary management (ASM) to create summary folders after the load process, based on folders that Discoverer creates. ASM attempts to create a set of suitable summary folders after analyzing the table structure. Selecting this option might slightly increase the duration of the business area load, but it should give end users improved query performance. Note: Do not choose this option if you are going to run ASM, or if your database space is low.

Date hierarchies Use this option to automatically create date hierarchies for date items. Choose a hierarchy from the drop-down list of default date hierarchy formats. The date hierarchy is applied to all date columns in the business area. Creating a date hierarchy enables end users to drill up/down through date items. Automatically generating date hierarchies inserts additional (e.g., calculated) items into the folder. An extra item is created for every date item in each node in the date hierarchy.

Note	Hierarchies are used to provide Discoverer end users the ability to either drill up to a more general level of detail or drill down to a finer level of detail (hierarchies are discussed later in this chapter).

Default aggregate on data points Use this option to specify a default aggregate for numeric items. Choose an aggregate from the drop-down list. The default aggregate is SUM. This will create an aggregate for every numeric column in the tables you specified on the previous page. Most times, this is inefficient. It is much more likely that you will create individual aggregates based on your needs.
List of values (LOVs) for items of type Use this option to generate LOVs based on the types you select. This option automatically creates LOVs for each axis item of each type specified, except character items longer than 40 characters . The values are derived from the values in the database column. LOVs make interacting with reports much more intuitive for end users. Selecting this option will create an LOV for every column specified. Most times, this is inefficient. It is much more likely that you will create individual LOVs to meet your needs.

The final page of the Load Wizard is where the business area is named, and table and view names can be altered when added to the business area. When you click Finish, the business area is populated with data.

Oracle Discoverer Administrator Workarea

From this moment onward, when your run Oracle Discoverer Administrator and log in as the user you created, you will be asked to either create a new business area or open an existing business area. To demonstrate , log out of Discoverer Administrator and log back in again. Select Open An Existing Business Area, specify the business area you just created, and click Finish. You will see the Discoverer Administrator Workarea.

In the bottom right of the screen is a small window called the Administration Tasklist. This window provides a quick way of running the various wizards built into Oracle Discoverer Administrator to enhance your EUL and provide greater functionality and capabilities to your Discoverer end users. The Administration Tasklist acts both as a reminder of the basic steps involved in preparing a business area and as an interactive method of launching the listed tasks . You do not have to use the Administration Tasklist, but it may be helpful to track your progress.

The Load Wizard is great for creating your EUL and setting global properties that apply to all elements in your business area(s). The Discoverer Administrator Workarea is used to create specific attributes in your business area (joins, calculated items, conditions, etc.). The four tabs at the top of the Workarea enable you to access the pages of the Workarea. Each tab enables you to work with a different element of the business area design. The Workarea is your primary view of the business area, where you modify folders and items to create the business view of data for the end user.

Discoverer Terminology

Now is a good time to take a small detour and discuss the terms Discoverer uses.

Folders Folders in Discoverer represent tables or views from your database that have been included in your EUL and added to a particular business area.
Items Items represent columns in the tables or views from your database that have been included in your EUL and added to a particular business area.
Joins Joins work the same way in Discoverer as they do in the database. The only difference is in terminology: in the database, a join is performed between two or more tables; in Discoverer, a join is performed between two or more folders. Once you have defined a join between two folders, Discoverer end users can include items from both folders in the same Worksheet in Discoverer Plus (and Discoverer Desktop) and Discoverer Administrators can create complex folders and hierarchies in Discoverer Administrator that contain items from both folders.
Calculated items Calculated items are items that use a formula to derive data for the item. Calculated items enable Discoverer end users to apply business calculations to the data. Calculated items (like other items in a folder) can be used in conditions, summary folders, lists of values, joins, and other calculated items. There are three types of calculated items: derived items, aggregate calculated items, and aggregate derived items. As the Discoverer manager, you can create calculated items and make them available for inclusion in Workbooks. Creating calculated items provides the following benefits:
Discoverer end users can include a complicated calculation in their Worksheets simply by selecting a calculated item.
You can add new items to a folder that do not exist as columns in the underlying database tables.
You create calculated items using expressions that can contain existing items, operators, literals, and functions.
Conditions Conditions filter Worksheet data, enabling Discoverer end users to analyze only the data they are interested in. You also use conditions to restrict access to sensitive data. By imposing mandatory conditions, you assure that only nonsensitive data is made available to Discoverer users. You can make sure that Discoverer end users see only the data that you want them to see. As a Discoverer Administrator, you can anticipate commonly used conditions and make them available to Discoverer end users so they can apply them in Worksheets. This enables Discoverer end users to work efficiently (Discoverer Plus users can also create their own conditions). Conditions are categorized as follows :
Simple conditions These contain a single condition statement.
Advanced conditions These contain two or more condition statements.
Nested conditions These contain condition statements that are defined within other condition statements.
Advanced nested conditions These contain two or more condition statements and also include condition statements defined within the advanced condition. As an alternative to creating advanced conditions, you might want to create two or more single conditions and apply them at the same time. This enables Discoverer users to be more selective about which parts of the condition they use.

Conditions work in Discoverer by matching condition statements against Worksheet data so that data matching your condition statements is displayed in Workbooks. There are two types of conditions:
Mandatory conditions are always applied to a Worksheet that contains one or more items from the folder that contains the condition. Discoverer Plus users are not notified of mandatory conditions and cannot turn them off.

Optional conditions can be turned on or off as required by Discoverer Plus users. They can also view a condition s formula, but they are not allowed to edit the formula.

Table 5-1 shows further differences between mandatory and optional conditions.

Table 5-1: Differences between Mandatory and Optional Conditions in Discoverer
Mandatory Condition	Optional Condition
Always applied to the results of a folder.	Applied to the results of a folder only if selected in Discoverer Plus.
Used by the Discoverer manager to permanently restrict the rows returned by a folder.	Provided by the Discoverer manager as a shortcut to help users build conditions more easily.
Invisible in Discoverer Plus.	Visible (but not editable) in Discoverer Plus.
When created in a complex folder, can reference items in the source folders.	When created in a complex folder, can reference only items in the complex folder.
Affects the result set (in the database) of the folder definition in the EUL.	Does not affect the result set (in the database) of the folder definition in the EUL (because an optional condition is used only when applied in Discoverer Plus).
When added, changed, or deleted, causes any summaries based on the folder to become invalid, because their result set no longer matches that of the folder. These summaries are set to Refresh required and must be refreshed to be made available again.	When added, changed, or deleted, has no affect on summaries based on the folder.

Hierarchies Hierarchies were discussed briefly earlier, but to quickly review: hierarchies are created between items in a business area to provide Discoverer end users with the ability to drill up to a more general level of detail or drill down to a finer level of detail. There are two kinds of hierarchies:
Item hierarchies Relationships between items other than dates.
Date hierarchies Relationships between date items. Oracle Discoverer Administrator contains a date hierarchy template and the ability for Discoverer Administrators to define custom date hierarchies.
Item classes Item classes are groups of items that share some similar properties. An item class enables you to define item properties once and then assign the item class to other items that share similar properties. Discoverer uses item classes to implement the following features: lists of values, alternative sorts, and drill-to-detail links. As the Discoverer manager, it is your responsibility to create suitable item classes to support these Discoverer features. You can create a different item class for each feature, or you can specify that Discoverer uses the same item class for more than one feature. Note that an item class to support an alternative sort must also support a list of values.
Summary folders Summary folders are a representation of queried data (created in Discoverer Administrator) that has been saved for reuse. The data is stored in the database in one of the following forms (depending on the version of Oracle):
Materialized views Discoverer uses materialized views to store summarized data in Oracle 8.1.7 (or later) Enterprise Edition databases.
Tables Discoverer uses tables to store summarized data in Oracle Standard Edition databases.

Summary folders improve the response time of a query because the query accesses preaggregated and prejoined data rather than querying against the detail database tables.

4x7 and Referenceware are registered trademarks of Books24x7, Inc.
Copyright ?1999-2005 Books24x7 Inc. - Feedback Privacy Policy (updated 09/2004)