Data Mapping for Data Warehouse Design

Qamar Shahbaz

• Publisher: Morgan Kaufmann
• Year: 2015
• Print ISBN: 9780128051856
• eBook ISBN: 9780128053355
• Topic: Informatique
• Subtopic: Sciences générales de l'informatique

Data mapping in a data warehouse is the process of creating a link between two distinct data models' (source and target) tables/attributes. Data mapping is required at many stages of DW life-cycle to help save processor overhead; every stage has its own unique requirements and challenges. Therefore, many data warehouse professionals want to learn data mapping in order to move from an ETL (extract, transform, and load data between databases) developer to a data modeler role. Data Mapping for Data Warehouse Design provides basic and advanced knowledge about business intelligence and data warehouse concepts including real life scenarios that apply the standard techniques to projects across various domains. After reading this book, readers will understand the importance of data mapping across the data warehouse life cycle. - Covers all stages of data warehousing and the role of data mapping in each - Includes a data mapping strategy and techniques that can be applied to many situations - Based on the author's years of real-world experience designing solutions

Chapter 1

Introduction

Abstract

Data mapping is the most important design step in the data warehouse lifecycle and impacts project success or failure. The process links the design and implementation phase of the project. The outcome of the process is the data mapping document, which is the main tool for communication between project designers and developers.

Keywords

data mapping; source matrix (SMX); transformation design document

Data mapping is the most important design step in the data warehouse lifecycle, and it impacts project success or failure. The process links the design and implementation phases of the project. The outcome of the process is the data mapping document, which is the main tool for communication between project designers and developers.

The data mapping document provides detailed steps in the data mapping process and provides a guide that a data mapper can use to successfully complete his or her task. The document also provides data mapping scenarios explaining different approaches to a problem and their pros and cons.

Definition

Data mapping in a data warehouse is the process of creating a link between two distinct data models’ (source and target) tables or attributes.

Chapter 2

Data Mapping Stages

Abstract

Data mapping is required at many stages of the data warehouse lifecycle; every stage has its own unique requirements and challenges. A data mapper’s biggest challenge is to understand how data will flow from the source system to the final graphical user interface; this flow will determine how data should be transformed to achieve the end goal.

Keywords

access layer; extract; transform; load (ETL); landing area; load-ready area; staging

Data mapping is required at many stages of the data warehouse life cycle; every stage has its own unique requirements and challenges. A data mapper’s biggest challenge is to understand how data will flow from the source system to the final graphical user interface; this flow will determine how data should be transformed to achieve the end goal.

Mapping from the Source to the Data Warehouse Landing Area

This kind of mapping is usually one to one, but may sometimes include transformations that can be done inside the source database engine. Such mapping helps by saving processing overhead toward the technology end of the architecture.

Mapping from the Landing Area to the Staging Database

Mapping from the landing area to the staging area is done by:

1. Selecting a subset of columns from the complete source file

2. Splitting a single column into multiple columns

3. Using information coming from the header or trailer for different purposes to cast the timestamp or date values to match the Target Database formats and so on.

Mapping from the Staging Database to the Load Ready or Target Database

In this stage of the data warehouse lifecycle, source data is transformed into data warehouse data; data from here onward will be treated as information. This is why maximum importance, resources, and time should be given to this stage of data mapping. This book highlights various data mapping techniques for this stage.

The rules at this point can be complex and may involve multiple tables or sources. The rules here are governed by the vision that the data modeler has in mind about the data in a logical data model (LDM). All kinds of data integrations, history handling, data joining, lookups, reference data population, data-type conversion, and so on should be documented here. Usually this kind of data mapping is referred to as source matrix (SMX) or detail transformation design (DTD).

Mapping from Logical Data Model to the Semantic or Access Layer

Data mapping LDM to the semantic, access, or PL layer involves data transformation to bring data into a state where business users can run reports and use the data as information. We will discuss this kind of data mapping in this chapter; however, Chapter 12 maps data for a scenario involving PL attributes in LDM.

Chapter 3

Data Mapping Types

Abstract

There are two types of data mapping done in any data warehouse project. The high-level logical data mapping is part of the data modeling process, and implementation-oriented physical data mapping is used to document the transformation rule in detail.

Keywords

logical data mapping; physical data mapping

There are two types of data mapping done in any data warehouse project. High-level logical data mapping is part of the data modeling process, and implementation-oriented physical data mapping is used to document the transformation rule in detail.

Logical Data Mapping

After the logical data model (LDM) is complete, the data mapper will start mapping source elements to LDM. This is high-level mapping and provides a baseline for more detailed physical mappings; the rules written are related more to logical concepts than to implementation.

Physical Data Mapping

When the physical data model is complete, the data mapper will write physical mappings or would physicalize the logical mappings written earlier. Here more detailed rules are needed that convey the mapper’s vision of the data to the ETL (extract, transform, load) developer.

In this book, we will only discuss physical data mapping.

Chapter 4

Data Models

Abstract

The logical data model defines how data will be stored or linked in the data warehouse. The process gives a single picture of an organization’s business and is the key process in the data warehouse lifecycle.

Keywords

logical data model (LDM); data modeling; physical data model (PDM); primary key; ER (Entity Relationship) Diagram

Before we go into a detailed discussion about data mapping, we need to understand the work that has been done before data mapping starts. The sequence in which a project usually flows is described in Figure 4.1.

Figure 4.1 Data warehouse design steps.

A data mapper needs to understand the data model of the project to be able to make correct data mappings. The model might contain different forms of modeling techniques and require special considerations for certain entities or tables.

The data modeler starts by modeling the client’s real-life objects into high-level concepts. This will result in a conceptual model; it can be high level or a relatively detailed one. Here the data modeler will not add any columns and might club many tables into a single concept. For example, the data modeler can group an employee, personal details, history, and so on into one concept employee (Figure 4.2).

Figure 4.2 Data model types for data warehouse.

The next step in the modeling process is to identify logical entities in the client’s business, add attributes for each entity, and create relationships among entities. This generates a logical data model of the business that represents all entities of the business. This model will have complete information from a logical representation perspec...

Table of contents

• Cover image
• Title page
• Table of Contents
• Copyright
• Dedication
• Chapter 1. Introduction
• Chapter 2. Data Mapping Stages
• Chapter 3. Data Mapping Types
• Chapter 4. Data Models
• Chapter 5. Data Mapper’s Strategy and Focus
• Chapter 6. Uniqueness of Attributes and its Importance
• Chapter 7. Prerequisites of Data Mapping
• Chapter 8. Surrogate Keys versus Natural Keys
• Chapter 9. Data Mapping Document Format
• Chapter 10. Data Analysis Techniques
• Chapter 11. Data Quality
• Chapter 12. Data Mapping Scenarios
• Glossary and Nomenclature List
• Bibliography