Miscellaneous Technology

Oracle Discoverer is a business intelligence toolset that comprises:

·        Oracle9i Discoverer Administrator

·        Oracle9iAS Discoverer Plus

·        Oracle9iAS Discoverer Viewer

·        Oracle Discoverer Desktop

Figure 1-1 The Discoverer components

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.

Oracle9i Discoverer Administrator

Oracle9i 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 us 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.

Role of a Discoverer manager

Discoverer manager is responsible for:

·        the initial implementation of the Discoverer system

·        the ongoing administration and maintenance of the Discoverer system

Discoverer manager needs to understand how to design business areas that support company's decision-makers. On the database side, knowledge of data in the database, their location, how it is stored, and how it relates to other data is essential. On the business side, understanding of data the decision-makers require, what kinds of analysis is necessary, and how the final results should be presented for easy comprehension is desired.

Basic concepts for Discoverer managers

·        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. 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).

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

·       End user Layer (EUL)

The End User Layer (EUL) insulates Discoverer end users from the complexity and physical structure of the database. The EUL provides a business-focused view of the database that can be tailored 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.

·       Business Area

No user 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, we can create one or more business areas as containers of related information.

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

·       Folders and items

The tables and views loaded 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, the names of folders and items can be made meaningful 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. Complex folders can be created that contain items based on columns from multiple tables or views. Also, custom folders based on SQL statements which a user can write can be created.

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

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

End users can be restricted to analyze 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 can be used to decide which end users can create their own workbooks, and which end users can only use workbooks that have been created for them.

·       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 tables are loaded into a business area, Discoverer automatically creates default date hierarchies for date items. Users can create their own hierarchies for other items as well.

·       Summary folders

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

Summary folders can be created 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. Discoverer can also be directed 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.

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

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

These six steps are described in more detail below.

·        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 they like to see?

Ø        How would they like the information presented?

As a starting point, review the reports and information sources that users are currently using?

Quickly we can understand 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.

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

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

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

Having identified users' requirements, we can 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, we can group information with a common business purpose into a business area. Having created a business area, we must specify which database tables and views hold that information. This can be done by 'loading' the tables and views into the business area.

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

Specifically, we can

Ø        create optional and mandatory conditions to restrict the number of rows returned in a folder

Ø        create calculated items to relieve users of the task of creating complex calculations by providing them with ready-made computations

Ø        create joins to combine folders that were not automatically joined when tables were loaded from the database

Ø        combine folders into complex folders to completely hide joins and relational structures from users

Ø        create custom folders, to represent a result set returned by a SQL statement that you have entered as a folder with items

Ø        edit item names, descriptions, and other formatting information to make data easier to understood

Ø        Create item classes to support lists of values, alternative sorts, and drill to detail

Ø        create hierarchies to simplify drill-down operations

Ø        create summary tables (or let Discoverer automate summary management, or register existing summary tables) to maximize query performance

·        Grant business area access to users or roles (mandatory)

Having identified users' requirements, we can 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, 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.

·        Discoverer Deployment

Users' requirements will determine which of the Discoverer components need to be made available to make available in your company.

When identifying their requirements, we will probably realize 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.

Following table illustrates how to decide which Discoverer components to deploy:

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

·        Maintaining a Discoverer system

Small amount of ongoing maintenance is required to make sure that Discoverer continues to meet users' requirements.

Typically, will continue to refine business areas by:

Ø        adding new item classes, to support new lists of values, alternative sorts, and drill to detail

Ø        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

Ø        adding new optional and mandatory conditions, to restrict the number of rows returned in a folder

Ø        adding new calculated items, to provide users with ready-made computations that were not initially required

Ø        adding new complex folders, to simplify query creation

Ø        adding new custom folders, to meet users' requirements that cannot be met using conventional folders

Ø        adding new hierarchies, to enable users to analyze data in new ways

Ø        adding new summaries to resolve performance issues with particular queries

In addition to the above, we can 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, grant them access to the business areas they need to do their job

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

·        New features in Discoverer Administrator Version 9.0.2

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

Ø        Enhanced export - Export End User Layer (EUL) objects to a file using the Discoverer Administrator user interface. The EUL objects that you can export to a file include business areas, folders, item hierarchies, date hierarchies, item classes, workbook definitions (created in Discoverer Desktop and Discoverer Plus), PL/SQL function registration information, summary folders and the automated summary management (ASM) policy.

Ø        Alphabetical sorting - Sort folders and items when you load a business area. You can also sort folders in a business area and sort the items in a folder from within the Workarea.

Ø        EUL cross references - View the impact that deleting EUL objects will have on Discoverer workbooks. For example, Discoverer Administrator can tell you whether a folder you are deleting is referenced by a specific workbook and whether the workbook will be affected by deleting the folder

Ø        Improved list of values - Specify in Discoverer Administrator how Discoverer Plus users select and display LOV options. You can determine the maximum number of rows to display to an end user (for each array fetch of data from the database), when a list of values is expanded in Discoverer Plus. You can also specify to  hide duplicate values, cache the LOV once it has first been displayed and force end users to enter search criteria for long LOVs.

Configuring the Connect dialog for Administration Edition and Discoverer plus (for Windows)

Before connecting to Oracle Discoverer as an Oracle Applications User, configure the Oracle Discoverer Connect dialog to expect Oracle Applications users, as follows:

Select Tools | Options from the main menu to display the following dialog:

Select one of the following radio buttons:

Use the drop down list to select the Oracle Applications schema containing the Oracle Applications FND tables.

Enter the password for the Oracle Applications schema.

Click Next to display Step 3 of the wizard where you select the Default and Temporary Tablespaces for the new database user/schema:

Highlight the required Default and Temporary Tablespaces you want to use for the new Oracle Applications user. Click Finish

This creates the tables and views for the new Oracle Applications EUL and populates them with default data. The following message is displayed:

Click OK to display the following dialog:

Click No unless you want to install the tutorial data

The following dialog is displayed:

Click Yes to connect as the owner of the EUL you have just created
Or click No to remain connected as the dba using the current database connection.

If you clicked Yes at the previous step, you are now connected to the Oracle Applications EUL just created (as the EUL owner).

You can now grant Task Privileges to an Oracle Applications user so that they can now administer this Oracle Applications EUL.

Granting Task Privileges

This section describes how to grant (or deny) the privilege to perform certain tasks as an Oracle Applications user.

The Privileges dialog box enables you to set task privileges. To open the Privileges dialog box, choose Tools | Privileges (or click the Privileges icon on the toolbar).

The Privileges page enables you to specify the task privileges granted to a responsibility or user.

The User/Responsibilities page enables you to grant task privileges to a user or responsibility.

Maintaining Assigned Privileges
 

If you want the list to include Oracle Applications users, select the Users check box (otherwise, clear it).

If you want the list to include Oracle Applications Responsibilities, select the Responsibility check box (otherwise, clear it).

The list is sorted alphabetically, with users at the top and responsibilities next.

Select the task privilege that you want to grant (or deny) to a set of users or responsibilities (from the drop-down list).

When you select a privilege from the drop-down list, a brief description of the privilege appears on the right-hand side of the dialog box.

Grant or deny the task privilege as required.

To grant a user or responsibility the task privilege, tick the relevant check box in the list.

To deny a user or responsibility the task privilege, clear the relevant check box in the list.

Preparing to Build a New Business Area

Load Wizard is used to build a New Business Area. The Load Wizard provides a user-friendly interface that enables to quickly:

·        Name and describe the business area

·        Load metadata into the business area

·        Automatically create joins from existing relationships between tables

·        Automatically create lists of values for items

Load Wizard: Step 1, Specifying the Metadata Source

The first page of the Load Wizard enables you to specify the source of the metadata that you want to populate the business area with.

Load Wizard: Step 1 gives the following choice:

Create a new business area
 

This option enables you to start the process for creating a new business area from scratch.

Open an existing business area
 

This option enables to open an existing business area.

.

Click Create a new business area.

Load Wizard: Step 1 adds the question, "Where do you want to load the metadata from?"

Specify the location of the metadata:

On-line dictionary
 

This option enables you to load tables and views from the standard Oracle dictionary.

Gateway
This option enables to choose the metadata source from the registered gateways.

Load Wizard: Step 2

Load Wizard: Step 2 will appear differently depending on whether On-line Dictionary or Gateway is selected on Load Wizard: Step 1.

Load Wizard: Step 2 (for On-line Dictionary)

Choose the database link from the Select a Database Link drop-down list.

By default, the database link is set to <Default Database>. This is the default database for the current user ID. The drop-down list only shows the databases that current user ID can connect to.

Select the users, whose objects to be loaded into the business area, from the Select the users you want to load list.

The users that appear in this list are those that have access to the database selected above.

Specify the pattern that user objects must match in order to be loaded in to the business area (in the Load user objects that match field).

By default the % symbol is specified. The % symbol is a wildcard that matches any character or string of characters. If you want to reduce the number of objects that can be loaded from the database, use the wildcard in combination with other characters as follows:

To load all objects, enter %.

To find all objects beginning with D, enter D%.

To find all objects ending with AND, enter %AND.

To find objects beginning with A and having a four letter name,
enter A_ _ _.

If you want to specify the type of tables to be loaded (for example, whether public or private, or whether owned by or accessible to the users you select) click Options and go to

By default, the Load Wizard will load only:

Tables owned by the users specified

Private tables

 Online Dictionary Options

This dialog box enables to specify the type of tables and views (from the user ID's database) that can be loaded.

Load Wizard: Step 3, Selecting Tables and Views

Load Wizard: Step 3 enables to select the specific tables and views (schema objects) that need to be loaded into the business area. The selections made in Load Wizard: Step 2 determine the tables and views that are available for selection in the Load Wizard: Step 3.

To change the selections made in Load Wizard: Step 2, click Back.

The left side of the wizard displays a hierarchical list of users (via the on-line dictionary) or schemas (via gateway) and the tables and views that are available to load into the business area.

Load Wizard: Step 4 enables to:

·        control how the database column names are mapped to Discoverer item names

·        specify how joins are created between items

·        specify that Automated Summary Management (ASM) will recommend and create summaries after the load process, based on the folders that are created.

If you select this option Bulk Load may take longer to complete, but users should benefit from improved query performance. Do not select this option if you are going to run ASM later, or if you have limited free space available in your database.

·        specify how date hierarchies are generated

·        specify the default aggregate on data points

·        specify which types of items you want Discoverer Administration Edition to generate lists of values for.

How Items are loaded (Axis Items or Data Point Items)

Items are loaded as data points if they are DECIMALS (that is, a NUMBER data type) and have a non-zero precision. Integer numbers, all keys, and all other data types are loaded as axis items, with a default position of "Top."

In Discoverer Plus, whether an item is an axis item or datapoint affects the items' default placement on cross tab worksheets, as follows:

Data points are shown with easy to pick aggregate functions and appear by default in the center of cross tabular reports, because they are usually numbers that users want to analyze. Another term for data points is measures.

Axis items can have visible lists of values; data points do not have visible lists of values. Axis items appear on the page, top, or side of cross tab reports by default. Another term for axis items is dimensions.

Load Wizard: Step 5 enables to name and describe the business area

·        Specify a name for the Business Area in the Name field.

·        Specify a description for the Business Area in the Description field. This step is optional.

Discoverer Administration Edition displays a progress indicator while it is generating new Business Area (and summaries, if appropriate). When it is finished, the progress indicator disappears and the new Business Area is displayed on the Data page of the work area.

Open an Existing Business Area

Select a business area or click Select All to select all the business areas.

Click Finish.

The work area window opens with the Data tab selected, listing the business areas you selected.

Open an Existing Business Area

Exporting a Business Area to a File

Discoverer Administration Edition enables to export a business area to a file. This can be useful when copying business areas between EULs or when archiving data The following steps show how to export a business area to a file:

On the Data page of the work area, select the business area that you want to export.

Choose File | Export or click the right button of your mouse and select Export from the Pop-up menu
this opens the Save as dialog box as shown below

Exporting the Business Area

The Save as type displays the file format the Business Area is saved in, which is Discoverer EUL(TM) Export File (*.eex)

Specify the location, filename and file format for the exported Business Area. If Discoverer EUL(TM) Export File format is chosen, give the file an EEX extension. For example, Export_file.eex. Click Save.

Importing EUL elements from a file

Discoverer Administration Edition enables you to import EUL elements (E.g. Business Areas, Folders, Functions etc.) from other EULs. To guide you through the process, Discoverer uses the

Import Wizard.

Identifiers are unique names used by Discoverer to identify unique EUL elements (and Workbook elements in Discoverer Plus).

When EUL elements are imported, Discoverer uses Identifiers to locate elements referring to the same business objects. This enables customized (or patched) elements to be preserved. For example, a folder named 'Sales' in EUL `A' may refer to the same folder named 'Sales Figures' in EUL `B'. Both folders have the same Identifier and can therefore be recognized as referring to the same element.

Identifiers are visible in the Discoverer Administration Edition but are hidden from Discoverer Plus users. Supported File Formats are:

·        Discoverer 4.1 supports the following import EUL formats:

·        Discoverer Export Files (*.EEX) exported using Discoverer 3.1 and earlier.

·        Discoverer Export Files created using Discoverer release 4.1 and later, in XML format, (which also have an *.EEX file extension).

The Import Wizard provides a user-friendly interface. Select the files to be imported.

Import Wizard can be started at any time during a Discoverer Administration Edition session.

Choose File | Import.
This opens the Import Wizard: Step 1, as shown below:

Import Wizard: Step 1 enables to choose which EUL files to import.

·        Click Add to display the file locate dialog.

·        Locate and select one or more import files - selected files appear in the list.

Import Wizard: Step 2 enables to choose how to process Conceptually Identical Objects from another EUL.

Choosing match options

Specify what action should occur when objects match:
The term `object' also applies to `element' (E.g. Folders, Items, Functions etc.)

Rename the imported object
 

Renames imported elements to differentiate them from your existing elements. For example, if you are importing a matching Folder named `Sales', selecting this option would rename the imported Folder to `Sales1' leaving you with two Folders, your existing one named `Sales' plus the imported Folder named `Sales1'.

Rename the existing object

Renames your existing elements to differentiate them from imported elements.
For example, if you are importing a matching Folder named `Sales', selecting this option would rename your existing Folder to `Sales1' leaving you with two Folders, your existing one renamed `Sales1' plus the imported Folder unchanged as `Sales'.

Do not import the matching object
 

Will not import elements that match your existing elements. For example, if you are importing a matching Folder named `Sales', selecting this option would not import the matching Folder `Sales' leaving you with your existing Folder unchanged as `Sales'

Refresh the object
 

Refreshes any matching elements. For example, if you are importing a matching Folder named `Sales', selecting this option would update your existing Folder `Sales' leaving you with one Folder named `Sales'. Specify whether to preserve display related properties (only available if the Refresh the object radio button is selected)

Preserve display related properties

The following item properties will not be refreshed if this check box is selected:
- Default position

Specify how objects to be matched:

By Identifier
 

Match objects using element Identifiers. To ensure that Conceptually Identical Objects are matched correctly, use this option instead of the Display Name option.

By Display Name
Match objects using the element Display Name.
 

This option is included here for backwards compatibility – It is recommended to match By Identifier.

Specify whether the current user should take ownership of the imported workbooks

Always take ownership of imported workbooks
 

If this radio button is set then any imported workbooks will become owned by the current user.

Only take ownership if original owner cannot be found
 

If this radio button is set then any imported workbooks will have their owner changed to the current user only if the original owner cannot be found in the current database.

Import Wizard: Step 3 enables to start the import and monitor its status as each EUL element is processed.

Choose Start to start the import.

The status bar at the top of the wizard shows the percentage of the import that has completed. The Log window displays status messages about the import process.

These messages provide information about imported elements.

These messages are warnings about potential problems - use the Cancel option to abort the import if required.

Save log...
After a completed import, this option enables to save the status information to a text file. Click `Save log... and choose a file name and location.

Cancel
This option aborts the Import - you may want to use this option if there are warning messages in the Log window.

Choose Finish.

If you perform a complete Import, your Discoverer Data window is updated to reflect the elements imported, according to the matching options that you have chosen

Copying Business Areas between EULs

It might be needed to copy a business area from one EUL to another, for example, from a test system to a production system. The following steps show how to do this:

·        Open the business area(s) you want to move.

·        On the Data page of the work area, select the business area(s) you want to move.

·        Choose File | Export.

·        Choose File | Connect. Connect to the EUL that you want to move the business area into.

·       Choose File | Import.

Editing Business Area Properties

The following steps show how to edit a business area's properties:

·        Open the Business Area Properties dialog box

There are four ways of doing this:

Double-click
Double-click the relevant business area icon on the Data page.

Popup Menu
Right-click the relevant business area icon on the Data page and choose Properties on the popup menu.

Toolbar Icon
Click the relevant business area on the Data page and click the Properties tool bar icon ()

Menu
Click the relevant business area icon on the Data page and choose Edit | Properties.

Figure 7-14 Business Area Properties with General Tab Selected
 

Set the business area properties as required.

For more information on the fields on this dialog box, click Help.

Click OK.

Deleting a Business Area

Open the Confirm Business Area Delete
There are two ways of doing this:

Popup Menu
Right-click the relevant business area icon on the Data page and choose Delete Business Area on the popup menu.

Menu
Click the relevant business area icon on the Data page and choose Edit | Delete.

Specify the scope of deletion that you require:

Delete this Business Area
 

This option removes the Business Area itself, but does not delete the contents of the Business Area. The folders that are in the Business Area remain in the EUL.

Folders that exist in the EUL but do not belong to a Business Area are known as Orphan Folders.

Delete this Business Area and its Folders
 

This option removes the business area and all of the folders contained in that business area. It does not remove folders that are part of any other business area. This is the default option and is usually recommended.

Click Impact (optional).
This displays the Impact dialog box which shows the other objects that may be affected by deleting this business area.

Click Yes or No.

Click Yes to delete the selected business area based on the choices you have made.

Click No to close the Confirm Business Area Delete dialog box without deleting the business area.

Managing Folders & Items:

Folders represent a result set of data, much like a database view. It is useful to think of folders as a SQL statement that returns the result set. In fact the SQL is actually stored in the End User Layer, and is used in SQL generation.

The assignment of a folder to a business area should be considered temporary and changeable. A folder can be used in multiple business areas. In these cases, the folder's definition is still unique: it is simply assigned to multiple business areas. Folders can be removed from all business areas, but sustained in the EUL. Such folders are known as orphan folders.

Folder Types

There are three types of folder:

·        Simple Folders

·        Complex Folders

·        Custom Folders

Whether a folder is simple, custom, or complex is only important to the administrator. There is no difference to the end user. Even in Discoverer Administration Edition, there is very little difference in the behaviors of these different types of folders. All folders can include calculations, joins, conditions, summaries, item classes, and hierarchies.

Simple Folders

Simple folders are created by loading a folder from the database or Oracle Designer. They map directly onto a single table or view. Items in simple folders represent columns or calculations on other items in the folder.

Complex Folders

Complex folders consist of items from one or more other folders. Complex folders enable you to create a combined view of data from multiple folders.

This can simplify the business area without creating a new database view. For example, we can create a Complex folder called Dept-Emp which has columns from both the DEPT and EMP tables. The user then only has to select from one folder, not two. All concepts of relational joins are hidden to the user.

For two Items from different Folders to belong to the same Complex Folder, a Join condition must exist between the two Folders.

Refresh
Custom Folders are refreshed by editing and validating the existing SQL. Simple folders are refreshed when the Business Area is refreshed.

Items
 

Items generated in a Custom folder do not have an Item Formula property where you can change the SQL expression for the item. Therefore, you cannot edit the formula of an item in a Custom folder, except by editing the SQL for the whole folder.

Properties
Properties for Custom folders do not include database, owner, and table name, nor do they have component source folders. The Properties dialog box for a Custom folder, displays a field named

Custom SQL, which contains the custom SQL statement used to generate the custom folder.

Creating Custom Folders

This section describes how to create a custom folder:

On the Data page of the work area, select a business area (or any object within a business area).

Choose Insert | Folder | Custom.
This opens the Custom Folder dialog box

Specify the SQL statement.
Specify the name of the folder in the Name field.

Click Validate SQL to ensure you have entered valid SQL.

Click OK.

This validates the SQL statement and saves the custom folder. Note that Discoverer Administration Edition allows save the custom folder even if the SQL is invalid. This enables to insert the SQL before the actual database objects are created or made available. However, end users will not be able to query the object until the SQL is valid.

Custom Folder Examples

This section consists of the following examples:

Example 1: Synonyms

Example 2: Set Operator in a Folder Definition

Example 3: ODBC-specific SQL Syntax

Example 4: Subquery in a Folder Definition

Example 5: Optimizer Hint

Example 6: CONNECT BY Clauses

Example 7: Column Expressions

Example 8: Speeding Up Lists of Values

Example 1: Synonyms

SELECT ENAME, JOB, SAL FROM EMP@ORCL

where EMP is a synonym that points to the EMP table on another database.

Example 2: Set Operator in a Folder Definition

SELECT "COMPANY1" COMPANY, ENAME, SAL FROM EMP@HQ
 

UNION
 

SELECT "COMPANY2", ENAME, SAL FROM EMP@REGIONA

where HQ and REGIONA are database links for remote databases. The result set is the union of all employees with a column named COMPANY1 to show which company they are from.

Example 3: ODBC-specific SQL Syntax

SELECT ENAME, DNAME FROM
 

{EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO}

This example uses the ODBC outer join syntax.

Editing Folder Properties

Folder properties are accessible through Folder Properties dialog boxes. This section shows how to enhance the user's view of the data by editing folder properties.

Folders can be assigned to and removed from any business area, and can be included in multiple business areas. However, for each folder, there is only one definition, shared across all the business areas that include it

When using the Folder Properties dialog box, can have changes saved as soon as you enter them by ticking Automatically save changes after each edit. With this option ticked, you don't have to click OK or Apply after each edit.

You can change folder names at any time without affecting its logical structure, because Discoverer uses an internal way of identifying folders which does not depend on the folder name. Names only affect the appearance of the information in the business area. However, Folder names must be unique within the EUL and Item names must be unique within a particular Folder.

Items:

An Item, is a representation of a database table's column, in the EUL. By presenting columns as Items, Discoverer enables the administrator to make formatting changes, name changes and other similar changes enabling the user to clearly read the data. Items are stored in folders and can be created, deleted, and moved among different folders.

Joins:

In Discoverer, a Join relates two folders using one or more common items. This is similar to a Join in the database which relates two tables using common columns.

The Joins created in Discoverer Administration Edition affect the combinations of items that can be selected during the following operations:

·        Creating a worksheet in Discoverer Plus.

·        Creating a Complex Folder in Discoverer Administration Edition.

Joins are defined with a master and detail end. The master end is the folder that has one row, for which there are several detail rows. For example, the relationship of a master row in the Department folder to the many detail rows in the Employee folder.

It is important to make sure you define the Join with the correct folders at the master and detail ends. If you set up this relationship incorrectly, it adversely affects what combinations of folders a user can combine in a single query, which in cases of queries with three or more folders can, in some circumstances, lead to misleading or incorrect results. It can also affect whether summary tables can be used to speed up queries.

Usually joins are one-to-many, where one row in the master folder is joined to multiple rows in the detail folder.

Occasionally there are one-to-one and many-to-many joins. Many-to-many joins are not supported directly in Discoverer, or in any relational system, although they can always be worked to be transformed to multiple many-to-one joins.

Users of Discoverer Plus cannot set up their own join conditions. However, they can decide which join path to use if more than one join exists.

Creating Joins

This section describes how to create Joins.

Creating a Join starts with either selecting the Item that will be the Master Item, or the Folder it belongs to. If you choose Insert | Join before selecting an Item or Folder, a selection dialog box opens for selecting the Item that will be the Master Item.

On the Data page of the work area, select the Item that you want to be the Master Item.

Choose Insert | Join.

If you did not select the Master Item in step 1, the first New Join dialog box. Select the Folder that contains the Item that you want to be the Master Item and click OK.

This opens the main New Join dialog box. The Master Item is displayed in the Master Folder column.

Specify the Join type using the Operator field.

Operator-- use the drop-down list to select an operator for the type of join you want to create. Operators include:

Detail Items

--select the folder that contains the detail item from the drop-down list. The detail item can be in a folder in either the same business area as the master item, or in a folder in a different business area. The syntax for the value in Detail Items is folder name.item name.

Name--name the join you are creating.

Description--text field for describing the join you are creating.

Multi-item--replaces the New Join dialog with a New Multi-item Join dialog with room for multiple rows of join criteria, which can be added and deleted using the Add and Delete buttons

Options--displays a dialog box for defining outer join conditions

This dialog presents the following options:

Outer join on detail--creates an outer join. Returns all master rows that have no corresponding detail items, plus all matching master and detail rows.

Outer join on master--creates an outer join. Returns all detail rows that have no corresponding master, plus all matching detail and master rows.

Calculations:

Typical business calculations include values such as profit margins, average revenues per month, expected sales, and percent of profit by product type. You can represent these business calculations in Discoverer by creating Calculation Items. Once created, Calculation Items behave much like any other Item in a Folder and can be used in Conditions, Summaries, Lists of Values, Joins, and other Calculation Items.

Calculation Items are created using expressions that can contain:

·        Existing Items

·        Operators

·        Literals

·        Functions

There are three types of calculations:

·        Derived Items

·        Aggregate Calculations

·        Aggregate Derived Items

Derived items and aggregate calculations appear with different symbols to differentiate them.

A derived item is a non-aggregate expression that appears and acts exactly like any other item in the folder. Derived items can be axis items or data points and can be used anywhere one would use an ordinary item. They are static, in that their value depends only on the value of the other items in the same row, and when computed will be the same regardless of what other items are selected in the user's query.

Examples of Derived Items:

Sal*12+NVL(Comm,0)

Initcap(Ename)

1

Sysdate-7

Aggregate Calculations

If the formula of a new item contains an aggregate or group function such as SUM, AVG, MAX, MIN, or COUNT, and the items it aggregates are in the current folder, the item is created as an aggregate calculation.

Examples of Aggregate Calculations:

SUM(Sal)*12

SUM(Comm)/SUM(Sal)

AVG(Monthly Sales)

Aggregate calculations are dynamic, in that their value depends on the other items selected in the Discoverer Plus worksheet in which they are used, because this affects the axis items that are grouped together and hence the number of rows that are aggregated. This is particularly important in the case of calculations that are ratios of two aggregates.

For example to calculate Margin, one would use the calculation SUM(Profit)/SUM(Sales) rather than Profit/Sales. Used in a query, the latter would result in SUM(Profit/Sales), which produces a different result from SUM(Profit)/SUM(Sales). Data points should always be summed before a ratio is computed.

Comments