WIXLIB

Data WarehousingAbout the TutorialA data warehouse is constructed by integrating data from multiple heterogeneous sources.It supports analytical reporting, structured and/or ad hoc queries and decision making.This tutorial adopts a step-by-step approach to explain all the necessary concepts of datawarehousing.AudienceThis tutorial will help computer science graduates to understand the basic-to-advancedconcepts related to data warehousing.PrerequisitesBefore proceeding with this tutorial, you should have an understanding of basic databaseconcepts such as schema, ER model, structured query language, etc.Copyright & Disclaimer Copyright 2014 by Tutorials Point (I) Pvt. Ltd.All the content and graphics published in this e-book are the property of Tutorials Point (I)Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republishany contents or a part of contents of this e-book in any manner without written consentof the publisher.We strive to update the contents of our website and tutorials as timely and as precisely aspossible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt.Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of ourwebsite or its contents including this tutorial. If you discover any errors on our website orin this tutorial, please notify us at contact@tutorialspoint.comi

Data WarehousingTable of ContentsAbout the Tutorial . iAudience . iPrerequisites . iCopyright & Disclaimer. iTable of Contents . ii1.DWH OVERVIEW. 1Understanding a Data Warehouse . 1Why a Data Warehouse is Separated from Operational Databases . 1Data Warehouse Features . 2Data Warehouse Applications . 2Types of Data Warehouse . 22.DWH CONCEPTS . 4What is Data Warehousing? . 4Using Data Warehouse Information . 4Integrating Heterogeneous Databases . 4Functions of Data Warehouse Tools and Utilities . 53.DWH TERMINOLOGIES . 6Metadata . 6Metadata Repository . 6Data Cube . 6Data Mart. 8Virtual Warehouse . 94.DWH DELIVERY PROCESS . 10Delivery Method . 10ii

Data WarehousingIT Strategy . 10Business Case . 11Education and Prototyping . 11Business Requirements . 11Technical Blueprint . 12Building the Version . 12History Load . 12Ad hoc Query . 12Automation . 12Extending Scope . 13Requirements Evolution . 135.DWH SYSTEM PROCESSES. 14Process Flow in Data Warehouse . 14Extract and Load Process . 14Clean and Transform Process . 15Backup and Archive the Data . 16Query Management Process . 166.DWH ARCHITECTURE . 17Business Analysis Framework. 17Three-Tier Data Warehouse Architecture . 17Data Warehouse Models . 18Load Manager . 19Warehouse Manager . 20Query Manager . 21Detailed Information . 22Summary Information . 23iii

Data Warehousing7.DWH OLAP. 24Types of OLAP Servers . 24Relational OLAP . 24Multidimensional OLAP . 24Hybrid OLAP . 24Specialized SQL Servers . 24OLAP Operations . 25OLAP vs OLTP . 298.DWH RELATIONAL OLAP . 31Relational OLAP Architecture . 319.DWH MULTIDIMENSIONAL OLAP. 33MOLAP Architecture . 33MOLAP vs ROLAP . 3410. DWH SCHEMAS . 35Star Schema . 35Snowflake Schema . 36Fact Constellation Schema . 36Schema Definition . 3711. DWH PARTITIONING STRATEGY . 40Why is it Necessary to Partition? . 40Horizontal Partitioning . 40Vertical Partition . 42Identify Key to Partition . 44iv

Data Warehousing12. DWH METADATA CONCEPTS . 45What is Metadata? . 45Categories of Metadata . 45Role of Metadata . 46Metadata Repository . 47Challenges for Metadata Management . 4713. DWH DATA MARTING . 48Why Do We Need a Data Mart? . 48Cost-effective Data Marting . 48Designing Data Marts . 50Cost of Data Marting . 5014. DWH SYSTEM MANAGERS . 52System Configuration Manager . 52System Scheduling Manager . 52S

data warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data. This data helps analysts to take informed decisions in an organization. An operational database undergoes frequent changes on a daily basis on account of the transactions that take place. Suppose a business executive wants to analyze previous feedback on any data such as a product, a supplier .