CS614 Midterm Notes

CS614 – Date Warehousing · Reference Short Notes for Mid Term Papers · Muhammad Faisal Dar · MIT 4th Semester · Difference between OLTP & DWH (page21) · What is a DWH? (page9)

A complete repository of historical corporate data extracted from transaction systems that is available for ad-hoc access by knowledge workers

Quantifying size of data (page 6)

1 MB 220 or 106 bytes Small novel – 31/2 Disk 1 GB 230 or 109 bytes Paper rims that could fill the back of a pickup van 1 TB 240 or 1012 bytes 50,000 trees chopped and converted into paper and printed 2 PB 1 PB = 250 or 1015 bytes Academic research libraries across the U.S. 5 EB 1 EB = 260 or 1018 bytes All words ever spoken by human beings

OLTP

DWH

Primary key used Primary key NOT used No concept of Primary Index Primary index used May use a single table Uses multiple tables Few rows returned Many rows returned High selectivity of query Low selectivity of query Indexing on primary key (unique) Indexing on primary index (non-unique)

Typical Applications of DWH (page27)

There are, and there can be many applications of a data warehouse. It is not possible to discuss all of them. Some representative applications are listed to be discussed as follows:

o Fraud detection o Profitability analysis o Direct mail/database marketing o Credit risk prediction o Customer retention modeling o Yield management o Inventory management

Fraud detection (page28)

•  By observing data usage patterns
•  People have typical purchase patterns
•  Deviation from patterns
•  Certain cities notorious for fraud
•  Certain items bought by stolen cards
•  Similar behavior for stolen phone cards

What are the goals of normalization? (page32)

•  Eliminate redundant data.
•  Ensure data dependencies make sense.

Five principal De-normalization Techniques (page43)

1. Collapsing Tables. • Two entities with a One-to-One relationship. • Two entities with a Many- to-Many relationship.
2. 2. Pre-Joining.
3. 3. Splitting Tables (Horizontal/Vertical Splitting).
4. 4. Adding Redundant Columns (Reference Data).
5. 5. Derived Attributes (Summary, Total, Balance etc).

Splitting Tables: Horizontal splitting (page46)

ADVANTAGE

•  Enhance security of data.
•  Organizing tables differently for different queries.
•  Reduced I/O overhead.
•  Graceful degradation of database in case of table damage.
•  Fewer rows result in flatter B- trees and fast data retrieval.

Issues of De-Normalization (page53)

• Storage
• Performance
• Ease-of-use

OLAP: Facts & Dimensions (page65)

•  FACTS: Quantitative values (numbers) or “measures.”

e.g., units sold, sales $, Co, Kg etc.

•  DIMENSIONS: Descriptive categories.

e.g., time, geography, product etc.