12.2 Query and Analysis | Oracle Application Server 10g Essentials

Business analysts often want to pose their own "what-if" questions, such as:

What sales volume occurred in the Midwest subregions in 2004?
How did sales compare to 2003?

To answer such questions, analysts need ad hoc query tools that can slice and dice the data, pivot the data, and drill down to the detailed level. Initially developed as client-server tools, tools of this kind are now mostly browser-based (though they provide similar functionality to the earlier versions). Because ease of use is very important to most business analysts, these tools are designed in such a way that queries are typically posed by selecting from lists of available data (tables and contents) and analysis selections or icons. Users need to know where their data is stored and what it represents. SQL is generated behind the scenes.

12.2.1 OracleAS Discoverer

Oracle's tool for query and analysis is OracleAS Discoverer, which supports several different client interfaces for different architectures:

Discoverer Desktop: This interface is the oldest of the three. It is a client-server version and isn't part of Oracle Application Server.
Discover Plus: This interface is browser-based and is part of Oracle Application Server. It is deployed via a Java applet.
Discoverer Viewer: This more limited interface is an HTML browser client that is also part of Oracle Application Server.

Discoverer Plus and Discoverer Viewer are much more popular than Discoverer Desktop and are the focus of this chapter.

Each Discoverer interface leverages a common End User Layer. The EUL is a metadata layer that provides business definitions, hiding underlying technical descriptions while providing a map to those specific database fields. Although OracleAS Discoverer is most often used to query an Oracle relational database, it can also leverage Heterogeneous Services support in the Oracle database and use ODBC to connect to non-Oracle databases. Note, however, that some capabilities tied to the Oracle database ”in particular, the management of summary tables and query prediction (described later in this section) ”aren't available when OracleAS Discoverer is implemented via ODBC.

OracleAS Discoverer access to data is controlled through security that is regulated at both the application level and the database level through defined roles. OracleAS Discoverer can leverage the OracleAS Single Sign-On, and can detect and change expired database passwords.

Information concerning connections to the database is stored in the Oracle Application Server Metadata Repository, which is a part of the OracleAS Infrastructure introduced in Chapter 2. This information includes the database name , database username and password, Oracle Applications responsibility (included only when used with Oracle's ERP Applications), language, and metadata information. Connections can be private and available to specific users, or can be public and available to all OracleAS Discoverer users. Users can create their own private connections.

As such tools gain importance in making day-to-day decisions, you may want to consider highly available deployment. If you are deploying OracleAS Discoverer to Oracle Application Server in a highly available manner, make sure to do the following:

Configure the Oracle Process Manager and Notification Server to monitor and restart OracleAS Discoverer processes on each middle- tier node. OPMN is described in Chapter 2.
Use the OracleAS Web Cache for load-balancing OracleAS Discoverer requests . OracleAS Web Cache and the other Oracle Application Server caches are described in Chapter 7.

12.2.1.1 Manipulating and displaying data

Discoverer Plus is a browser-based ad hoc query tool that is deployed via a Java applet. This tool provides an easy-to-use interface for picking and choosing data items (exposed via the EUL) around which to build queries. These queries can be stored as "workbooks" for reuse as the underlying data changes. Business analysts can manipulate these workbooks or simply view the contents, depending on their access rights. Workbooks can be scheduled for execution at specific times using OracleAS Discoverer's Scheduling Manager.

Discoverer Plus's wizards can create more than 50 different graph types for viewing the data in a graphical form (the same graph types described earlier in Section 12.1.1). Figure 12-2 shows a typical OracleAS Discoverer view of data and an associated graph.

You can generate OracleAS Discoverer's reports with Discoverer Plus and deploy them to the Web as HTML files. You can also export them from Discoverer Plus as Excel files, PDF files, or XML for use with Oracle Reports ' distribution capabilities. Information passed via XML includes layout, formatting, exceptions, and parameters.

Figure 12-2. OracleAS Discoverer showing data and associated graph

Discoverer Viewer is an HTML-based browser client that is used mainly to view workbooks and worksheets, although some additional capabilities (e.g., sorting) are provided. The Viewer can also export data to formats such as XML and Excel. Discoverer Viewer can be customized to meet corporate standards using a CSS file to define fonts, colors, and graphics. XML is used to represent application state; the XSL is used for formatting the user interface.

A Discoverer Portlet Provider included with Oracle Application Server allows you to publish workbooks to OracleAS Portal. Two types of portlets are provided:

Worksheet: This portlet can contain tabular or cross-tabular data, graphs, or both.
List of workbooks: This portlet contains the names of available workbooks.

OracleAS Discoverer and OracleAS Portal

You might want to leverage Oracle Reports when deploying OracleAS Discoverer results to the OracleAS Portal framework in order to display data in a more visually pleasing way. You can do this as follows :

Export your OracleAS Discoverer worksheet in Oracle Reports XML format.
Within Oracle Report Builder, modify the layout to give the worksheet a more "published" look.
After you have finished improving the look of the worksheet, save the report in JSP format.
Register the report in OracleAS Portal through the Builder and Administer interfaces.
Customize the link to the data by providing automatic passing of identification and password to the database source in a manner hidden from the OracleAS Portal user.
If desired, add prebuilt OracleAS Portal styles to be consistent with other portlets.
Add the report definition file to the OracleAS Portal framework.

12.2.1.2 Using analytics and OLAP

As business analysts become more sophisticated, the questions they ask may evolve from "what happened " to "what trends are present and what might happen in the future." OLAP tools provide the ability to handle time-series and mathematical analysis for understanding past trends and forecasting the future. OLAP tools use two types of data:

ROLAP: Relational online analytical processing. These tools use data stored in relational databases in a star schema.
MOLAP: Multidimensional online analytical processing. These tools use predefined multidimensional cubes.

Popular MOLAP engines include Oracle's Express Server, Hyperion's Essbase, and Microsoft's Analytic Services. These MOLAP engines handle queries extremely quickly, and they traditionally work best when the data isn't updated frequently (because the cube-generation process takes time). Since Oracle9 i , Oracle has supported MOLAP cubes stored within the relational database via the OLAP Option; use of this option provides better scalability and more timely access to this type of data.

When building a relational query in OracleAS Discoverer, business analysts can select analytic functions provided in the Oracle database, use predefined functions provided by the administrator, or create their own calculations. Prebuilt templates are provided for the commonly used analytic calculations provided by the Oracle database. These calculations include:

Rank

Percent rank

Difference

Percent difference

Preceding value

Following value

Running total

Percent running contribution

Percent contribution

Moving total

Group total

Band by value

Band by rank

The Oracle database's OLAP Option object contains predefined facts, dimensions, and cube structures stored in an analytic workspace . These features greatly speed pivoting and provide other multidimensional functions. OracleAS Discoverer support for access to the analytic workspace is a new capability added in 2004.

12.2.1.3 Managing and improving query performance

OracleAS Discoverer uses a cubic cache to maintain query performance in a way that is transparent to both business analysts and administrators. In a typical three-tier web deployment, the cubic cache is in the middle tier. Because it is tied to the analyst's session, the cube allows subsequent analysis against the same data without requiring repeated queries.

OracleAS Discoverer also provides additional facilities that can be used to improve performance. For example, query performance can be greatly improved when the query is redirected to data that is preaggregated in the database at a summary level. OracleAS Discoverer can automatically redirect queries to these summary tables. You can use a wizard in OracleAS Discoverer to simplify creation by defining a summary policy. After setting aside a portion of the database for summaries (20 MB is typical), OracleAS Discoverer can then analyze available space and recommend, create, and maintain the summary tables.

A business analyst might want to figure out how long a query will take before actually running it. OracleAS Discoverer's query governor can predict the amount of time a query will take based on comparisons to previous query times of similar queries. This data is kept in the Oracle database server and is updated so that the predictions become more accurate over time. The administrator has the option of placing limits on the time allowed for queries to complete. This provides a safeguard because an analyst could (either intentionally or accidentally ) request incredibly complex and lengthy queries for a particular report. Long-running queries can negatively impact overall database performance for other business analysts.

12.2.1.4 Using Administrator Edition

Administrators of OracleAS Discoverer can use the Oracle Discoverer Administrator Edition, a part of the Oracle Developer Suite. Oracle Discoverer Administrator lets administrators perform such functions as formatting business areas, creating or editing business areas, creating summaries, setting privileges, and managing scheduled workbooks. Administrators can also set end-user privileges ”for example, the ability to use OracleAS Discoverer clients , create or edit queries, collect query statistics, perform item drills, grant workbooks, schedule workbooks, save workbooks to the database, and change passwords.

The Oracle Discoverer Administrator Edition also provides the ability to set up and maintain the End User Layer. Wizards guide the administrator through the process of building the EUL. The EUL can be populated from an Oracle data dictionary, via a gateway to another source or via the Oracle Warehouse Builder Common Warehouse Metamodel (CWM) bridge. As an alternative, the EUL can also be created and managed using Oracle Application Server 10 g 's EUL command line for Java.

Administrators also control access to workbooks and worksheets through the Discoverer Administrator Edition. As mentioned earlier, they can put limits on resources available to analysts monitored by the OracleAS Discoverer query governor.

If there is no client activity for a period of time, an administrator may want to have an OracleAS Discoverer "time out" because an OracleAS Discoverer session is a live connection into your corporate data source. For Discoverer Plus, a timeout value can be set in seconds ( Timeout= X ) in the pref.txt file. The minimum allowed is 180 seconds. For Discoverer Viewer, the Web.xml file contains a session-timeout tag that is set in minutes.

12.2.2 Oracle Application Server Business Intelligence Partners

The continued popularity of the Oracle database for business intelligence and data warehousing is driving a growing number of partnerships among tools vendors . Such partnerships sometimes extend to support of Oracle Application Server.

Business Objects (www.businessobjects.com) provides an ad hoc query and OLAP capability that can be deployed in Oracle Application Server in various ways. For example:

The Business Objects' Enterprise analysis tool can be used to generate queries against Oracle relational and OLAP Option data through SQL.
The Business Objects "Universe," or metadata layer, can be created to enable transparent drill-through from OLAP data to detailed data stored in relational tables.
An OracleAS Portal toolkit is also available for the viewing of Business Objects-generated reports.

12.2.3 Custom OLAP Applications

Instead of using standard tools to access OLAP, some organizations choose to deploy applications-oriented solutions. Often, these custom-built solutions are designed to make operations as easy as possible for less-skilled users ”although often at the cost of providing less flexibility.

Back when Oracle's Express Server was popular, custom OLAP applications were built via Oracle's Express Objects. For today's Oracle database OLAP Option, such applications are now built using Oracle JDeveloper and Business Intelligence beans. These JavaBeans provide prebuilt components for manipulating tables, crosstabs, and graphs, and for building queries and calculations similar to the functionality previously found in Oracle Express. Oracle JDeveloper generates Java code using these building blocks that maps to the Java OLAP API provided by Oracle's OLAP Option. The J2EE-compliant applications that are produced can be deployed to Oracle Application Server.

12.2.4 Data Warehouses and Oracle Warehouse Builder

The database schema used for business intelligence may be one of several types:

Third normal form (3NF): In such a model, data is stored once and appears much the same as in online transaction processing. Third normal form is typically used for operational reporting.
Star schema: In such a model, a large transaction fact table is surrounded by multiple lookup or "dimension" tables. This model is common in data warehousing. The star schema is especially popular when used with ad hoc query and analysis tools that explore historical data because the model typically matches the kinds of questions that business analysts want to ask (see Figure 12-3 for a typical example). This model often includes hierarchies with summary levels. These models are extremely useful when the business analyst wants to determine, for example, the number of sales (in a fact table) made by a sales channel (dimension) over a certain period of time (dimension).
OLAP schema: The analytic workspace in the Oracle OLAP Option takes the star schema model one step further by enabling the storage of multidimensional cubes (MOLAP) as objects within the same relational database. This approach further speeds query performance.

Figure 12-3. Star schema viewed through Oracle Warehouse Builder

If you want to perform business analysis on your data, you typically have to create a data warehouse with a star schema model. Oracle Warehouse Builder, included with the Oracle Developer Suite or the Oracle database, is Oracle's tool for designing these schema, importing the metadata describing data sources, and designing extraction, transformation, and loading source-to-target maps. Oracle Warehouse Builder can then automatically generate PL/SQL scripts for ETL from relational sources, and SQL*Loader files for loading from flat files. SQL*Loader's direct path load capability can provide rapid parallel loading and bypass the buffer cache and rollback mechanism by writing directly to the datafile.

Data models and mappings are stored in OWB's Metadata Repository, which is based on a version of the Common Warehouse Metamodel standard. OWB provides metadata reports, impact analysis, and data lineage diagrams through a web browser. Metadata interchange to a variety of repositories is available via third-party integration tools. Metadata of designs may be directly imported from tools such as Oracle Designer, CA's ERwin, and Sybase's PowerDesigner.

If you create an OLAP Option analytic workspace schema, you will typically use OWB and an Analytic Workspace Manager. Using OWB, you start by first creating a relational dimensional model, then running an OLAP bridge to create an OLAP catalog and populate the analytic workspace.

In any typical ETL building process, you do the following:

Import metadata that describes source tables, including Oracle (via database links) and other relational databases (through ODBC or gateways), flat files, or application-specific tables (such as those in SAP).
Design target tables.
Map source metadata to target metadata, including mapping functions. The set of mapping functions in OWB includes a joiner, filter, aggregator, reduplicator, sorter, splitter, sequencer, inline expressions, transformations, and name and address cleansing operator. Figure 12-4 provides an example of how a typical mapping looks.

Figure 12-4. Typical Oracle Warehouse Builder source table-to-target table mapping

OWB then validates the source-to-target mappings.
Once the validation has been performed, generate any of the following:
- DDL if target tables are to be created
- SQL*Loader control files for the loading of flat files
- PL/SQL scripts for ETL from relational sources

OWB can leverage ETL functionality in the Oracle database, such as support for external tables, table functions, merges (inserts or updates, depending on whether a data item exists), and multitable inserts .

Scripts are deployed to, and run at, the target data warehouse, typically scheduled using the Oracle Enterprise Manager job scheduler. Although batch jobs are more common, OWB can also create trickle feeds (data is fed nearly continuously and in near-real time) through the use of advanced queues. For more complex scheduling of ETL jobs where certain prerequisites must be met, OWB includes an interface to Oracle Workflow. (See the discussion of Oracle Workflow in Chapter 15)