In the Beginning, life was simple
Our information needs
Kept growing. (The Spider web)
To explore and discuss the purpose and principles of data warehousing.
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?
Making consolidated reports
Finding relationships and correlations
Banks identifying credit risks
Insurance companies searching for fraud
Comparison Chart of Database Types
Supporting a Complete Solution
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.
Building a Data Warehouse
Install front-end tools
Test and deploy
Stage 1: Analysis
Timeliness of data
Create an enterprise-level data dictionary
Identify facts and dimensions
Stage 2: Design
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.
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
Load the data into the warehouse
Importing Data Into the Warehouse
Stage 4: Install Front-end Tools
Data mining tools
Stage 5: Test and Deploy
Performance tweaking based on usage
Time and expense
Managing the complexity
Update procedures and maintenance
Changes to source systems over time
Changes to data needs over time
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
Central Warehouse Application Flow
Web Application Demo
STORET Central Warehouse -Potential Future Enhancements
More query functionality
Additional report types
Additional source systems?
Data Warehouse Components
Data Warehouse Components -Detailed