Page 271 - DCAP603_DATAWARE_HOUSING_AND_DATAMINING

Unit 14: Quality Driven Data Warehouse Design

notes
Data warehouse database oLtp database
Designed for analysis of business measures by Designed for real-time business operations
categories and attributes
Optimized for bulk loads and large, complex, Optimized for a common set of transactions, usually
unpredictable queries that access many rows per adding or retrieving a single row at a time per table
table
Loaded with consistent, valid data; requires no real Optimized for validation of incoming data during
time validation transactions; uses validation data tables
Supports few concurrent users relative to OLTP Supports thousands of concurrent users

14.3.2 a Data Warehouse supports oLtp

A data warehouse supports an OLTP system by providing a place for the OLTP database to
offload data as it accumulates, and by providing services that would complicate and degrade
OLTP operations if they were performed in the OLTP database.
Without a data warehouse to hold historical information, data is archived to static media such as
magnetic tape, or allowed to accumulate in the OLTP database.

If data is simply archived for preservation, it is not available or organized for use by analysts
and decision makers. If data is allowed to accumulate in the OLTP so it can be used for analysis,
the OLTP database continues to grow in size and requires more indexes to service analytical
and report queries. These queries access and process large portions of the continually growing
historical data and add a substantial load to the database. The large indexes needed to support
these queries also tax the OLTP transactions with additional index maintenance. These queries
can also be complicated to develop due to the typically complex OLTP database schema.

A data warehouse offloads the historical data from the OLTP, allowing the OLTP to operate at
peak transaction efficiency. High volume analytical and reporting queries are handled by the
data warehouse and do not load the OLTP, which does not need additional indexes for their
support. As data is moved to the data warehouse, it is also reorganized and consolidated so that
analytical queries are simpler and more efficient.
14.3.3 oLap is a Data Warehouse tool

Online analytical processing (OLAP) is a technology designed to provide superior performance
for ad hoc business intelligence queries. OLAP is designed to operate efficiently with data
organized in accordance with the common dimensional model used in data warehouses.

A data warehouse provides a multidimensional view of data in an intuitive model designed
to match the types of queries posed by analysts and decision makers. OLAP organizes data
warehouse data into multidimensional cubes based on this dimensional model, and then
preprocesses these cubes to provide maximum performance for queries that summarize data
in various ways. For example, a query that requests the total sales income and quantity sold for
a range of products in a specific geographical region for a specific time period can typically be
answered in a few seconds or less regardless of how many hundreds of millions of rows of data
are stored in the data warehouse database.
OLAP is not designed to store large volumes of text or binary data, nor is it designed to support
high volume update transactions. The inherent stability and consistency of historical data in a
data warehouse enables OLAP to provide its remarkable performance in rapidly summarizing
information for analytical queries.

LoveLy professionaL university 265