WIXLIB

About the TutorialA data warehouse is constructed by integrating data from multipleheterogeneous sources. It supports analytical reporting, structured and/or adhoc queries and decision making. This tutorial adopts a step-by-step approach toexplain all the necessary concepts of data warehousing.AudienceThis tutorial will help computer science graduates to understand the basic-toadvanced concepts related to data warehousing.PrerequisitesBefore proceeding with this tutorial, you should have an understanding of basicdatabase concepts 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 ofTutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain,copy, distribute or republish any contents or a part of contents of this e-book inany manner without written consent of the publisher.We strive to update the contents of our website and tutorials as timely and asprecisely as possible, however, the contents may contain inaccuracies or errors.Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy,timeliness or completeness of our website or its contents including this tutorial.If you discover any errors on our website or in this tutorial, please notify us atcontact@tutorialspoint.comi

Table of ContentsAbout the Tutorial . iAudience . iPrerequisites . iCopyright & Disclaimer . iTable of Contents . ii1. OVERVIEW. 1Understanding a Data Warehouse . 1Why a Data Warehouse is Separated from Operational Databases . 2Data Warehouse Features. 2Data Warehouse Applications . 3Types of Data Warehouse . 32. CONCEPTS . 5What is Data Warehousing? . 5Using Data Warehouse Information . 5Integrating Heterogeneous Databases . 5Functions of Data Warehouse Tools and Utilities . 63. TERMINOLOGIES . 8Metadata . 8Metadata Repository . 8Data Cube . 9Data Mart . 11Virtual Warehouse . 124. DELIVERY PROCESS . 13Delivery Method . 13ii

IT Strategy. 14Business Case . 14Education and Prototyping . 14Business Requirements . 14Technical Blueprint . 15Building the Version . 15History Load . 15Ad hoc Query . 16Automation . 16Extending Scope . 16Requirements Evolution . 165. SYSTEM PROCESSES. 18Process Flow in Data Warehouse . 18Extract and Load Process . 18Clean and Transform Process . 19Backup and Archive the Data . 20Query Management Process . 206. ARCHITECTURE . 21Business Analysis Framework . 21Three-Tier Data Warehouse Architecture . 21Data Warehouse Models . 22Load Manager . 24Warehouse Manager . 25Query Manager . 26Detailed Information . 27Summary Information . 28iii

7. OLAP . 29Types of OLAP Servers . 29Relational OLAP . 29Multidimensional OLAP . 29Hybrid OLAP. 29Specialized SQL Servers . 30OLAP Operations. 30OLAP vs OLTP . 358. RELATIONAL OLAP . 37Relational OLAP Architecture . 379. MULTIDIMENSIONAL OLAP. 39MOLAP Architecture . 39MOLAP vs ROLAP . 4010. SCHEMAS . 41Star Schema . 41Snowflake Schema . 42Fact Constellation Schema . 42Schema Definition . 4311. PARTITIONING STRATEGY . 46Why is it Necessary to Partition? . 46Horizontal Partitioning . 47Vertical Partition . 49Identify Key to Partition . 5112. METADATA CONCEPTS . 52What is Metadata? . 52iv

Categories of Metadata . 52Role of Metadata . 53Metadata Respiratory . 54Challenges for Metadata Management . 5513. DATA MARTING. 56Why Do We Need a Data Mart? . 56Cost-effective Data Marting . 56Designing Data Marts . 58Cost of Data Marting . 5914. SYSTEM MANAGERS . 61System Configuration Manager .

DWH 6 Process of Query-Driven Approach 1. When a query is issued to a client side, a metadata dictionary translates the query into an appropriate form for individual heterogeneous sites involved. 2. Now these queries are mapped and sent to the local query processor. 3. The results from heterogeneous sites are integrated into a global answer