Oracle Essentials Oracle Database 11g (43 page)

Streams. Replication is described in more detail in Chapter 13.

Real Application Clusters

Real Application Clusters can increase the scalability of the Oracle database over multiple nodes in a cluster. But by supporting multiple instances with full access to the same database, RAC also provides the highest levels of availability for protection from the failure of a node in a clustered environment. If one node fails, the surviving nodes provide continued access to the database. Grid computing deployment further extends availability capabilities.

Oracle Database 11
g
provides a number of high availability enhancements, including the ability to easily capture diagnostic information about database failures. For a

more detailed discussion of high availability, see Chapter 11.

Oracle Streams and Advanced Queuing

Messaging technology has existed for quite some time and is common in OLTP

applications. Typical messaging technologies provide a reliable transport layer for shipping messages from one machine to another over a network. Oracle8 introduced Advanced Queuing (AQ) as an integrated database service. Oracle9
i
Release 2 combined AQ with log-based replication in the creation of Oracle Streams.

Oracle Streams AQ provides the benefits of simple messaging products but adds the value of database-resident queues. The information in message queues represents critical business events and should be stored in a reliable, scalable, secure, and recoverable place. Placing the queues in the database extends the core benefits of a database to the queues themselves.

The data that flows through queues represents the ebb and flow of business activity.

Analyzing the types and volumes of message traffic can help to identify how different business functions are operating and interacting and this, in turn, can provide valuable insights into the operation of your business. AQ supports the notion of
218

|

Chapter 9: Oracle and Transaction Processing

message warehousing
, in which the content and details of the queues can be queried and analyzed because they’re already in the database. Oracle can dequeue messages but can leave historical data in the queues for subsequent analysis.

Applications can enqueue and dequeue messages as part of a transaction or as a separate event that occurs as soon as the specific enqueue or dequeue statement is issued. Queue actions included in the scope of a transaction are committed or rolled back with that transaction. Should a failure occur, the queue activities are recovered along with the rest of the database activity.

Oracle can propagate messages from one queue to another by providing a routing

engine for message traffic. Figure 9-7 illustrates the use of queuing and propagation.

Oracle

M

A

Application

C

te

Advanced

H

Propaga

Queues

I

Application

N

Oracle

E

B

Advanced

O

Queues

U

N

Application

D

A

Oracle

R

Propaga

Y

te

Application

Advanced

Queues

Figure 9-7. Oracle Streams/Advanced Queuing

Oracle Database 10
g
and more recent versions make it easier to implement Streams programmatically, by allowing you to enqueue and dequeue batches of messages and by reducing the amount of coding required to interact with queues.

Streams for System Interfaces

Implementing OLTP systems invariably involves interfaces with other systems in the enterprise or in other companies. The effort to design, create, and manage these interfaces is substantial and can easily account for 40 percent to 60 percent of the cost of large-scale ERP implementations. Furthermore, adding other systems to the mix or changing existing systems entails reworking the interfaces, resulting in an increasing and ongoing burden.

Oracle Streams can help companies solve the integration problem when implementing a “hub-and-spoke” architecture using a combination of messaging, routing, and transformation technologies. Traditionally, youwould develop a specific interface between two systems. As youadded a third system to the mix, youwould have to
Oracle Streams and Advanced Queuing

|

219

create more specific interfaces between each of the systems. The more systems you attempt to integrate, the more custom interfaces you would be responsible for developing and the greater the development and maintenance burden would be.

Using these components, individual systems can connect to a hub via the spokes, thus avoiding direct system-to-system interfaces. The spokes send and receive messages, while the hub provides routing and transformation services. This reduces the number of interfaces required to connect a set of systems. You don’t need a specific interface for every specific system pair. Adding systems to existing systems doesn’t require development of many new interfaces. You connect the new system to the hub and leverage the routing and transformation services.
Figure 9-8
contrasts the custom approach with the hub-and-spoke approach.

N-Touchpoints

Hub-and-Spoke

System

System

System

System

HUB

System

System

System

System

• Each interface is specific

• Queuing provides transport analog spokes

• Expensive to develop and maintain

• Hub provides message transformation

• Adding systems is increasingly difficult

• Adding systems is simpler

Figure 9-8. Custom interfaces versus hub-and-spoke approach
Oracle and Publish-Subscribe Technology

Oracle8
i
enhanced Advanced Queuing to include publish-subscribe functionality.

Applications can subscribe to a message queue by specifying the attributes of messages they’re interested in receiving. When another application publishes a message by placing it in a queue, Oracle evaluates the contents of the message to determine which of the subscribing applications are interested and notifies those applications.

For example, a shipping application can subscribe to a queue used for orders and specify that only messages for orders with a status of “Ready to Ship” are of interest.

As messages representing these orders flow through the queue, the shipping application will receive only the desired messages. This publish-subscribe functionality, coupled with message propagation for routing, provides a very powerful messaging backbone for information flow between systems.

220

|

Chapter 9: Oracle and Transaction Processing

Object Technologies and Distributed Components

In theory, the greater the amount of information, the more intelligence that can be extracted from it. Integrating information from separate systems can be an enormous task, especially because the complexity of integration increases geometrically as more systems are added to the mix.

While messaging technologies can assist with interfacing different systems, online interaction is often needed as well. For example, if the Human Resources system maintains information about the company’s employees (such as the department in which they work and their role), ideally the Purchasing system could access the data in the HR system online at the time purchases are being made. At this point, the Purchasing system could determine the spending limits of the purchaser and to what department the accounting should be tied. In practice, these online interfaces are difficult to build because they require the systems to agree, and remain in agreement, about how to communicate. Each system has proprietary application programming interfaces (APIs) that allow other systems to communicate with them. These specific, and often conflicting, APIs limit the reuse of the functionality within each system.

Object technologies offer one solution: systems communicate by invoking methods on objects instead of by calling specific APIs. For example, if youwant to check the department of a user, you make a standard object call to the employee object managed by the HR system.

Oracle8
i
and later versions support a number of object technologies, including Java for use as an object-oriented programming language. Oracle’s object-oriented support is described in more detail in
Chapter 14.
More recently, the focus on developing applications and code for reuse now includes the concept of a Service-

Oriented Architecture (SOA) and web services, described in Chapter 15.

Object Technologies and Distributed Components

|

221

Chapter 10

CHAPTER 10

Oracle Data Warehousing and Business

Intelligence

10

Although a database is general-purpose software, it provides a solution for a variety of technical requirements, including:

Recording and storing data

Reliably storing data and protecting each user’s data from the effects of other users’ changes

Reading data for online viewing and reports

Providing a consistent view of the data

Analyzing data

Summarizing data, detecting trends and data relationships, and forecasting The last two solutions can be deployed as a
data warehouse
, part of an infrastructure that provides
business intelligence
used in strategic and tactical management of the corporation or organization. Such solutions expose valuable business information embedded in an organization’s data stores.

Data warehousing and business intelligence solutions are now widely deployed and new projects continue to be extremely popular. There is a very simple reason behind this trend: such projects are seen as core to the business and provide a return on investment that can be grasped by the business community.

The trend is not new. Oracle began adding data warehousing features to Oracle7 in the early 1990s. Additional features for warehousing and business intelligence appeared in subsequent releases, enabling better performance, functionality, scalability, and management. Oracle also offers tools for building and using a business intelligence infrastructure, including data movement and business analyses tools and applications.

A business intelligence infrastructure can enable business analysts to determine:

• How a business scenario compares to past business results

• New solutions by looking at the data differently

222

• What could happen in the future

• How business actions could be changed to impact the future This chapter introduces the basic concepts, technologies, and tools used in data warehousing and business intelligence. To help you understand how Oracle addresses infrastructure and analyses issues, we’ll first describe some of the basic terms and technologies.

Business Intelligence Basics

Why build a data warehouse or business intelligence solution? Why is the data in an online transaction processing (OLTP) database part of only a business intelligence solution? Data warehouses are often designed with the following in mind:
Strategic and tactical analyses can discern trends in data
Data warehouses often are used in creation of simple reports based on aggregate values culled from enormous amounts of data. If OLTP databases were used to create such aggregates on the fly, the database resources used would impact the ability to process transactions in a timely manner. These ad hoc queries often leverage compute-intensive analytic functions embedded in the database.

A significant portion of the data in a data warehouse is often read-only, with infrequent updates

Leveraging database manageability features can make it possible to deploy warehouses holding hundreds of terabytes of data, even where near real-time updates of some of the data is occurring.

The data in OLTP systems is not “clean” or consistent across systems
Data input to OLTP systems, if not carefully controlled, is likely to contain errors and duplication. Often, a key portion of the data warehouse loading process involves elimination of these errors. In addition, since multiple OLTP

systems might differ in common data definitions, the loading process can be used to consolidate this data into a single definition.

The design required for an efficient data warehouse differs from the standard normalized design for a relational database

Queries are typically submitted against a fact table, which may contain summarized data. The schema design often used, a
star schema
, lets youaccess facts quite flexibly along key dimensions or “lookup” values. (The star schema is described in more detail later in this chapter.) For instance, a data warehouse user may want to compare the total amount of sales, which comes from a fact table, by region, store in the region, and items, all of which can be considered key dimensions. Today’s data warehouses often feature a
hybrid schema
that is a combination of the star schema common in previous-generation data marts with third normal form schema for detailed data that is common in OLTP systems and enterprise data warehouses.

Business Intelligence Basics

|

223

The Evolution of Business Intelligence

Gathering business intelligence is not a new idea. The use of corporate data for strategic decision-making beyond simple tracking and day-to-day operations has been going on for almost as long as computing itself.

Quite early, builders and users of operational systems recognized potential business benefits of analyzing the data in complementary systems. In fact, much of the early growth in personal computers was tied to the use of spreadsheets that performed analyses using data downloaded from the operational systems. Business executives began to direct IT efforts toward building solutions to better understand the business using such data leading to new business strategies. Today, solutions are commonly provided in business areas such as customer relationship management, sales and marketing campaign analysis, product management and packaging, financial analysis, supply chain analysis, and risk and fraud analysis.