DataWarehousing and DataMining

Prof. Sin-Min Lee

DATA WAREHOUSE, OLAP, and DATA MINING

• Concepts– Data warehousing

– OLAP (On-Line Analytical Processing)

– Data mining

• Case Studies– WebTarget (USN)

– TFDW (USMC)

DATA WAREHOUSE

• DATABASE MANAGEMENT IN THE INTERNET ERA

• CLIENT/SERVER - BASED

• ANALYTICAL vs OPERATIONAL (OLAP vs OLTP)

• MULTI-DIMENSIONAL ANALYSIS

• DATA WAREHOUSE (ENTERPRISE-WIDE) vs

DATA MART (FUNCTIONAL AREA)

MULTIDIMENSIONAL NATURE OF DATA WAREHOUSES

• BORING QUERY: “How many Sailors/Marines chose not to stay in the Navy/Marine Corps this year?”

• USEFUL QUERY: “What was our retention (separation) rate this year by community by paygrade by years of service by gender by rating and how did it compare to last year and what can we expect next year?”

DW ARCHITECTURE

DW 3-TIER ARCHITECTURE

1. DATA QUALITY & DATA CLEANSING

• #1 REASON FOR DW PROJECT FAILURE• PROBLEMS

- Database heterogeneity- Data heterogeneity

• FUNCTIONALITY OF TOOLS- Removing unwanted data from operational databases- Converting to common data names and definitions- Calculating summaries and derived data- Establishing defaults for missing data- Accommodating source data definition changes

APPROACHES TO DATA CLEANSING

• AUTOMATIC CODE GENERATION

Creates code to convert from source to target data

• DATA REPLICATION TOOLS

Captures changes to source database from recovery logs and database triggers and propagates changes to copies of the data

• DYNAMIC TRANFORMATION ENGINES

Rule-driven systems that capture data from source databases at user-defined intervals, transform it, and export it to a data warehouse/mart target

2. METADATA (What does the data mean?)

• Logical Structure of DW Including End User Views

• Identification of Authoritative Data Sources

• Transformation Rules for Populating DW

• Transformation Rules to Deliver Data to End-User Analytical Tools

• Subscription Information for Information Delivery

• DW Operational Information

• DW Usage Metrics

• Security Authorizations, Access Control Lists, etc.

3. DATA WAREHOUSE DATABASE

• PARALLEL COMPUTING PLATFORMS

Exs: Symmetric (Shared) Multiprocessors (SMPs);

Massively Parallel Processors (MPPs)

• ROLAP

Relational DBMS with “Heavy Duty” Indexing Capabilities

• MOLAP

Multidimensional Databases (MDDBs)

3rd Party Tools that Augment Relational Model

4. DATA MARTS

• A Data Warehouse Focused on a Specific Subject Area

• Subsidiary to a Data Warehouse of Integrated Data

• More Rapidly Deployable than a Data Warehouse

• Subject-based vice Enterprise-based

5. ACCESS TOOLS

• QUERY AND REPORTING TOOLS

- Managed query tools: Layer between user and SQL (e.g., BrioQuery)

- Configurable report generators (e.g., Brio’s BrioReport)

• APPLICATIONS

- Application development platforms (e.g., PowerSoft’s PowerBuilder; Microsoft’s Visual Basic)

ACCESS (cont’d)

• OLAP

- Support of multidimensional analysis

- Ability to drilldown and rollup along any of the

predefined dimensions

- Major vendors: Cognos, Business Objects, Brio

MULTIDIMENSIONAL DATA MODEL: STAR SCHEMA

• FACTS: Core data element being analyzed, e.g., Units_of_Items_Sold

• DIMENSIONS: Attributes about FACTS, e.g., Product_Type, Purchase_Date

ROLE OF METRICS

• Facts should be defined as Measures of Effectiveness (sometimes called Key Performance Indicators (KPI’s))

• Exs: NEC Reutilization Rate

Retention Rate

Attrition Rate

Readiness (Personnel)

COGNOS DEMO

• http://www.cognos.com/products/tours/analysis_launch.html

ACCESS: Data Mining

• “Searching for meaningful patterns in large data sets”

• Knowledge acquisition• Motivated and facilitated by:

– Availability of large data sets– Advances in storage technology– Data warehouse technology– E-commerce and the Internet

• Exploratory vs. confirmatory analysis

6. DW ADMINISTRATION AND MANAGEMENT

• “Normal” DBA Responsibilities plus:

• Source Data Quality Checks

• Keeping track of what all the source data means

• Managing Very Large Databases (gigabytes or terabytes in size)

7. INFORMATION DELIVERY SYSTEM

• How to get information from the data warehouse to users?

• Users subscribe to the data warehouse.

• Specifically, they subscribe to specific reports to be delivered on a periodic basis.

• Reports are delivered to user’s Web browser as per prescribed frequency.

• Powerful tool for delivering information to the people who need it in an extremely timely fashion. True MIS; true DSS.

BENEFITS OF DATA WAREHOUSE

• Freedom from restrictions of operational databases• Decision-oriented• Extremely efficient presentation of management

information• Widespread access to critical information for those who

need it when they need it• Knowledge discovery• Improves business intelligence• Relatively inexpensive to implement• Does not require re-engineering of legacy systems

GIS: GEOGRAPHIC INFORMATION SYSTEMS

• Ability to visualize data spatially

• Maps on top of a relational DBMS

• Data is viewed on maps vice from tables

• Features:

- Thematic maps

- Spatial queries

- Geocoding of data

• Vendors: MapInfo; ESRI (ArcInfo)