لطفا منتظر باشید ...
12.2 Query and Analysis
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
databasein 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.
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
Oracle9i, 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:RankPercent rankDifferencePercent differencePreceding valueFollowing valueRunning totalPercent running contributionPercent contributionMoving totalGroup totalBand by valueBand 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 privilegesfor
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
10g'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.
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 (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 usersalthough 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
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 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)
