Moss L.T., Atre S. Business intelligence roadmap: The complete project lifecycle for decision-support applications
Подождите немного. Документ загружается.
[ Team LiB ]
Chapter Eight. Step 8: Database
Design
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
251/631
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
252/631
Chapter Overview
This chapter covers the following topics:
Things to consider about database design
The differences in database design philosophies and in best design practices for
operational databases and for BI target databases
The multidimensional design premise of aggregation and summarization
Basic explanations of a star schema and a snowflake schema
Aspects of physical database design, including implementation options (such as
free space and buffer space), physical dataset placement, partitioning,
clustering, indexing, reorganizations, backup and recovery, and parallel query
execution
Brief descriptions of the activities involved in database design, the deliverables
resulting from those activities, and the roles involved
The risks of not performing Step 8
Things to Consider
Reports and Queries
What common reporting patterns exist across departments?
What level of detailed data will the business people require for drill-
down queries?
How much ad hoc querying against detail data do we project will
occur?
How many reporting dimensions should we consider? What are
they?
How many new dimensions may have to be added in the future?
Design Considerations
Should we store aggregated and summarized data?
How much concurrent usage of the data should we expect?
How big will the BI target databases be? What are the projected
data volumes and growth factors?
How much historical data will we keep?
What will be the frequency of loads?
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
253/631
Will the databases need to be distributed?
What are the availability requirements?
Performance Considerations
What are the performance requirements?
How will we cluster the tables? By the date column or by other
columns?
What tables should be co-located?
How will we partition the tables? Will we partition by date?
What types of indexing algorithms should we use (B-tree, hash,
inverted file, sparse, binary)?
Can we run multiple operations (queries, loads) in parallel?
Selection of Database Management System
Which database management system (DBMS) are we using for our
existing applications? Will we use the same DBMS for the BI target
databases?
Will our current DBMS scale to the expected size?
Are we satisfied with the current DBMS? If not, what are we doing
about it?
Will we need to license (buy) another DBMS?
Staffing
What skills do we have available to design the BI target databases?
Do we have enough database administrators?
Can one database administrator be dedicated to this project full-
time?
Does he or she have multidimensional design skills? If not, how
soon can he or she receive training?
Will we need to hire a consultant to mentor the database
administrator and the team?
BI decision-support requirements for aggregated and summarized data have
introduced a new type of database design and a new way of storing data. This new
multidimensional database design schema, coupled with new BI technology, supports
the ability to "slice and dice" information in myriad ways for reporting and analysis
purposes. In order to implement the capabilities of slicing and dicing, database
administrators and developers must learn new design techniques and must acquire a
new way of working with the databases. They must begin by understanding the ways
in which the data will be accessed. Data can be accessed either in a conventional
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
254/631
way (usually detailed records retrieved with Structured Query Language [SQL]
queries) or in a multidimensional way (usually summarized records retrieved with an
online analytical processing [OLAP] tool). Multidimensional data storage and data
access techniques, which support slicing and dicing, allow information to be viewed
from a variety of perspectives, such as Products by Factory by Market Segment and
Market Segments by Product by Factory.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
255/631
[ Team LiB ]
Differences in Database Design Philosophies
There is a completely different design philosophy behind BI target databases as
compared with operational databases. Table 8.1 summarizes the differences between
these two types of databases.
Operational Databases
The intent of operational database design is to prevent the storage of the same data
attributes in multiple places and thus to avoid the update anomalies caused by
redundancy. In other words, from an operational perspective you want to avoid
storing the same data in multiple columns in multiple tables so they do not get out of
synch. Designing normalized database structures is key for developing relational
databases in support of that intent. Normalization ensures that the data is created,
stored, and modified in a consistent, nonredundant way.
Table 8.1. Operational Databases versus BI Target Databases
Operational Databases
BI Target Databases
Geared toward eliminating
redundancy, coordinating
updates, and repeating the
same types of operations many
times a day, every day (for
example, airline reservations,
deposits and withdrawals from
bank accounts, hotel room
reservations).
Geared toward supporting a wide range
of queries and reports. Queries and
reports may vary from one business
analyst to another or from one
department to another. All of the queries
and reports may not run on the same
day and may not run every day (for
example, quarterly trend analysis
reports on regional sales, monthly order
fulfillment report).
Most of the transactional
systems require subsecond
response time.
Although response time is important,
subseconds cannot be expected. Typical
response times are seconds, minutes, or
hours.
Highly normalized to support
consistent updates and
maintenance of referential
integrity.
Highly denormalized to provide quick
retrieval of a wide range and a large
amount of data. Data that belongs
together from an analytical reporting
perspective is usually stored together.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
256/631
Store very little derived data.
Data is usually derived
dynamically when needed.
Store large amounts of derived data.
This saves time for the queries and
reports.
Do not store historical data.
Historical records are archived.
Store large amounts of historical data,
often at some level of summarization,
but just as often at a detailed level.
Lightly summarized, mostly for
reporting purposes.
Many levels of precalculated,
summarized data, from lightly
summarized to highly summarized.
Most operational systems are designed with a data-in philosophy (data entry), not a
data-out philosophy (reporting and querying). The objective of a data-in philosophy
is to make data entry as efficient as possible, running hundreds of thousands of
transactions per day, while eliminating or minimizing redundancies in the data. Data
redundancy leads to inconsistencies, and inconsistencies are often the reason for
poor-quality data. Therefore, in trying to solve the enormous data quality and data
redundancy problems in operational systems, the goal is to avoid redundancy
(except for key redundancy, which is unavoidable). This goal is achieved through
normalization.
While normalization works well for operational systems, the requirements for
reporting are different from the requirements for data entry. Reporting uses data
that has already been created, which means update anomalies cannot occur. While it
is of great benefit that the data is consistent and nonredundant as a result of a
normalized database design, that same design makes reporting difficult. For
example, to create strategic trend analysis reports, many tables have to be accessed,
and every row in those tables has to be read. This is not only complex but also
extremely inefficient when run against a normalized database design because it
requires scanning tables and performing large multi-table JOINs. For that reason,
most BI target databases are based on a multidimensional design, in which the data
for the strategic trend analysis reports is stored in a precalculated and
presummarized way.
Figure 8.1 illustrates the general difference between an operational normalized
design and a BI multidimensional design.
Figure 8.1. Operational normalized versus BI Multidimensional
Designs
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
257/631
In this example, the operational database design shows an Order database where
customers are associated with orders, and each order is composed of many line
items. With each placed order, the line items have to be subtracted from a separate
Inventory database. The BI target database design shows a database with
summaries that are used to identify trends over time. In this design, the same data
about orders, line items, and inventory may exist in multiple tables (Monthly
Summary, Regional Summary, Product Summary), albeit summarized by different
dimensions. While operational databases generally store granular (atomic) data, BI
target databases, for the most part, store summarized data.
BI Target Databases
Contrary to the data-in philosophy (data entry) of operational systems, the data-out
philosophy (reporting and querying) of BI applications includes the following design
considerations.
BI target databases are designed for simplified, high-performance data
retrieval, not for efficiency of data storage and maintenance (which are
important design considerations for operational databases).
Eliminating or minimizing data redundancy is not a goal in designing BI target
databases. If a choice must be made, data redundancy is favored over
complexity, but the redundancy must be controlled. Redundant data must be
consistent and reconcilable.
Basic assumptions for designing BI target databases are listed below.
- Data is stored in such a manner that it is readily accessible in ways that
are of interest to the business people.
- The design is driven by access and usage.
- A normalized design is not necessarily intuitive for a business person
and could therefore become quite complex.
- No BI data can be invented! All data in the BI target databases must
exist in or be derivable from current internal or external operational data
sources.
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
258/631
A key decision for all BI applications is whether or not, and at what level, to store
summarized data in the BI target databases. The database administrator and the
lead developer may decide to store both detailed data and summarized data, either
together in the same BI target database or in different BI target databases. This
database design decision must be based on access and usage requirements.
[ Team LiB ]
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
259/631
[ Team LiB ]
Logical Database Design
Because of the differences in intent and purpose between operational systems and BI
applications, different database design techniques have been devised for BI target
databases. These highly denormalized designs store aggregated and summarized
data in a multidimensional fashion. Logical database designs are documented as
physical data models with technical meta data.
Aggregation and summarization are probably the most significant contributors to
good BI application performance. If most business analysts need to see their data
summarized, these totals should be precalculated and stored for quick retrieval. It is
important to discuss the level of granularity with the business representative, as well
as with other business analysts who will be using the BI target databases, since they
will expect the database design to allow them to drill down to a certain level of
detail.
Multidimensional database designs support the quick retrieval of a wide range of
data. Two popular multidimensional design techniques are the star schema and the
snowflake schema, both described below.
The Star Schema
In a star schema, data is represented as an array of precalculated values, called
facts, around which analysis is performed. These precalculated facts represent
atomic operational data values that have been presummarized by certain
dimensions, such as customer, product, and time. A dimension in a star schema is
similar to an entity in a logical data model: it is a business object about which data is
collected for business purposes.
The star schema mirrors the view of a business query. As the name implies, the star
schema has a single object in the middle, called the fact table, which is connected in
a radial fashion to a number of objects, called dimension tables. Figure 8.2 presents
an example of a star schema.
Figure 8.2. Star Schema
北斗成功社区 BeiDouWeb.com 教育音视频/电子书/实用资料文档/励志音乐影视 仅供免费试用/版权原著所有
260/631