An overview of Data Warehousing and OLAP Technology

1
An overview of Data Warehousing and OLAP
Technology

• Surajit Chaudhuri
  Umeshwar Dayal
• Microsoft Reserch,
  Redmond HP Labs,
  Palo Alto

Presented by- Krishma Dutta
2
Outline

• Introduction
• Need of Data Warehousing and OLAP
• Architecture of Data Warehousing
• Front-Back End Tools
• Database Design Methodology
• Conclusion

3
Data Warehousing- An Introduction

• Defined in many different ways
• In simplest terms Data Warehouse can be defined
  as collection of Data marts
• A data warehouse is a subject-oriented,
  integrated, time-variant, and nonvolatile
  collection of data in support of managements
  decision-making process.W. H. Inmon
• A data warehousing is a collection of decision
  support technologies, aimed at enabling the
  knowledge worker to make better decisions

4
Need of Data Warehousing and OLAP
5
Tiered Architecture
6
Front-Back End Tools

• Front End Tools
• Rollup (Drill-up)
• Drill-down (Roll-down)
• Slice and dice
• Back End Tools
• Data Cleaning
• Load
• Refresh

7
Conceptual Model
Total annual sales of TV in U.S.A.
Date

2
1
sum
3
4
TV
Product
U.S.A
PC
PVR
sum
Canada
Country
Mexico
sum
ALL
8
Database Design Methodology
A fact table in the middle connected to a set of
dimension tables
A refinement of star schema where hierarchy is
normalized into a set of smaller dimension
tables, forming a shape similar to snowflake
9
Star Schema
10
Snowflake Schema
Time
T_key T_day T_day_week T_month T_quarter T_year
Sales Fact Table
Snowflake Schema
11
Summary

• Data warehouse
• A subject-oriented, integrated, time-variant, and
  nonvolatile collection of data in support of
  managements decision-making process
• Architecture of Date warehouse
• Consisting of Warehouse servers, front end and
  back end tools
• OLAP operations drilling, rolling, slicing,
  dicing and pivoting
• Multi dimensional model of Data warehouse
• Data cube
• Star Schema
• Snowflake Schema

12
Thank You