Learning Center
Plans & pricing Sign in
Sign Out

An Overview of Data Warehousing and OLAP Technology


									An Overview of Data Warehousing
and OLAP Technology


n   Introduction
    q   OLTP applications vs. OLAP applications
n   Architecture of data warehousing
n   Back end tools
n   Conceptual model
n   Front end tools
n   Some examples
Introduction (OLTP applications)
n   OLTP applications typically automate clerical data processing
    q such as order entry and banking transactions

n   These tasks are structured and repetitive, and consist of short,
    atomic, isolated transactions.

n   The transactions require detailed, up-to-date data.

n   Consistency and recoverability of the operational database are
Introduction (OLAP applications)
n   OLAP applications are designed for supporting decision making.

n   Historical, summarized and consolidated data are often used.

n   The workload of data warehouses are query intensive with
    mostly ad hoc, complex queries that access millions of records.

n   Query throughput and response times are critical.
Introduction (problem statement)
n   Given that operational databases, trying to execute complex OLAP
    queries would result in unacceptable performance.

n   Decision support requires data that might be missing from the
    operational databases.

n   Decision support requires consolidating data from many heterogeneous

n   Supporting the multidimensional data models and operations requires
    special data organization, access methods and implementation

n   =>Data Warehouse
Architecture of data warehouse
Back end tools

n   Data extraction
    q   ODBC, Oracle Open Connect, and Sybase Enterprise
        Connect, etc.
n   Data clearing
    q   Tools that help to detect data anomalies and correct them.
n   Load
    q   Tools that help to load data into the warehouse.
n   Refresh
    q   Refreshing a warehouse consists in propagating updates
        on source data.
Conceptual model
Database design

    q   Array
    q   Star schema
Warehouse servers

n   Index structures
    q   Bit map indices, join indices
n   Materialized views
n   Transformation of complex SQL queries
n   Parallel processing
n   SQL extensions
    q   Aggregate function
    q   Reporting features
    q   Multiple group-by
    q   Comparisions
Front end tools

n   Spreadsheet
    q   Operations for spreadsheet
        n   Pivoting
        n   Rollup
        n   Drill-down
        n   Slice_and_dice
n   Query/Reporting
n   Data mining tools

n   Oracle

n   Informix

n   Sybase

n   …

To top