# Presentation: 392822

## An Introduction to Data Warehousing

*Presented by*
- Joseph M. Wilson*EPA*

## In the Beginning, life was simple...

## But...

## Our information needs...

## Kept growing. (The Spider web)

- SOURCE:  William H. Inmon

## Purpose

- 	To explore and discuss the purpose and principles of data warehousing.

## Briefing Contents

## So What Is a Data Warehouse?

- Definition:  A data warehouse is the data repository of an enterprise.  It is generally used for research and decision support.
- By comparison: an OLTP (on-line transaction processor) or operational system is used to deal with the everyday running of one aspect of an enterprise.
- OLTP systems are usually designed independently of each other and it is difficult for them to share information.

## Why Do We Need Data Warehouses?

- Consolidation of information resources
- Improved query performance
- Separate research and decision support functions from the operational systems
- Foundation for data mining, data visualization, advanced reporting and OLAP tools

## What Is a Data Warehouse Used for?

- Knowledge discovery
- Making consolidated reports
- Finding relationships and correlations
- Data mining
- Examples
- Banks identifying credit risks
- Insurance companies searching for fraud
- Medical research

## Goals
Structure
Size
Performance optimization
Technologies used

- Structure
- Size
- Performance optimization
- Technologies used

**How Do Data Warehouses Differ From Operational Systems?**

## Comparison Chart of Database Types

| Data warehouse | Operational system |
| --- | --- |
| Subject oriented | Transaction oriented |
| Large (hundreds of GB up to several TB) | Small (MB up to several GB) |
| Historic data | Current data |
| De-normalized table structure (few tables, many columns per table) | Normalized table structure (many tables, few columns per table) |
| Batch updates | Continuous updates |
| Usually very complex queries | Simple to complex queries |

## Design Differences

- Star Schema

- Data Warehouse

- Operational System

- ER Diagram

## Supporting a Complete Solution

- Operational System-
- Data Entry

- Data Warehouse-
- Data Retrieval

## Data Warehouses, Data Marts, and Operational Data Stores

- Data Warehouse – The queryable source of data in the enterprise.  It is comprised of the union of all of its constituent data marts.
- Data Mart – A logical subset of the complete data warehouse.  Often viewed as a restriction of the data warehouse to a single business process or to a group of related business processes targeted toward a particular business group.
- Operational Data Store (ODS) – A point of integration for operational systems that developed independent of each other.  Since an ODS supports day to day operations, it needs to be continually updated.

- SOURCE:  Ralph Kimball

## Briefing Contents

## Building a Data Warehouse

- Analysis
- Design
- Import data
- Install front-end tools
- Test and deploy

- Data Warehouse Lifecycle

## Stage 1: Analysis

- Identify:
- Target Questions
- Data needs
- Timeliness of data
- Granularity
- Create an enterprise-level data dictionary
- Dimensional analysis
- Identify facts and dimensions

- 	Analysis
- Design
- Import data
- Install front-end tools
- Test and deploy

## Stage 2: Design

- Star schema
- Data Transformation
- Aggregates
- Pre-calculated Values
- HW/SW Architecture

- Analysis
- 	Design
- Import data
- Install front-end tools
- Test and deploy

- Dimensional Modeling

## Dimensional Modeling

- Fact Table – The primary table in a dimensional model that is meant to contain measurements of the business.
- Dimension Table – One of a set of companion tables to a fact table. Most dimension tables contain many textual attributes that are the basis for constraining and grouping within data warehouse queries.

- SOURCE:  Ralph Kimball

## Stage 3: Import Data

- Identify data sources
- Extract the needed data from existing systems to a data staging area
- Transform and Clean the data
- Resolve data type conflicts
- Resolve naming and key conflicts
- Remove, correct, or flag bad data
- Conform Dimensions
- Load the data into the warehouse

- Analysis
- Design
- 	Import data
- Install front-end tools
- Test and deploy

## Importing Data Into the Warehouse

- Operational Systems
- (source systems)

## Stage 4: Install Front-end Tools

- Reporting tools
- Data mining tools
- GIS
- Etc.

- Analysis
- Design
- Import data
- 	Install front-end tools
- Test and deploy

## Stage 5: Test and Deploy

- Usability tests
- Software installation
- User training
- Performance tweaking based on usage

- Analysis
- Design
- Import data
- Install front-end tools
- 	Test and deploy

## Special Concerns

- Time and expense
- Managing the complexity
- Update procedures and maintenance
- Changes to source systems over time
- Changes to data needs over time

## Briefing Contents

## Goals of the STORET Central Warehouse

- Improved performance and faster data retrieval
- Ability to produce larger reports
- Ability to provide more data query options
- Streamlined application navigation

## Old Web Application Flow

**Old Web Application Flow**

## Central Warehouse Application Flow

- Search Criteria Selection

- Report Size Feedback/
- Report Customization

- Report Generation

## Web Application Demo

- STORET Central Warehouse:

**Web Application Demo**

## STORET Central Warehouse – Potential Future Enhancements

- More query functionality
- Additional report types
- Web Services
- Additional source systems?

## Data Warehouse Components

- SOURCE:  Ralph Kimball

## Data Warehouse Components – Detailed

- SOURCE:  Ralph Kimball

## Briefing Contents