Chapter 13
Download
1 / 33

Chapter 13 - PowerPoint PPT Presentation


  • 226 Views
  • Uploaded on

Chapter 13. The Data Warehouse Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel. In this chapter, you will learn:. How operational data and decision support differ What a data warehouse is and how its data are prepared

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Chapter 13' - niabi


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Chapter 13 l.jpg

Chapter 13

The Data Warehouse

Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel


In this chapter you will learn l.jpg
In this chapter, you will learn:

  • How operational data and decision support differ

  • What a data warehouse is and how its data are prepared

  • What star schemas are and how they are constructed

  • What steps are required to implement a data warehouse successfully

  • What data mining is and what role it plays in decision support

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


The need for data analysis l.jpg
The Need for Data Analysis

  • External and internal forces require tactical and strategic decisions

  • Search for competitive advantage

  • Business environments are dynamic

  • Decision-making cycle time is reduced

  • Different managers require different decision support systems (DSS)

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Decision support systems l.jpg
Decision Support Systems

  • Decision Support

    • Is a methodology

    • Extracts information from data

    • Uses information as basis for decision making

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Decision support systems5 l.jpg
Decision Support Systems

  • Decision support system (DSS)

    • Arrangement of computerized tools

    • Used to assist managerial decision

    • Extensive data “massaging” to produce information

    • Used at all levels in organization

    • Tailored to focus on specific areas and needs

    • Interactive

    • Provides ad hoc query tools

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Dss components l.jpg
DSS Components

Figure 13.1

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Operational vs decision support data l.jpg
Operational vs. Decision Support Data

  • Operational data

    • Relational, normalized database

    • Optimized to support transactions

    • Real time updates

  • DSS

    • Snapshot of operational data

    • Summarized

    • Large amounts of data

  • Data analyst viewpoint

    • Timespan

    • Granularity

    • Dimensionality

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


The dss database requirements l.jpg
The DSS Database Requirements

  • Database schema

    • Support complex (non-normalized) data

    • Extract multidimensional time slices

  • Data extraction and filtering

  • End-user analytical interface

  • Database size

    • Very large databases (VLDBs)

    • Contains redundant and duplicated data

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Data warehouse l.jpg
Data Warehouse

  • Integrated

    • Centralized

    • Holds data retrieved from entire organization

  • Subject-Oriented

    • Optimized to give answers to diverse questions

    • Used by all functional areas

  • Time Variant

    • Flow of data through time

    • Projected data

  • Non-Volatile

    • Data never removed

    • Always growing

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Creating a data warehouse l.jpg
Creating a Data Warehouse

Figure 13.3

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Data marts l.jpg
Data Marts

  • Single-subject data warehouse subset

  • Decision support to small group

  • Can be test for exploring potential benefits of Data warehouses

  • Address local or departmental problems

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Dss architectural styles l.jpg
DSS Architectural Styles

  • Traditional mainframe-based OLTP

  • Managerial information system (MIS) with 3GL

  • First-generation departmental DSS

  • First-generation enterprise data warehouse using RDMS

  • Second-generation data warehouse using MDBMS

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Twelve data warehouse rules l.jpg
Twelve Data Warehouse Rules

1. Separated from operational environment

2. Data are integrated

3. Contains historical data over long time horizon

4. Snapshot data captured at given time

5. Subject-oriented data

6. Mainly read-only data with periodic batch updates from operational source, no online updates

7. Development life cycle differs from classical one, data driven not process driven

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Twelve data warehouse rules con t l.jpg
Twelve Data Warehouse Rules (Con’t.)

8. Contains different levels of data detail

  • Current and old detail

  • Lightly and highly summarized

    9. Characterized by read-only transactions to large data sets

    10. Environment has system to trace data resources, transformation, and storage

    11. Metadata critical components

  • Identify and define data elements

  • Provide the source, transformation, integration, storage, usage, relationships, and history of data elements

    12. Contains charge-back mechanism for usage

  • Enforces optimal use of data

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Online analytical processing olap l.jpg
Online Analytical Processing (OLAP)

  • Advanced data analysis environment

  • Supports decision making, business modeling, and operations research activities

  • Characteristics of OLAP

    • Use multidimensional data analysis techniques

    • Provide advanced database support

    • Provide easy-to-use end-user interfaces

    • Support client/server architecture

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Olap client server architecture l.jpg
OLAP Client/Server Architecture

Figure 13.6

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Olap server arrangement l.jpg
OLAP Server Arrangement

Figure 13.7

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Olap server with multidimensional data store arrangement l.jpg
OLAP Server with Multidimensional Data Store Arrangement

Figure 13.8

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Olap server with local mini data marts l.jpg
OLAP Server with Local Mini-Data-Marts

Figure 13.9

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Relational olap rolap l.jpg
Relational OLAP (ROLAP)

  • OLAP functionality

  • Uses relational DB query tools

  • Extensions to RDBMS

    • Multidimensional data schema support

    • Data access language and query performance optimized for multidimensional data

    • Support for very large databases (VLDBs)

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Typical rolap client server architecture l.jpg
Typical ROLAP Client/Server Architecture

Figure 13.10

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Multidimensional olap molap l.jpg
Multidimensional OLAP (MOLAP)

  • OLAP functionality to multidimensional databases (MDBMS)

  • Stored data in multidimensional data cube

  • N-dimensional cubes called hypercubes

  • Cube cache memory speeds processing

  • Affected by how the database system handles density of data cube called sparsity

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Molap client server architecture l.jpg
MOLAP Client/Server Architecture

Figure 13.11

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Star schema l.jpg
Star Schema

  • Data-modeling technique

  • Maps multidimensional decision support into relational database

  • Yield model for multidimensional data analysis while preserving relational structure of operational DB

  • Four Components:

    • Facts

    • Dimensions

    • Attributes

    • Attribute hierarchies

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Simple star schema l.jpg
Simple Star Schema

Figure 13.12

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Slice and dice view of sales l.jpg
Slice and Dice View of Sales

Figure 13.14

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Star schema representation l.jpg
Star Schema Representation

  • Facts and dimensions represented by physical tables in data warehouse DB

  • Fact table related to each dimension table (M:1)

  • Fact and dimension tables related by foreign keys

  • Subject to the primary/foreign key constraints

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Star schema for sales l.jpg
Star Schema for Sales

Figure 13.17

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Performance improving techniques for star schema l.jpg
Performance-Improving Techniques for Star Schema

  • Normalization of dimensional tables

  • Multiple fact tables representing different aggregation levels

  • Denormalization of the fact tables

  • Table partitioning and replication

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Data warehouse implementation road map l.jpg
Data Warehouse Implementation Road Map

Figure 13.21

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Data mining l.jpg
Data Mining

  • Seeks to discover unknown data characteristics

  • Automatically searches data for anomalies and relationships

  • Data mining tools

    • Analyze data

    • Uncover problems or opportunities

    • Form computer models based on findings

    • Predict business behavior with models

    • Require minimal end-user intervention

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Extraction of knowledge from data l.jpg
Extraction of Knowledge from Data

Figure 13.22

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Data mining process l.jpg
Data Mining Process

Figure 13.23

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel