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


  1. In any case, at the center, it's information investigation that changed the course of strategizing. data science course in pune

  2. Thank you so much for helping me out to find the Data analytics course in Mumbai Organisations and introducing reputed stalwarts in the industry dealing with data analyzing & assorting it in a structured and precise manner. Keep up the good work. Looking forward to view more from you.


  3. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. I would like to state about something which creates curiosity in knowing more about it. It is a part of our daily routine life which we usually don`t notice in all the things which turns the dreams in to real experiences. Back from the ages, we have been growing and world is evolving at a pace lying on the shoulder of technology. data science course will be a great piece added to the term technology. Cheer for more ideas & innovation which are part of evolution.

  4. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    pmp certification in bangalore

  5. Such a very useful article. I have learn some new information.thanks for sharing.
    data scientist course in mumbai

  6. I recently came across your article and have been reading along. I want to express my admiration of your writing skill and ability to make readers read from the beginning to the end. I would like to read newer posts and to share my thoughts with you.
    Data Analytics Course in Mumbai

  7. Such a very useful Blog. Very interesting to read this article. I have learn some new information.thanks for sharing. know more about

  8. A great website with interesting and unique material what else would you need.
    ExcelR Data Analytics courses

  9. Very nice blog here and thanks for post it.. Keep blogging...
    ExcelR data science training

  10. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.
    data analytics course in hyderabad

  11. I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you!
    ExcelR data science course in mumbai

  12. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well. In fact your creative writing abilities has inspired me to start my own Blog Engine blog now. Really the blogging is spreading its wings rapidly. Your write up is a fine example of it. excelr data science

  13. You completed certain reliable points there. I did a search on the subject and found nearly all persons will agree with your blog.
    ExcelR Courses in Business Analytics

  14. I have to search sites with relevant information on given topic and provide them to teacher our opinion and the article.

    business analytics course

    data analytics courses

    data science interview questions

    data science course in mumbai

  15. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!

    artificial Intelligence course

    machine learning courses in mumbai

  16. The information provided on the site is informative. Looking forward more such blogs. Thanks for sharing .
    Artificial Inteligence course in Nashik
    AI Course in Nashik

  17. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    pmp certification