|
Low Maintenance Administration with Oracle Discoverer R3.0
Helen Dwight
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 BI tools; Applications Data Warehouse, Express Analyzer, Express Objects and Discoverer. This integration coupled with the integration of other Oracle products such as the Oracle7 server 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:
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. Discoverer supports Oracle directly and non-Oracle SQL data sources via ODBC. 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 or/and ODBC. 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 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 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 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:
Maintaining two meta-layers is a time-consuming and error-prone process. To resolve this, integration between Discoverer and Oracle Designer/2000TM 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 :
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:
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-Odysseus 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:
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 |