Data Warehousing Module 2 PDF

MODULE 2 DATA WAREHOUSING Introduction Imagine a large organization having different departments, each with their own database systems. A business analyst would like to generate r...

MODULE 2 DATA WAREHOUSING Introduction Imagine a large organization having different departments, each with their own database systems. A business analyst would like to generate reports for decision support. She approaches each department but has problems with some of them whose main roles are just to handle data transactions – not reports. Those that do give her information give data in a number of different formats. Customer names are saved differently, birthdates are in mm/dd/yy and dd/mm/yy and so on. Wouldn’t it save the business analyst so much time and effort if there was a central repository containing information needed for her to generate the reports that she needs with the data in a standardized format, too? In this module, we will learn about data warehousing which makes tasks like the above easier to handle. Learning Objectives After working on this module, you should be able to: 1. Discuss the key concepts of data warehousing; and 2. Identify resources needed for data warehousing. 2.1. Data Warehouses and Data Marts A data warehouse is a physical repository where relational data are specially organized to provide enterprise-wide, cleansed data in a standardized format In our previous module, we have learned what database systems are. In turn, a data warehouse is a collection of integrated, subject-oriented databases. Each unit of data is non-volatile and relevant to some moment in time. Data in data warehouses are NOT in 3NF. That being so, they are referred to as BIG DATA. Since they are not normalized, some data may be redundant. The redundancies will result in, well, BIG data. However, BIG DATA is more useful for DECISION SUPPORT. This is good since the purpose of a data warehouse is provide aggregate data for decision making. You are not that interested in what the data for each table are, you are more interested in how the company will move forward given that data. There may be questions or decisions which are specialized for specific people. Thus, separate entities called DATA MARTS are used to provide specialized and strategic answers for specific people. This keeps it simple for the users. Small problems are easier to solve. Data marts, therefore, are a subset of the data warehouse that support the requirements of a particular department or business function. Prepared By: Engr. Marteen Beravon Remolacio Fundamentals of Data Warehousing A data mart is a departmental data warehouse that stores only relevant data. Data marts can be dependent or independent. A dependent data mart is a subset that is created directly from a data warehouse. An independent data mart, on the other hand, is a small data warehouse designed for a strategic business unit or a department. Study Question How will organizations benefit from data warehouses and data marts? 2.2. Alternate Data Warehousing Architecture Alternative data warehousing architectures include: a. Independent Data Marts b. Data Mart Bus Architecture c. Hub-and-Spoke Architecture d. Centralized Data Warehouse e. Federated Data Warehouse Study Questions 1. How are the alternative data warehousing architectures different from the usual architecture? 2. Discuss the advantages and disadvantages of the different alternative data warehousing architectures. Activity 2-1 Objective: To identify resources needed for data warehousing. Task: Identify a business or organization that might benefit from using data warehouses and data marts. List down the resources they will need to get these up and running. Prepared By: Engr. Marteen Beravon Remolacio Fundamentals of Data Warehousing