An overview of data warehousing and olap technology l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 17

An overview of Data Warehousing and OLAP Technology PowerPoint PPT Presentation


  • 219 Views
  • Uploaded on
  • Presentation posted in: General

An overview of Data Warehousing and OLAP Technology. Presented By Manish Desai. 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

Download Presentation

An overview of Data Warehousing and OLAP Technology

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


An overview of data warehousing and olap technology l.jpg

An overview of Data Warehousing and OLAP Technology

Presented By

Manish Desai


Slide2 l.jpg

  • 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


Introduction l.jpg

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


What is data warehouse l.jpg

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


Explanation of definition l.jpg

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


Explanation continues l.jpg

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


Data warehouse vs operational database l.jpg

Data Warehouse Vs. Operational Database


Architecture l.jpg

Architecture

  • Data sourcing,migration,cleanup tools

  • Meta data repository

  • Data marts

  • Data query, reporting, analysis and mining tools

  • Data warehouse administration and management


Architecture continues l.jpg

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


Back end tools and utilities l.jpg

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


Conceptual model and front end tools l.jpg

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


Database design l.jpg

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


Warehouse servers l.jpg

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.


Index structures l.jpg

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


Meta data and warehouse management l.jpg

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


Conclusion l.jpg

Conclusion

  • Data warehouse is the technology for the future.

  • data warehouse enables knowledge worker to make faster and better decisions


References l.jpg

References

  • Inmon W. H.,Building the data warehouse

  • www.olapcouncil.org

  • www.pwp.starnetinc.com

  • www.arborsoft.com

  • Kimball, R. The data warehouse toolkit.


  • Login