Oracle Essentials Oracle Database 11g (48 page)

Oracle has enterprise portal offerings (Oracle Portal, and more recently WebCenter) available as part of the Oracle Application Server. These provide an integration point for custom-built business intelligence applications using Oracle Business Intelligence tools. For example, Answers can publish portlets to an enterprise portal via the JSR

specification. An enterprise portal can also provide access to a number of other applications and web sites through its interface, and it is highly customizable by users.

244

|

Chapter 10: Oracle Data Warehousing and Business Intelligence
OLAP and OLAP Applications Building

As business users become more sophisticated, their questions 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 initially grew around the early inability of relational databases to effectively handle multidimensional queries (described previously in the section
“Data Ware-

house Design”).
This led to OLAP tools packaged with their own data “cubes” where data is downloaded from relational sources into the cubes.

These separate database engines are called Multidimensional Online Analytical Processing engines, or
MOLAP engines
. Examples include Oracle’s Express Server and Oracle’s Hyperion Essbase, as well as the Microsoft Analysis Services. Such MOLAP

engines handle queries extremely quickly and work best when the data is not updated frequently (because the cube-generation process takes time). Oracle’s Essbase offering provides a MOLAP engine that can be used in conjunction with a variety of relational database engines.

OLAP functionality became more common in relational databases since star schema containing summary levels are supported to various degrees in many databases and because there is an increased need for very frequently updated data. When used in this fashion, the interaction is called
ROLAP
, which stands for Relational Online Analytical Processing. Tools that can work against either relational databases or MOLAP engines are sometimes referred to as
hybrid tools
. For ROLAP deployment, Oracle’s Business Intelligence tools and several other tools can leverage ANSI standard analytic functions built into the database as SQL extensions and can also access the OLAP Option, a MOLAP cube within the relational database, via SQL.

Oracle Database 11
g
significantly improved the flexibility of accessing the Oracle database OLAP Option. Although queries were accessible via SQL in the past, business users needed to specifically point their queries to OLAP Option cubes. When deployed in Oracle Database 11
g
, the OLAP cubes can be used transparently as an alternative to materialized views since Oracle’s SQL query rewrite recognizes the cubes.

The materialized view refresh can refresh OLAP cubes as of Oracle Database 11
g
.

OLAP Option cubes are deployed in what are called
analytic workspaces
. They can be created using Oracle Warehouse Builder or using a simplified logical dimensional modeling tool called the Analytic Workspace Manager (AWM). Both tools provide interfaces for creation of the cubes and for building maps from relational tables into the cubes.

Custom OLAP applications can be built using Oracle’s JDeveloper and
business
intelligence beans
, although this is much less common than using off-the-shelf tools.

Other Software for the Data Warehouse

|

245

The Java beans provide prebuilt components for manipulating tables, crosstabs, and graphs and for building queries and calculations similar to the functionality previously found in Express. JDeveloper generates Java code utilizing these building blocks that maps to the Java OLAP API provided by Oracle’s OLAP Option.

Data Mining

Data mining
, an often overused and misunderstood term in data warehousing, is the use of mathematical algorithms to model relationships in the data that wouldn’t be apparent by using other tools. Most companies shouldn’t approach data mining unless analysts have met the following criteria:

• An understanding of the quality and meaning of the data in the warehouse.

• Business insight gained using other tools and the warehouse.

• An understanding of a business issue being driven by too many variables to model outcomes in any other way.

In other words, data-mining tools are not a replacement for the analytical skills of data warehouse users.

The data-mining tools themselves can rely on a number of techniques to produce the relationships, such as:

• Extended statistical algorithms, provided by statistical tools vendors, that can highlight statistical variations in the data.

• Clustering techniques that show how business outcomes can fall into certain groups, such as insurance claims versus time for various age brackets. In this example, once a low-risk group is found or classified, further research into influencing factors or “associations” might take place.

• Logic models (if A occurs, then B or C are possible outcomes) validated against small sample sets and then applied to larger data models for prediction, commonly known as
decision trees
.

• Neural networks “trained” against small sets, with known results to be applied later against a much larger set.

• Anomaly detection used to detect outliers and rare events.

• Visualization techniques used to graphically plot variables and understand which variables are key to a particular outcome.

Data mining is often used to solve difficult business problems such as fraud detection and churn in micro-opportunity marketing, as well as in other areas where many variables can influence an outcome. Companies servicing credit cards use data mining to track unusual usage—for example, the unexpected charging to a credit card of expensive jewelry in a city not normally traveled to by the cardholder. Discovering clusters of unusual buying patterns within certain small groups might also drive
246

|

Chapter 10: Oracle Data Warehousing and Business Intelligence
micro-opportunity marketing campaigns aimed at small audiences with a high probability of purchasing products or services.

A recent trend among relational database providers is tighter integration of data-mining algorithms into the relational database. Oracle’s data-mining strategy initially included a client/server product called Oracle Darwin to provide algorithms for modeling associations, neural networks, classification and regression trees, and clusters against Oracle tables or flat files. Oracle began to embed algorithms packaged as the Data Mining Option into the Oracle9
i
database. Algorithms now in the Data Mining Option include Naïve Bayes, Associations, Adaptive Bayes Networks, Clustering, Support Vector Machines (SVM), Nonnegative Matrix Factorization (NMF), Decision Trees, and Generalized Linear Models (as of Oracle Database 11
g
, supporting Binary Logistic Regression and Multivariate Linear Regression). The algorithms are accessible via Java and PL/SQL APIs. Other data mining capabilities available include text mining (providing document clustering and classification) and BLAST

similarity searches leveraging the SVM algorithms (common in genetic research).

Data mining applications can be custom-built using Oracle’s Data Miner tool. Data Miner is used to develop, test, and score the models. Generally, the data must first be prepared for mining by binning, normalizing, and adjusting for missing values. The Data Mining Option in Oracle Database 11
g
added the capability to automate this data preparation process in the database. Data Miner provides the ability to define metadata, tune the generated Java code, view generated XML files, and test application components. Data-mining analysts can also use tools such as InforSense or SPSS

Clementine to build models that leverage the Oracle Data Mining Option algorithms and manage the development process.

Business Intelligence Applications

Business intelligence applications are prebuilt solutions providing extended reporting and “dashboard-like” interfaces to display business trends. These applications directly access OLTP schema (Oracle’s Daily Business Intelligence) or more commonly access solutions with infrastructure similar to traditional data warehouses.

Examples of product suites that take the latter approach include Oracle Business Intelligence Applications, PeopleSoft EPM, and SAP’s Business Warehouse, all often deployed on Oracle databases.

The business intelligence applications often focus on specific areas of the business, such as marketing or financial analysis. For example, Oracle’s Hyperion Financial Performance Management applications address financial planning and budgeting. Such applications include predefined queries, reports, and charts that deliver the kind of information required for a particular type of business analysis while sparing the business user the complexity of creating these objects from scratch. The data warehousing type of solutions also include prebuilt ETL from supported data sources.

Other Software for the Data Warehouse

|

247

Oracle E-Business Suite’s Daily Business Intelligence (DBI) provides access into Oracle transactions tables and materialized views. Access is through prebuilt Oracle Business Intelligence workbooks containing prepopulated business metadata. The most recent Oracle toolset supported is OBI EE. Oracle DBI modules include Compliance, Customer Support, Financials, Human Resources, Procurement, Product Lifecycle, Projects, Marketing, Maintenance, Sales, Supply Chain, and others.

Oracle Business Intelligence Applications include more than 2500 KPIs in OBI EE

and prebuilt mappings for ETL from Siebel, Oracle E-Business Suite, PeopleSoft, SAP, and other applications. Formerly known as the Siebel Analytics applications, the applications cover the areas of Sales, Service and Contact Center, Marketing, Financial, Supply Chain, and Workforce. Oracle Business Intelligence Applications have been designated as Oracle’s flagship business intelligence horizontal applications offering. As such, Oracle is continuing to extend the KPIs provided, the ETL

mappings, and the business areas covered.

The PeopleSoft EPM offering includes more than 1,200 metrics with prebuilt mappings from PeopleSoft and JD Edwards applications. EPM packaged warehouses include Human Capital Management, Financials, Campus Solutions, Supply Chain, and Customer Relationship Management. These applications also support OBI EE as a frontend tool.

The promise of such prebuilt solutions is that they provide easier-to-deploy solutions with more out-of-the-box functionality. While some customization will probably always be needed, the time required to deploy an initial and useful solution can be substantially reduced.

The Metadata Challenge

On the one hand,
metadata—
or descriptive data about data—is incredibly important. Virtually all types of interactions with a database require the use of metadata, from datatypes of the data to business meaning and history of data fields.

On the other hand, metadata is useful only if the tools and clients who wish to use it can leverage it. One of the great challenges is to create a set of common metadata definitions that allows tools and databases from different vendors to interact.

There have been a number of attempts to reach an agreement on common metadata definitions. In 2000, a standard was ratified that defines a common interface for interchange of metadata implementations. Named the Common Warehouse Metadata Interchange (CWMI) by the Object Management Group (OMG), this standard is based on XML interchange. Oracle was one of the early proponents and developers of the technology in this standard. For example, Oracle has a CWM bridge for exchanging metadata stored in the Oracle Warehouse Builder repository. OWB also includes a metadata viewer for more detailed metadata reports, and a viewer for data lineage and impact analysis diagrams.

248

|

Chapter 10: Oracle Data Warehousing and Business Intelligence
As noted earlier in this chapter, an emerging complementary solution—one in which ETL into a single data warehouse is not the entire solution—is the leveraging of master data management and data hub solutions. Today, most organizations are still a long way from consolidated metadata, and when they have tried to do this as an IT

best-practice project, they generally have not been successful. Such projects are usually adopted only when delivered within a business intelligence project that delivers business value.

Best Practices

Those experienced in business intelligence generally agree that the following are typical reasons why these projects fail:

Failure to involve business users, IT representatives, sponsoring executives, and anyone
else with a vested interest throughout the project process
Not only do all of these groups provide valuable input for creating a business intelligence solution, but lack of support by any of them can cause a project to fail.

Overlooking the key reasons for the business intelligence infrastructure
During the planning stages, IT architects can lose sight of the forces driving the creation of the solution.

Overlooked details and incorrect assumptions

A less-than-rigorous examination of the environment can doom the project to failure.

Unrealistic time frames and scope

As with all projects, starting the creation of a business intelligence solution with too short a time frame and too aggressive a scope will force the team to cut cor-ners, resulting in the mistakes previously mentioned.

Failure to manage expectations

Data warehouses and business intelligence solutions, like all technologies, are not a panacea. You must make sure that all members of the team, as well as the eventual users of the solution, have an appropriate set of expectations.