Docstoc

An Overview of Data Warehousing and OLAP Technology

Document Sample
An Overview of Data Warehousing and OLAP Technology Powered By Docstoc
					An Overview of Data Warehousing
and OLAP Technology



    2012/03/13
Outline

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
    tasks.
    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
    critical.
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
    sources.

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

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

n   MOLAP
    q   Array
n   ROLAP
    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
Examples

n   Oracle

n   Informix

n   Sybase

n   …

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:9/12/2013
language:
pages:12