22
Part I: Laying the Foundations
■ (^) Reference data stores are primarily read-only and store generic data required by the organi-
zation but which seldom changes — similar to the reference section of the library. Examples
of reference data might be unit of measure conversion factors or ISO country codes. A
reference data store is tuned for high-performance data retrieval.
■ (^) Data warehouses collect large amounts of data from multiple data stores across the entire
enterprise using an extract-transform-load (ETL) process to convert the data from the vari-
ous formats and schema into a common format, designed for ease of data retrieval. Data
warehouses also serve as the archival location, storing historical data and releasing some
of the data load from the operational data stores. The data is also pre-aggregated, mak-
ing research and reporting easier, thereby improving the accessibility of information and
reducing errors.
■ (^) Because the primary task of a data warehouse is data retrieval and analysis, the data-integ-
rity concerns presented with an operational data store don’t apply. Data warehouses are
designed for fast retrieval and are not normalized like master data stores. They are generally
designed using a basic star schema or snowfl ake design. Locks generally aren’t an issue,
and the indexing is applied without adversely affecting inserts or updates.
■ (^) The analysis process usually involves more than just SQL queries and uses data cubes that con-
solidate gigabytes of data into dynamic pivot tables. Business intelligence (BI) is the combination
of the ETL process, the data warehouse data store, and the acts to create and browse cubes.
■ (^) A common data warehouse is essential to ensure that the entire organization researches
the same data set and achieves the same result for the same query — a critical aspect of
the Sarbanes-Oxley Act and other regulatory requirements.
■ (^) Data marts are subsets of the data warehouse with pre-aggregated data organized specifi -
cally to serve the needs of one organizational group or one data domain.
■ (^) Master data store, or master data management (MDM), refers to the data warehouse that
combines the data from throughout the organization. The primary purpose of the master
data store is to provide a single version of the truth for organizations with a complex set of
data stores and multiple data warehouses.
■ (^) Data Quality Services (DQS) refers to the SQL Server instance feature that consists of three
SQL Server catalogs with data-quality functionality and storage. The purpose of this feature
is to enable you to build a knowledge base to support data quality tasks.
Chapter 51, “Business Intelligence Database Design,” discusses star schemas and snowfl ake designs
used in data warehousing.
Smart Database Design
More than a few databases do not adhere to the principles of information architecture, and
as a result, fail to meet organization’s needs. In nearly every case, the root cause of the
failure was the database design. It was too complex, too clumsy, or just plain inadequate.
The side effects of a poor database design include poorly written code because developerscontinuedc02.indd 22c02.indd 22 7/30/2012 4:07:52 PM7/30/2012 4:07:52 PM
http://www.it-ebooks.info