An overview of Data Warehousing and OLAP Technology

1
An overview of Data Warehousing and OLAP
Technology

• Presented By
• Manish Desai

2

• Introduction
• What is data warehouse ?
• Explanation of definition
• Data warehouse Vs. Operational Database
• Data warehouse architecture
• Back end tools
• Conceptual model
• Database design
• Warehouse servers
• Index structures
• Meta data
• Conclusion
• References

3
Introduction

• Essential elements of decision support
• Enables The Knowledge Worker to make better and
  faster decisions
• Used in many industries like
• Manufacturing (for order shipment)
• Retail (for inventory management)
• Financial Services (claims and risk analysis)
• Every major database vendor offers product in
  this area

4
What is Data Warehouse ?

• A data warehouse is a subject-oriented,
  integrated, time-varying, non-volatile collection
  of data that is used primarily in organizational
  decision making
• Typically maintained separately from operational
  databases

5
Explanation of definition

• Subject-Oriented
• Designed around subject such as customer, vendor,
  product and activity
• Does not includes data that are not needed for
  Decision support system (DSS)
• Integrated
• Most important feature
• Consistent naming convention, measurement of
  variables and so forth
• The data should be stored in single globally
  acceptable fashion

6
Explanation (continues)

• Time Varying
• All data in the warehouse should be accurate as
  of some moment in time
• Data stored over a long time horizon (5 10
  years)
• Key structure contains element of time
  (implicitly or explicitly)
• Data once correctly recorded cant be updated
• Non Volatile
• No Update of data allowed
• only loading and access of data operations

7
Data Warehouse Vs. Operational Database
8
Architecture

• Data sourcing,migration,cleanup tools
• Meta data repository
• Data marts
• Data query, reporting, analysis and mining tools
• Data warehouse administration and management

9
Architecture (continues)

• Distributed Data warehouse
• Load balancing, scalability,higher availability
• Meta data replicated and centrally administrated
• Too expansive
• Data marts
• Departmental subset focused on selected subjects
• example marketing department includes customer,
  sales and product tabels
• Has own repository and administration
• May lead to complex integration problems if not
  designed properly

10
Back end tools and Utilities

• Data cleaning, loading, refreshing tools
• Cleaning
• Multiple source, possibility of errors
• Example replace string sex by gender
• Loading
• Building indices, sorting and making access paths
• Large amount of data
• Incremental loading
• Only updated tuples are inserted ,Process hard to
  manage
• Refresh
• Propagating updates
• When to refresh ?
• Set by administrator depending on user needs and
  traffic

11
Conceptual Model and front end tools

• Multi dimensional view
• Dimensions together uniquely determine the
  measure
• Example Sales can be represented as
  city,product, data
• Each dimension is described by set of attribute
• Example product consist of
• Category of product
• Industry of product
• Year of introduction
• Front end tools
• Multi dimensional spreadsheet
• Supports Pivoting-reorientation
• Roll_up - summarized data
• Drill_down - go from high level to low level
  summary

12
Database design

• Two ways to represent Multi dimensional model
• Star schema
• Database consist of single fact table and single
  table for each dimension
• Each tuples in fact table consist of pointer to
  each of dimension
• Snowflake schema
• Refinement over star schema
• Dimensional hierarchy is explicitly represented
  by normalizing dimension tables

13
Warehouse Servers

• Specialized SQL servers
• Provides advanced query language and query
  processing support for SQL queries over star and
  snowflake schemas
• Example Redbrick
• ROLAP
• Between relational back end and client front end
  tools
• Extend traditional relational servers to support
  multidimensional queries
• Example Microstratergy
• MOLAP
• Multidimensional storage engine
• Direct mapping
• Example Essbase from Arbor Inc.

14
Index structures

• Bit map indices
• Use single bit to indicate specific value of
  attribute
• Example
• instead of storing eight characters to record
  engineer as skill of employee use single bit
• id Name Skill
• 1000 John 1
• Join indices
• Maintains the relationship between foreign key
  with its matching primary keys

15
Meta data and warehouse management

• Its data about data
• Used for building, maintain, managing and using
  data warehouse
• Administrative meta data
• Information about setting up and using warehouse
• Business meta data
• Business terms and definition
• Operational meta data
• Information collected during operation of
  warehouse

16
Conclusion

• Data warehouse is the technology for the future.
• data warehouse enables knowledge worker to make
  faster and better decisions

17
References

• Inmon W. H.,Building the data warehouse
• www.olapcouncil.org
• www.pwp.starnetinc.com
• www.arborsoft.com
• Kimball, R. The data warehouse toolkit.