OracleAS Discoverer 10
is an end-
Fewer developers and end users work with OracleAS Discoverer 10
than with other Oracle tools such as Oracle Forms and Oracle
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
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
If you logged in as a user with CREATE USER privileges, the radio button
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
You are prompted to either create a new business area or
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
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).
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.
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
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
The final page of the Load Wizard is where the business area is named, and table and view names can be
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
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
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.
Now is a good time to take a small
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 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
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
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
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.
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 were discussed
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 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
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
4x7 and Referenceware are registered trademarks of Books24x7, Inc.