Databases Reference
In-Depth Information
—the infrastructure becomes useful only when business users use the data to gain in‐
sight. This may seem like a trivial point, but we've seen numerous companies build
elegant infrastructure without consulting business users to determine business needs
or key performance indicators (KPIs) to be measured. Often, such deployed projects
end up supporting very few users, generate little activity, and little business intelligence
is gained.
Assuming that your infrastructure is well planned and there is a demand for the data,
your next challenge will be to figure out how to handle the demand. You will be faced
with the need to design your data warehouse and other infrastructure components to
deliver appropriate performance to your users—performance that may initially seem
far beyond your capabilities, since the information needed can involve comparisons of
massive amounts of detailed data.
When you start your design, also remember that the data warehouse and business in‐
telligence infrastructure will never be considered finished. When the business needs
change, so too must components in the infrastructure. Thus, the ability to track changes
through metadata stored in a repository often becomes critical as part of the design
work. Various tools from Oracle and other vendors can provide this capability.
As noted previously, data warehouses historically have had a different set of usage char‐
acteristics from those of an OLTP database. One aspect that makes it easier to meet data
warehousing performance requirements is the higher percentage of read operations.
Oracle's locking model, described in detail in
Chapter 8
, is ideally suited for data ware‐
house operations. Oracle doesn't place any locks onto data that's being read, thus re‐
ducing contention and resource requirements for situations where there are a lot of
database reads. Since locks don't escalate, Oracle is also frequently deployed where near
real-time data feeds into the warehouse occur in a scenario not unlike OLTP workloads.
Warehousing usage characteristics lead to deploying different types of schema. In OLTP
databases, transaction data is usually stored in multiple tables and data items are stored
only once in what is called 3NF or third normal form (described in
Chapter 4
). If a query
requests data from more than one transaction table, the tables are joined together. Typ‐
ically, the database query optimizer decides which table to use as the starting point for
the join, based on the assumption that the data in the tables is essentially equally im‐
portant.
When business users need an understandable schema to formulate their own ad hoc
queries or analytical processing is required, key transaction data can be more appro‐
priately stored in a central fact table, surrounded by dimension or lookup tables, as
shown in
Figure 10-2
. The fact table can contain summarized data for data items du‐
plicated elsewhere in the warehouse, and dimension tables can contain multiple hier‐
archies. As noted previously, when organizations consolidate their data marts into en‐
terprise data warehouses, many now deploy a variation called a hybrid schema, a mixture
of third normal form and star schema.
