Monday, September 10, 2012

Part 1: Data Modeling Overview

1.1: Prerequisites for this Session


  • Should be aware of DBMS ConceptsS
  • Should be aware of Data warehouse concepts
  • Should be aware of Data base concepts (any database)
  • Should have 2+ years of experience on any ETL Tool

1.2 : Expectations from Session 

1.3: Scope of the Session

1. Data Model Overview
2. Data Models
3. How to Model Data
4. Data Modeling
Conceptual Data Model
Relational Data Modeling
Dimensional Data Modeling
Logical Data Modeling
Physical Data Modeling
5. Data Warehouse Database Administration and Performance Improvements
6. Data Modeling for Data Warehouse Environment
7. Introduction to Erwin Tool

Data Model Overview

1.4: Introduction to Data Models

Fundamentals of Database Systems
A known fact that can be recorded and that have implicit meaning

  A collection of related data with the following implicit properties
A Database represents some aspect of real world, sometimes called the Universe of Discourse (UoD)
A database is a logically coherent collection of data with some inherent meaning
A Database is designed, built, and populated with data for a specific purpose
 Database Management System (DBMS)
A collection of programs that enables users to create and maintain a database
A general purpose software system that facilitates the process of defining, constructing and manipulating database for various applications
Database System
Database and DBMS software together forms a database system

What is Data Modeling?

Data modeling is a technique for exploring the data structures needed to support an organization’s information need.
It would be a conceptual representation or a replica of the data structure required in the database system.
A data model focuses on which data is required and how the data should be organized.
At the conceptual level, the data model is independent of any hardware or software constraints.
 Why Use Data Modeling?
Data model serves as a blueprint for the database system.
Data model functions as an effective communication tool for discussions with the users.
Data Quality
        Data model acts as a bridge from real-world information to database storing relevant data content. 

 What Makes a Good Data Model?
Ensure that every piece of information required for a System is recorded and maintained.
One fact should be recorded only once. Repetition may result in inconsistency and increased storage requirements.
Adherence to Business Rules
The collected data is to be recorded by considering all business rules. It should not violate any rule.
 Data Reusability
Design a data structure to ensure re-usability.
Stability and Flexibility
A model needs to be flexible enough to adopt to new changes without forcing the programmer to re-write the code.
A data model should neatly present the required data in the least possible number of groups or tables.
A model should present the data in a manner understandable to all stakeholders.
A good model is compatible with the existing and future systems.
1.5: Sample ER Data Model

1.6: Challenges, Benefits &  Opportunities
Organizations today have vast quantities of data. Although this data contains information that is useful to the business, it can be extremely difficult to gather and report on this information. There are several key challenges that need to be addressed:
Ø  Discovering, collecting, and transforming data into a single source of record.
Ø  Ensuring that data is relevant and accurate for business reporting.
Ø  Storing historical data in a format that enables fast searches across large amounts of data.
If the data that a business holds can be unlocked and provide meaningful information to business users, there are several opportunities:
Ø   Business users can have access to relevant information to enable them to    make informed decisions.
Ø   Fast queries make data more accessible and historical data enables trends to be identified.
Ø   Data can be assessed at every level -- from an individual purchase to the total sales of a multinational corporation.
Designing data warehouses correctly by using a data model will help meet many of today’s data challenges. Key benefits include:

Ø    Designing structures specifically to enable fast querying for business-centric reporting.
Ø   Ensuring that business requirements are met, and reports are accurate and meaningful.
Ø  Documenting source and target systems correctly to aid development, ensure effective version control, and enhance understanding of the systems.
 1.7: Why data modeling Tool
Why Data modeling tool
Ø   Improves productivity among developers when database designs are divided, shared, and reused
Ø   Establishes corporate modeling standards
Ø  Creates good documentation (metadata) in variety of useful formats
Ø   Ensures consistency, reuse, and integration of enterprise data
Ø   Enable creation the data model in one notation and conversion it to another notation without losing the meaning of the model
Ø    Saves time by accelerating the creation of high-quality, high-performance physical database from logical model
Ø    Conserves resources and improves accuracy by synchronizing model and database
1.8: Requirements for a good data modeling Tool
Requirements for a good data modeling tool
Ø   Diagram Notation
  Both ER & dimensional modeling notation must be available
Ø   Reverse Engineering
   Creation of a model based on the source data in the operational environment as well as from other external sources of data
Ø   Forward Engineering
    Creation of the data definition language (DDL) for the target tables in the data warehouse databases
Ø    Source to Target Mapping
     Linking of source data in the operational systems and external sources to the data in the databases in the target data warehouse
Ø    Data Dictionary or Repository
     Contains metadata that describes the data model

1.9: Overview of Data Model Tools

Data Modeling tools available in market
AllFusion ERwin Data Modeler ( An industry-leading   data modeling solution that can help you create and maintain databases,   data warehouses and enterprise data models
PowerDesigner( Designs and generates the database   schema through true bi-level (conceptual and physical) relational   database modeling supports data warehouse specific modeling   techniques
Oracle Designer (
ER/Studio (
IBM VisualAge DataAtlas (
Popkin System Architect (
CAST DB-Builder (
For Data Warehouse Data Modeling tools, please refer to

In this module, you learned about the following:
Data Model Overview
Prerequisites for this Course
Expectations from this training
Scope of the training
Introduction to Data Models
Fundamentals of database Systems
Definition of a Model
What is Data Modeling
Why use Data Modeling
What Makes a Good Data Model
Sample Data Models
Data Model Tools