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

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.