Advanced Data & Dimensional Modeling Course and Certification

Course Schedule and Upcoming Batches


Self Paced Course
Classes Start
Duration
Price (INR)
Price (USD)
Anytime
6 Weeks
12,000
Enroll
250
Enroll
Original prices are reduced for limited period. Extra 5% Discount
Classes Start
Duration
Days
Time (IST)
Price (INR)
Price (USD)
To be confirmed
6 Weeks
Saturday
17,000
16,150
Enroll
350
332.5
Enroll


We are running a special Combo Offer for Self Paced Courses on Big Data and Data and Dimensional Modeling. Click here to know more. Or you can also call our sales representative.

*Service Tax rate is revised to 14.5% from 15 November 2015. This includes S.B. cess of 0.5%.

In case you want to Register now and take up this course later, you can register by clicking on " Register" section.

If you have any query, you can call/mail or send your query by clicking on " Send a Query" section.


Sample Class Video

Check out the Introduction Class video here:

About the Course


Data Modeling is an important aspect of any Database building exercise, whether this database is used for e-commerce application, retail application, telecom application or any software product which uses the data. Without a proper data modeling process, things may go out of hand in the later stages when application starts adding more functionality. A good data model provides a robust and future-proof mechanism for data management.

Dimensional Modeling is at the core of Data warehouse development as it dictates the overall architecture of Data warehouse database and helps in identifying the subject areas and a common bus which would connect all subject areas. This will give a clear understanding of how to deal with Data warehouse related issues like performance, data quality etc.

This course is designed to give a learner the end to end knowledge on Data Modeling for general database applications and Dimensional Modeling for Data warehouse development.


Course Objective

After completion of this course, you will be able to:

  1. Learn basics of Data Modeling, Normalization and Entity Relationship framework.
  2. Learn why the concept of Subtypes/Supertypes is important for Data Modeling.
  3. Learn about Conceptual Data Model, Logical Database Design and Physical Database Design.
  4. Learn ERwin and some of the major features of ERwin tool.
  5. Learn about building blocks of Dimensional Modeling.
  6. Learn in-depth about Slowly Changing Dimensions.
  7. Learn about Snowflake, Outriggers, Hierarchy and Bridge Table.
  8. Learn how to handle the Late-Arriving Dimension Records.
  9. Learn about Transactions, Snapshots, and Accumulating Snapshots fact tables.
  10. Learn about Factless Fact Tables.
  11. Learn how to Design the Dimensional Model.
  12. Learn some important tips to deal with Dates, Indexing and Keys..
  13. Learn how is it important to have a separate fact table for each process.
  14. Learn in-depth about Conformed Dimensions.
  15. Learn about Hierarchies in detail.
  16. Learn about Multi-Valued Dimensions.
  17. Learn how to make dimensional design more intelligent by using a Core Star and Custom Star concepts.
  18. Learn about Derived Schemas and Aggregates.
  19. Carry out an end to end Data Modeling project by using ERwin tool.
  20. Learn how data model and dimensional models are implemented in Retail and Telecom domains as we will take up case studies from these domains.


Why is it the right course for you?

If you want to get deep knowledge on Data Modeling and Dimensional Modeling then this is the right course for you. This course is particularly suited for BI and Datawarehousing professional as it would open up the opportunities in Data design.


Pre-requisites

You need to be familiar with Databases and should have some ideas about how to handle data manipulation in Database.

Course Curriculum


1) Introduction to Data Modeling

Learning Objectives

Introduction of Data Modeling and Data Models.

Topics

Data Modeling, Importance and Characteristics of a Good Data Model, Types of Data Models.


2) Normalization

Learning Objectives

Learn how important is normalization. Learn various forms of Normalization.

Topics

First Normal Form, Second Normal Form, Third Normal Form, Boyce-Code Normal Form (BCNF), Fourth Normal Form, Fifth Normal Form.


3) The Entity Relationship (E-R)

Learning Objectives

Learn about Entity Relationship framework.

Topics

Symbols for E-R, Entity, Dependent and Independent Entity Classes, Examples of relationships, One-to-One Relationships, Self-Referencing Relationships, Relationships Involving Three or More Entity Classes, Transferability, Relationships with Attributes, Roles, The Weak Entity Concept.


4) Subtypes and Supertypes

Learning Objectives

Learn why the concept of Subtypes/Supertypes is important for Data Modeling.

Topics

Non-overlapping and Exhaustive, Overlapping Subtypes and Roles, Benefits of Using Subtypes and Supertypes.


5) Conceptual Data Model

Learning Objectives

Learn about Conceptual Data Model. This is the first step in building a Data Model.

Topics

Using Pattern/ Generic Model, Bottom-Up Modeling, Top-Down Modeling, Which is better- Top Down or Bottom up?, Data Modeling based on Subject Area, Hierarchies, Networks, and Chains.


6) Logical Database Design

Learning Objectives

Learn about Logical Data Model. This is the second step in building a Data Model.

Topics

Introduction, Overview of the Transformations, Table Specification, The Standard Transformation, Exclusion of Entity Classes from the Database, Classification of Entity Classes, Many-to-Many Relationship Implementation, Relationships Involving More than Two Entity Classes, Supertype/Subtype Implementation, Basic Column Definition , Attribute Implementation, Category Attribute Implementation, Derivable Attributes, Attributes of Relationships, Complex & Multi-valued Attributes,Column Datatypes,Column Nullability,Primary Key Specification,Foreign Key Specification,One-to-Many Relationship Implementation,One-to-One Relationship Implementation,Derivable Relationships,Optional Relationships,Table and Column Names.


7) Physical Database Design

Learning Objectives

Learn about Physical Data Model. This is the final step in building a Data Model.

Topics

Inputs required for Physical Data Model,PDM Design Options,Indexes, Index Properties,Types of Indexes,Data Storage,Table Space Usage,Free Space,Locking, Views,Views of Supertypes and Subtypes ,Inclusion of Derived Attributes in Views, Views of Split and Merged Tables.


8) ERwin Tool

Learning Objectives

Learn ERwin. Learn basics and some of the advanced features of ERwin.

Topics

We will teach some of the basic and advanced features of ERwin. We will give a project assignment on ERwin, which is to create a Datamart data model on ERwin and execute it on a database.


9) Introducing Dimensional Modeling

Learning Objectives

Introduction of Dimensional Modeling.

Topics

Dimensional Modeling, Comparison of Dimensional Modeling with E-R Modeling or Normalized Modeling, Benefits of Dimensional Modeling.


10) Dimensional Modeling Building Blocks

Learning Objectives

Learn about all basic building blocks of Dimensional Modeling.

Topics

Fact Tables,Fact Table Keys,Fact Table Granularity,Dimension Tables,Dimension Features ,The Basic Structure of a Dimension,Dimension Table Keys ,Conformed Dimensions ,Grain ,Sparsity,Degenerate Dimensions,Role-Playing Dimensions ,Junk Dimensions ,Big Dimensions ,Small Dimensions ,Dimension co-relation,Date and Time Dimensions , Bus Architecture, Common Matrix definition Mistakes,Few tips while defining Matrix , Dimensional Design Process.


11) Slowly Changing Dimensions

Learning Objectives

Learn why Slowly Changing Dimensions is an important concept in Data warehousing and how to deal with various types of Slowly Changing Dimensions.

Topics

Slowly Changing Dimensions,Type 1: Overwrite the Dimension Attribute,Type 2: Add a New Dimension Row,Type 3, Add a New Dimension Attribute,Hybrid Slowly Changing Dimension Techniques,Conflicting Requirements, Frozen Attributes,Time-Stamped Dimensions,Mini-Dimensions for large dimension tables.


12) Snowflake, Outriggers, Hierarchy and Bridge Table

Learning Objectives

Learn about Snowflake, Outriggers, Hierarchy and Bridge Table.

Topics

Snowflake,Outrigger ,Eliminating Repeating Groups with Outriggers,Outriggers and Slow Change Processing, Hierarchies ,Fixed Hierarchies ,Variable Depth Hierarchies,Many-Valued Dimensions with Bridge Tables,Time-Varying Bridge Tables.


13) Late-Arriving Dimension Records and Correcting Bad Data

Learning Objectives

Learn how to handle when fact data arrives first and dimension data arrives later. This can create an integrity issue if not dealt with properly.

Topics

Dimension Data Value arrives after Fact Value is loaded,A Correction in Dimension Data Value arrives late.


14) More on Facts

Learning Objectives

Learn in detail about Fact tables and how to deal with complex types of Fact tables.

Topics

Three Fundamental Grains,Transaction Fact Tables, Periodic Snapshot Fact Tables,Accumulating Snapshot Fact Tables,Facts of Differing Granularity and Allocation ,Multiple Currencies and Units of Measure ,Factless Fact Tables ,Consolidated Fact Tables.


15) Detail on Transactions, Snapshots, and Accumulating Snapshots

Learning Objectives

Learn about Transactions, Snapshots, and Accumulating Snapshots fact tables.

Topics

Transaction Fact Tables,Describing events ,Properties of Transaction Fact Tables, Grain of Transaction Fact Tables,Transaction Fact Tables Are Sparse, Transaction Fact Tables Contain Additive Facts,Snapshot Fact Tables,Sampling Status with a Snapshot, Semi-Additivity, Snapshot Considerations, Pairing Transaction and Snapshot Designs,Additional Facts,Period-to-Date Measurements,Snapshots and Slow Changes,Accumulating Snapshot Fact Tables,Challenge: Studying Elapsed Time between Events,Tracking Process Steps in a Transaction Fact Table,Where the Transaction Model Falls Short ,Begin and End Dates Are Not the Answer,The Accumulating Snapshot,Life Cycle of a Row,Accumulating Snapshot Considerations , Pairing Transaction and Accumulating Snapshot Designs ,Focus on Key Status Milestones , Multi-source Process Information ,Nonlinear Processes, Slow Changes.


16) Details of Factless Fact Tables

Learning Objectives

Learn about Factless Fact table and why is it important for Datawarehousing.

Topics

Factless fact table for Events,Adding a Fact,F actless fact table for Conditions, Coverage, or Eligibility,Slowly Changing Dimensions and Conditions.


17) Designing the Dimensional Model

Learning Objectives

Learn how to create a Dimensional Model.

Topics

Prerequisite,The Team,The Requirements, Modeling Tools,Naming Conventions ,Provision for Source Data Research and Profiling,Obtain Facilities, Supplies and Time booking,Four-Step Modeling Process,Design the Dimensional Model,Build the High Level Dimensional Model,Conduct the Initial Design Session,Document the High Level Model Diagram,Identify the Attributes and Metrics,Develop the Detailed Dimensional Model,Identify the Data Sources,Understand Candidate Data Sources,Profile and Select the Data Sources,Establish Conformed Dimensions,Identify Base Facts and Derived Facts,Document the Detailed Table Designs, Update the Bus Matrix , Review and Validate the Model,Finalize the Design Documentation.


18) Some Important topics

Learning Objectives

In this module, you will learn about some important topics to deal with Dates, Indexing and Keys.

Topics

Start Date/End Data versus Effective Date/Expiry Date,Security Code ,Indexing and Partitioning ,Primary Index/ Primary Key,Secondary Indexes,Surrogate Keys and Natural Keys,Cubes,Cubes and the Data Warehouse.


19) A Fact Table for Each Process

Learning Objectives

Learn how is it important to have a separate fact table for each process and what complications can occur if this is not followed.

Topics

Example of Events occurring at different times,Example of Facts having different Grains, How to compare and analyze facts from multiple fact tables?,Drilling Across,Drill-Across Procedure.


20) Conformed Dimensions

Learning Objectives

Learn why Conformed Dimensions is a base to create the Data warehouse. Learn about various types of Conformed Dimensions.

Topics

Types of Dimensional Conformance,Shared Dimension Tables,Conformed Rollups,Conforming Degenerate Dimensions,Overlapping Dimensions.


21) More on Dimension Tables

Learning Objectives

Learn in-depth details of Dimension Tables.

Topics

The Browsability Test,Grouping Dimensions into Tables,Grouping Dimensions Based on Affinity ,Breaking Up Large Dimensions ,Splitting Dimension Tables Arbitrarily ,Drawbacks to Arbitrary Separation ,Alternatives to Split Dimensions ,Dimension Roles and Aliases,Problems created by NULL and their resolution ,Some other Special-Cases,Invalid Data ,Late-Arriving Data,Future Events,Behavioral ,Past Association with another Dimension ,Historic Fact,Categorizing Facts.


22) Hierarchies

Learning Objectives

Learn why Hierarchies are an important concept for Data warehousing. Learn various forms of Hierarchies.

Topics

Drilling ,Attribute Hierarchies and Drilling ,Drilling Within an Attribute Hierarchy,Other Ways to Drill,Multiple Hierarchies in a Dimension,Crossing between Dimensions,Instance Hierarchies ,Documenting Attribute Hierarchies ,Cube Design and Management.


23) Multi-Valued Dimensions and Bridges

Learning Objectives

Learn about Multi-Valued Dimensions and Bridges and why should they be managed properly to avoid any kind of double counting.

Topics

Standard One-to-Many Relationships,Simple Solution,Using a Bridge for Multi-Valued Dimensions , Bridges and Double-Counting,When Double-Counting Is Wanted,Adding an Allocation Factor,Supplementing the Bridge with a Primary Member , Impacts of Bridging, Slow Changes ,Resolving the Many-to-Many Relationship,Multi-Valued Attributes,Simplifying the Multi-Valued Attribute,Using an Attribute Bridge,Double-Counting,Primary Member and Hiding the Bridge ,The Impact of Changes,Resolving the Many-to-Many Relationship.


24) Recursive Hierarchies and Bridges

Learning Objectives

Learn about Recursive Hierarchies and Bridges. Recursive Hierarchies can be useful in creating generic Hierarchies.

Topics

The Reporting Challenge,Flattening a Recursive Hierarchy,Drawbacks of Flattening,The Hierarchy Bridge,Looking Down, Looking Up,Avoiding Double-Counting,Hiding the Bridge from Novice Users,Resolving the Many-to-Many Relationship,Looking Down Without a Many-to-Many Relationship, Looking Up Without a Many-to-Many Relationship,Changes and the Hierarchy Bridge,Type 1 Changes in the Dimension or Bridge, Type 1 Change to the Dimension,Type 1 Change to the Hierarchy,Type 2 Changes to the Dimension,Type 2 Changes to the Hierarchy , The Reason for the Ripple Effect,Multiple Hierarchies.


25) Type-Specific Stars

Learning Objectives

Learn how to make dimensional design more intelligent by using a Core Star and Custom Star concepts.

Topics

The Single-Star Approach, Drawbacks to the Single-Star Approach, Core and Custom Stars, Slowly Changing Dimensions, Core and Custom Fact Tables, The Same Facts for All Types ,Type-Specific Facts, Other Considerations, Overlapping Custom Dimensions, The Outrigger Alternative, Using Generic Attributes.


26) Derived Schemas

Learning Objectives

Learn about Derived Schemas. Derived Schemas pre-compute the results and store them separately to improve the performance.

Topics

Uses for Derived Schemas,Query Performance,Report Complexity, Data Subset,Use of Cubes,The Cost of Derived Schemas,Types of Derived Schemas,The Merged Fact Table ,Precomputed Drill-Across Results,Advantage of Merged Fact Table ,The Pivoted Fact Table,The Need to Pivot Data,The Pivoted Advantage,Drawbacks to Pivoting,The Sliced Fact Table,Creating Slices of a Star ,Uses for Sliced Fact Tables,Set Operation Fact Tables.


27) Aggregates

Learning Objectives

Aggregate is a very important concept of Data warehousing. It is used to improve the query performance.

Topics

Summarizing Base Data,Conformance,Using Aggregates,Writing (or Rewriting) Queries, Determining Which Star to Query,Loading Aggregates ,The Source of an Aggregate,Type 1 Changes,Type 2 Changes ,Aggregate Navigation, The Aggregate Navigator,Other Potential Benefits.


28) Project

Learning Objectives

In this module you will be working on a business problem where you need to design a data model with the help of ERwin Data Modeler tool. You will need to work on Logical, Conceptual and Physical data models. You will also learn how to build a Data model in ERwin and convert that to DDL code automatically.

Topics

Data Modeling project by using ERwin Data Modeler Community Edition.

Project Assessment

Project assessment will be done by the Trainer and a grade will awarded based on the performance on project. Grading will be done on the scale of 1 to 5, 5 being the outstanding performance and 1 being the Unsatisfactory performance. Scale is as below:

  1. Outstanding
  2. Very Good
  3. Good
  4. Average
  5. Unsatisfactory


29) Case Study: Retail

Learning Objectives

A Retail Data Model will be discussed during this case study.


30) Case Study: Telecom

Learning Objectives

A Telecom Data Model will be discussed during this case study.



Course Duration


Online Classes : 30 Hours

There will be six instructor led online classes of five hours each in interactive course. Or total 30 hours recorded video from class will be provided in case if it is a Self paced course.

Project Work : 25 Hours

Project work will be given to learners to be completed during the course duration.

Course Work : 40 Hours

Study material of 40 hours or more will be given as a course work to be completed. Total five study guides will be provided.

Exam : 1 Hour

A proctored exam of 1 hour will be conducted for final assessment.

Course Features


  • Live Classes

    Live Instructor Led classes from Industry Experts. Option to choose from Online or Classroom Lectures. Case studies from real life projects.

  • Ongoing Research

    Research team works hard to bring out the latest innovations and best practices of course subjects. Courses are evolving continuously; they never get stale.

  • Be More Productive

    Get work related tips and perform your work more efficiently. Once you know the tricks of trade, you become more productive.

  • Industry Experts

    Classes are conducted by Industry Experts. Learners gain from world class curriculum and extensive experiences of Trainers as well.

  • Award of PDUs

    We award you 30 PDUs which can be used towards PMI certifications' requirements. It is completely free but you need to raise a separate request for it.

  • Learning Material

    Learners get unlimited access to online and offline materials. Don't worry if you miss any class, we will be providing you a repeat class online or offline.

  • Learning Support

    Learners are encouraged to ask online and offline questions. Our team of Trainers makes it a priority to answer these questions.

  • Money-back Guarantee

    If you are not satisfied with the quality then take full refund within the seven days of first class. No questions will be asked.

  • Easy Reschedule

    Have you missed a class? Don't worry !!, You can watch the class videos or you can also request for a reschedule. We will invite you for next class for Free.

  • Career Support

    Learner's resume is reviewed and a one-to-one discussion is arranged with an expert to advice on career roadmap and job opportunities. For details, visit Career Centre

Certification


Certificate of Participation

A certificate of participation will be awarded after participating in the training program. The name of certificate is "BIWHIZ CERTIFICATE OF PARTICIPATION ON DATA & DIMENSIONAL MODELING".


Certification after Completion

A certification will be issued after assessment of assignments, course work requirements, projects and a written test as per the course curriculum. After successfully completing all the requirements and passing the written test, a certification will be issued. The name of the certification is "BIWHIZ CERTIFICATION ON DATA & DIMENSIONAL MODELING".


Certificate Issuing Authority

BIWHIZ is part of the company "Business Intelligence Consultant and Services LLP". Certificates are issued by "Business Intelligence Consultant and Services LLP". This is a registered company with Ministry of Corporate Affairs, Government of India.

Sample Exam Questions

This sample is only for illustrative purpose and only basic level questions are displayed here. Actual exam questions may be completely different with different format as well. Please contact your coordinator to know more about prevailing exam format.


Normalization is must?

Choose one.

  1. Yes
  2. No
  3. Yes for Non-DWH Database
  4. Yes for DWH Database

Grain is important because?

Choose one.

  1. It has to be first activity in DWH
  2. Represents the level of detail
  3. It is not so important
  4. It creates a de-normalized databse

How to track student attendance?

Choose one.

  1. By using Fact table
  2. By using Snapshot table
  3. By using Factless fact table
  4. Create a standalone table

Custom Star is?

Choose one.

  1. Customized star for each Data Mart
  2. Star specifict to a particular need
  3. Having no dimensions
  4. Having only one fact table

Conformed Dimension is?

Choose one.

  1. Dimensions shared by all Data Marts
  2. Dimensions approved by top management
  3. Dimensions which comply to set standards
  4. Used by authorized person

Type 2 is the best method for Slowly changing dimension?

Choose one.

  1. Yes
  2. No
  3. Can't Say
  4. Depends on the situation

Frequently Asked Questions


  1. What is the difference between Self paced course and Instructor led course?

    In self paced course you will get all recorded videos from a previous instructor led interactive course. Advantage of self paced course is that you can watch these videos anytime and complete your course as per your convenience. For any doubt clarification or any question to trainer, a one to one online meeting will be arranged. In instructor led online course, trainer will conduct all these classes in online meeting mode and you can ask your question at the time of class itself.

  2. What support is provided if I take Self paced course?

    A one on one online meeting of three hours between you and trainer will be setup so that you can ask your questions and clarify any doubts. You can ask unlimited questions through emails and all questions will be answered by the trainer. You will be given all course material on first day itself.

  3. What is the advantage of Instructor led online course?

    This is a live class and you can ask your questions in real time.

  4. Is it necessary to have any prior knowledge of Databases before starting this course?

    It's good if you have basic knowledge about Database and SQL, if not then you can take up the Advanced SQL course. However it is not mandatory to have any prior knowledge; having said that you would need to put up some extra effort to understand the Modeling concepts in the beginning. Once you are ok with basic concepts of Data Modeling then you move forward without any difficulty.

  5. Is this course for me? What value would it add to my career?

    If you are planning to take up the Data Designer or Data Modeler role then this is the right course for you. This course is a perfect fit for people who are already in Business Intelligence/Data Warehousing domain. This will give them good knowledge about Dimensional Modeling and then they can start working in Data Design phase of a Datawarehousing project.

  6. Where would I be developing and running Data Modeling Project?

    We will help you in installing Erwin tool (Community edition). We would also supply you a dataset. We will explain you how to carry on project activities on your personal computer.

  7. What is the relationship between Data Modeling and Dimensional Modeling?

    Data Modeling is related to database development for any IT application and Dimensional Modeling is related to Datawarehouse/BI System development.

  8. I guess I need to have very fast Internet connection to be able to attend these courses?

    Generally 1 MB connection is sufficient however lower bandwidth connections are also working fine.

  9. Can I request for another class if I miss it?

    You can watch the recorded session video or you can also attend the same class in next batch.

  10. Why Certification is included in the course?

    This is to make sure that a learner has understood the course content and BIWHIZ has verified the knowledge level of the Learner. Certification is the proof that you have achieved a certain level of expertise on course and any authorized organization can verify that with us.

  11. What if I am not able to clear the Certification exam in first attempt?

    You can take extra attempts for free.

  12. What is the learning support and would it be available after completing the course as well?

    You can raise your queries and doubts during and post training period. All queries will be resolved by Data/Dimensional Modeling experts.

  13. Do you help in career related issues as well, like reviewing resume, mentoring for my career growth?

    Yes, we have a panel of Data/Dimensional Modeling experts who will guide and mentor you; please check Career Centre for more details.

  14. Will you sell my data to Recruitment/marketing companies or will you use it for Recruitment or any other activities not related with Training?

    NO, Never. Your details are highly confidential and safe with us. But If you are willing to accept any such calls then you can inform us in advance with a specific need and we will contact only for those specific requirements. For a detailed privacy policy please check Terms, Conditions & Privacy Policy.

Register Here

Please register for this course here. Even if you are not ready to Enroll now, you can register now and get an intimation about our next batch whenever it is starting.


Please Fill Your Details



Send a Query

You got a query for us? Please use the Form below to send your query. We will get back to you soon. Have a great day !!


Please Fill Your Details & Query