DATA WAREHOUSE CONCEPTS A Definition A Data Warehouse

DATA WAREHOUSE CONCEPTS

A Definition · A Data Warehouse: • Is a repository for collecting, standardizing, and summarizing snapshots of transactional data contained in an organization’s operations or production systems • provides a historical perspective of information • is most often, but not exclusively, used for decision support applications and business information queries • can be more than one database • Is not a new concept

Another Definition · Decision Support: • • is a set of tools to easily access data is becoming a critical business tool is usually graphically oriented is empowering end users with tools to access vital business information • is moving lots of data down to the end user workstation • is a rapidly expanding area because of data warehousing efforts and projects

Why a warehouse? · For analysis and decision support, end users require access to data captured and stored in an organization’s operational or production systems · This data is stored in multiple formats, on multiple platforms, in multiple data structures, with multiple names, and probably created using different business rules

Why do we want a central data store

Interesting Statistics · 85% of the Fortune 1000 companies have, are implementing, or are looking at, data warehouses (Meta Group) · 90% of all information processing organizations will be pursuing a data warehouse strategy in the next three years (Meta Group) · The Decision Support industry will be a $1 Billion industry by 1997 (IDC & Forrester)

Data Warehouse Evolution - Stage 0 No end user access to production files “What we print” is “what you get”

Data Warehouse Evolution - Stage 1 End users denied direct access to production files Snapshots or copies of production files are made available instead Solution: Provide end users access to production systems

No Integration Between Systems

Data Warehouse Evolution - Stage 2

Data Characteristics Type Production Warehouse · Data Use · Level of detail · Currency Operational Detailed Real time, Latest value Relatively brief Dynamic Static Application wide Capture/update Coded Mgt Reporting Summary Multiple generations Forever · · · Longevity Stability Scope of definition Data Operations Data values Enterprise wide Read only Decoded

Transforming the Logical Model

Key Differences - Part 1 · Key differences between “data jails” (operational database) & warehouses • Subject orientation - operational systems are applicationsegmented (i. e. banks = auto loan, demand deposit accounting or mortgages). Subject areas for banks would be customer and each financial product • Level of integration - warehouses resolve years of application inconsistency in encoding/decoding, data name rationalization, etc • Update volatility - record at a time updates in operational database vs bulk loads in data warehouse • Time variance norms include: 30 -90 days of transactions for operational system, 1 -10 years for data warehouses

Key Differences - Part 2 Characteristic Operational Transaction volume Response time Updating Time Period Scope Activities High Low to huge Very fast Reasonable High volume Very Low Current Period Past to Future Internal External Focused, clerical Exploratory, operational analytical, managerial Predictable, Can be Unpredictable, periodic Ad hoc Queries Warehouse

Types of Warehouse Configurations · Enterprise · Division · Functional • Financial • Personnel • Engineering/Product · Departmental · Special Project

What’s Really Involved?

Typical Users of a Data Warehouse · Decision Support Analysts, Business Analysts • Marketing, Actuaries, Financial, Sales, Executive · Grocery Store attitudes • Going to the store, not knowing what they want • Close proximity says give me “everything” · Explorers • Don’t know what they want • Search on a random basis, non-repetitively • Frequently finds nothing, but when they do, there are huge rewards · Farmers • Know what they want • Non random searches, finds frequent “flakes of gold” • Finds small amounts of data

Advanced Warehouse Topics · Metadata repositories • Information about the data in the warehouse • Like a library card catalog • Data about when the information was created, what files accessed, how much data • Data about changes in business rules, processes • Context versus Content • “What does it mean? ” · Data Mining • Drilling down into databases with tools to find specific anomolies · Online Analysis Processing (OLAP) • Really means summary data