Helen Dwight
Product Manager
Oracle Corporation

1.0 Introduction

Oracle Discoverer R3.0 is an ad hoc query, reporting, analysis and web publishing tool for any skill level of end user in any organization.

Oracle Discoverer R3.0 allows end users to extract information from their data warehouse or online transaction processing system using only familiar business-like terminology. Access to relational data source objects and the creation of SQL is completely hidden from end users by a meta layer called the End User Layer™(EUL).

All decision support tools in the market use the concept of a meta layer to hide the underlying complex structures of the data warehouse or online transaction processing system. The differentiation between these tools and Oracle Discoverer R3.0 tends to be the length of time, effort and money spent on setting up the meta layer and providing longer term maintenance.

This paper focuses on the features implemented within the administration component of Discoverer that provide significant benefits to an IS organization in terms of low setup and on going maintenance.

2.0 Low Maintenance End User Layer;

Any organization purchasing a decision support tool today will find themselves with the task of installing and setting up the meta layer component of the software so that end users can get meaningful access to their data warehouse or online system. This is generally a task carried out either by the IS department or a computer literate business user. If an organization wants to take advantage of the data held in their data warehouse or online system, then they need to go through the process of creating a meta layer in order to make the data user friendly and thus gain maximum benefit from their investment.

Administrators of decision support tools are looking for a product that will give them maximum control, security and flexibility whilst at the same time requiring very little work in terms of initial setup and on going maintenance.

What benefits does Discoverer provide in this area? Discoverer has been developed to ensure that meta layer setup and maintenance time are minimal. The additional benefit in using Discoverer’s End User Layer is that it will be shared by other products in the Oracle family of Business Intelligence tools: Applications Data Warehouse™, Express Analyzer™ and Express Objects™. This integration coupled with the integration of other Oracle products such as the Oracle7 and Oracle8 servers and Oracle Designer/2000™, protects any investment an organization makes in the Oracle Warehouse™.

Information from a number of surveys shows there are some key criteria for administering a decision support tool. It should provide:

• Ease of Use
• Fast Setup
• Low Maintenance

How effective is Discoverer in these areas?

2.1 Ease of Use

Discoverer’s End User Layer is created and maintained using the Administration user interface. From the outset, development focus was placed on the usability of Discoverer, from an administrator as well as user perspective.

2.1.1 Usability Testing

Usability testing is an integral part of the development process. The interface has been designed by a team of experienced User Interface professionals recognized worldwide for their expertise. Storyboards were created, revised and repeatedly tested with customers before any code was produced. Focus groups were recruited to give direct feedback on the process of creating complex queries and performing powerful analysis. The emphasis on usability continues throughout the development process, with extensive testing being carried out at Oracle’s sophisticated usability laboratory.

As a result of the Usability focus, Discoverer is recognized as the easiest to use administration interface in the market today.

2.1.2 Wizards, Cue Cards and Quick Tour (CBT)

Discoverer uses a modern wizard-based approach which leads the administrator through each step of the administration process thus making them immediately more productive. Additional cue cards and computer based training ensure the training curve is minimal, enabling administrators to setup the End User Layer in the shortest amount of time.

2.1.3 Centralized Repository

The End User Layer is a set of tables created in a centralized relational database server. The centralized repository has a number of benefits from an ease of use perspective especially when using the Oracle server as the meta layer storage area.

Firstly, the initial creation of the meta layer can be achieved from any PC which is connected to the network using SQL*Net .

Secondly, the meta layer is created once and stored in a central repository as opposed to being stored, potentially, many times with a file based approach. If the data needs to be amended, it is amended once and all changes are immediately propagated to the users.

Finally, Discoverer takes advantage of the security and control mechanisms inherent in the database architecture. For example, with Oracle7 and Oracle8 the Database Administrator authority controls access to underlying database objects and Discoverer users connect with a database userid. There is no additional tool based security layer to maintain. The use of Oracle7 and Oracle8 roles and resource profiles are supported with Discoverer. Summary tables can be maintained automatically and it is possible to predict how long a query will run prior to running the query using the Oracle7 or Oracle8 cost based optimizer in combination with statistical techniques.

2.2 Fast Setup

Besides having an easy to use interface, it is important to be able to setup the meta layer in as little time as possible with as much automation as possible, thus enabling users to access their data more quickly and allowing administrators time for more important tasks. This is an area that Discoverer excels in.

2.2.1 Fast Initial Load

Discoverer is able to logically group together related sets of information held in the End User Layer in the form of Business Areas. A Business Area enables end users to find the information they are looking for quickly and without getting confused with too much information. A Business Area may contain, for example, personal data, departmental data (Sales, Marketing, Finance), data relating to a specific topic (Customer information). The possibilities are endless. Physically a Business Area can contain as many references to database objects as desired. A user or role may have many business areas associated them.

Business Areas are created in seconds using Discoverer’s fast, one button, bulk-load option. There are a number of ways to populate Business Areas using the fast, bulk-load facility, all of which save vast amounts of time and effort for administrators:

• Load existing database object definitions from the on-line dictionary
• Load objects from existing business areas, thus reusing work previously carried out
• Load objects from the meta data dictionary of Oracle Designer/2000. A typical data warehouse today contains two types of meta-data :

• The meta data that defines the relationship between the source (OLTP) systems and the Warehouse itself, (which contains the models of both systems and any relationships/transformation processes between them)
• The front end meta data for an end user perspective of the data structures.

Maintaining two meta-layers is a time-consuming and error-prone process. To resolve this, integration between Discoverer and Oracle Designer/2000 enables you to populate the front-end meta-layer directly from Oracle Designer/2000, thus reducing the need to duplicate the models and effort required for maintenance of the Warehouse.

2.2.2 Default, Automated End User Layer Settings

In addition to fast, bulk load of objects into Business Areas, Discoverer automatically sets default values for :

• Folders, which equate to database tables or views
• Items, which equate to database columns
• Descriptions held against Folders and Items. These descriptions are derived either from comments held against the database objects in the on-line dictionary or from values held in the Oracle Designer/2000 meta dictionary
• Properties for both Folders and Items. For example, default column width, formula
• User Access to the Business Areas depending on the userid that owns the underlying database objects
• Join Relationships between database objects based on primary and foreign key relationships held within the on-line dictionary or the Designer/2000 repository. It also creates them based on matching item names
• Drill Relationships between database objects based on primary and foreign key relationships held within the on-line dictionary or the Designer/2000 repository
• User Preferences. A default user preference is supplied which all userids share unless they need their own specific set of preferences created

The defaults described above are implemented as soon as objects are loaded into the Business Area. The major benefit of the extensive defaulting and one button load is that the administrator can reuse existing meta data and create Business Areas that are fully ready for use by end users in a matter of seconds.

Based on the organizational requirements, there are a number of optional activities which may need to be carried out by an administrator to enhance and maintain the End User Layer even further. All of these options are very easy to implement due to the intuitive ‘wizard’ style interface. Extensive on-line help is supplied … just in case.

2.2.2.1 Folder and Item Properties

Additional naming conventions, layout positions, list of values etc. may be defined for each Folder and Item by simply double clicking on the object in question.

2.2.2.2 Complex Folders and Items

Instead of creating a view in the on-line dictionary and loading this into a Business Area an administrator can simply create a ‘view’ within the End User Layer by creating a user defined Folder and dragging and dropping items into it.

2.2.2.3 Join Definitons

Should additional join definitions be required over and above those created automatically, these can be simply entered using a few button clicks.

2.2.2.4 Hierarchy Definitions

There are 3 distinct types of hierarchy that may be defined:

• Date
  the ability to drill down and up through a series of dates e.g. Year -> Quarter -> Month
  
• Item
  the ability to drill between items in either the same or different folders e.g. Region -> State
  
• Value
  the ability to navigate between parent and child hierarchies in a single object that has a self referencing join e.g. Managers and their Employees

Again, the ability to define these is through a step-by-step ‘wizard’ interface - the hierarchy wizard.

2.2.2.5 Item Classes

The administrator can define a different collation or sequencing period. This particular feature is useful when dealing with financial data. Instead of collating data by calendar year, for example, item classes (or domains) allow users to sort data by fiscal year.

2.2.2.6 User Preferences

Discoverer supplies a default user preference for all database userids that do not have their own preference. The preferences interface enables such options as specifying upper limits on maximum number of rows to retrieve and maximum amount of time to spend retrieving data. It also sets thresholds for when summary tables should be used and when query prediction should be used.

2.2.2.7 Public Queries

The administrator may share commonly accessed queries amongst all Discoverer users.

2.3 Low Maintenance

But, are all tools as easy to maintain? Discoverer was developed specifically with low maintenance requirements in mind.

2.3.1 Simple Refresh

The one button refresh will automatically synchronize the End User Layer with the online dictionary so that when database changes occur, the End User Layer can be easily updated with the press of a button.

2.3.2 Summarization Management

Discoverer has a feature called Automatic Summary Redirection which will redirect a query to a pre-aggregated table, if it exists in the database, instead of performing the aggregation on the fly. In order for this feature to be available, summary tables require to be either created and managed in the End User Layer or existing summary tables require to be registered in the End User Layer.

Creating and automatically maintaining summary tables is extremely easy within Discoverer. Firstly, the tool keeps a historical record of queries submitted and will identify candidate summary tables to the administrator. This reduces the time and effort needed for an administrator to identify candidate summary tables him/herself.

Secondly, the ‘wizard’ interface guides the administrator step-by-step through the creation of a summary table by prompting for axis, measures and dimensions. This ensures that the summary is created correctly.

Thirdly, the administrator is prompted for the frequency at which these summary tables should be refreshed - this could be, for example, every day, week, month and at specific time periods. The benefit is that the administrator can spend their time more productively whilst the database monitors and updates the summary table information automatically. (Note: Automatic Summary Table Management is only available when using Discoverer against Oracle).

Finally, the table is automatically created. An interface is available for specifying the existence of non-Discoverer created summary tables. This means that the tables may be used with the Automatic Summary Redirection feature described above.

3.0 Summary

The Discoverer End User Layer is a server based, low-maintenance, powerful mechanism for providing End Users with a meaningful interface to their production data or data warehouse systems. It provides an extensive amount of ‘default’ functionality which allows administrators to setup systems rapidly so that End Users can focus on the task in hand. For example:

Bulk Load facility for Folders Default Folder and Item Names
Default Item Properties e.g. Format Mask, Width, Report Heading
Default Join Conditions
Default Descriptions
Default Preferences
Default Access Control
Default Drill Relationships

Initial End User Layer setup is not the only thing that you should take into consideration. Any meta-layer needs to be maintained once it has been setup and this is often one of the most time consuming tasks for the Information Systems department. Wouldn’t it be great if this maintenance could be carried out by the End Users themselves? At many sites, the System Administrator who takes responsibility for the maintenance of the End User Layer is a non IS user.

Organizations can fully exploit the fast setup and low maintenance of Discoverer through the intuitive ‘wizard’ interface and the Oracle server which provides multi-user access, non-blocking query, security, resource profile and role