Course Schedule and Upcoming Batches
| Self Paced Course | |||||
|---|---|---|---|---|---|
Enroll | Enroll |
||||
| Original prices are reduced for limited period. Extra 5% Discount | |||||
|---|---|---|---|---|---|
16,150 Enroll | 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:
- Learn basics of Data Modeling, Normalization and Entity Relationship framework.
- Learn why the concept of Subtypes/Supertypes is important for Data Modeling.
- Learn about Conceptual Data Model, Logical Database Design and Physical Database Design.
- Learn ERwin and some of the major features of ERwin tool.
- Learn about building blocks of Dimensional Modeling.
- Learn in-depth about Slowly Changing Dimensions.
- Learn about Snowflake, Outriggers, Hierarchy and Bridge Table.
- Learn how to handle the Late-Arriving Dimension Records.
- Learn about Transactions, Snapshots, and Accumulating Snapshots fact tables.
- Learn about Factless Fact Tables.
- Learn how to Design the Dimensional Model.
- Learn some important tips to deal with Dates, Indexing and Keys..
- Learn how is it important to have a separate fact table for each process.
- Learn in-depth about Conformed Dimensions.
- Learn about Hierarchies in detail.
- Learn about Multi-Valued Dimensions.
- Learn how to make dimensional design more intelligent by using a Core Star and Custom Star concepts.
- Learn about Derived Schemas and Aggregates.
- Carry out an end to end Data Modeling project by using ERwin tool.
- 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:
- Outstanding
- Very Good
- Good
- Average
- 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 of "Classroom Course" then take full refund within the seven days of first class. However there is NO REFUND for "Self Paced Course" because we deliver all the training material at once in soft copy format and we cannot UNDO it. Please buy Self-Paced course only when you are 100% convinced about the quality.
-
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?
- Yes
- No
- Yes for Non-DWH Database
- Yes for DWH Database
Grain is important because?
- It has to be first activity in DWH
- Represents the level of detail
- It is not so important
- It creates a de-normalized databse
How to track student attendance?
- By using Fact table
- By using Snapshot table
- By using Factless fact table
- Create a standalone table
Custom Star is?
- Customized star for each Data Mart
- Star specifict to a particular need
- Having no dimensions
- Having only one fact table
Conformed Dimension is?
- Dimensions shared by all Data Marts
- Dimensions approved by top management
- Dimensions which comply to set standards
- Used by authorized person
Type 2 is the best method for Slowly changing dimension?
- Yes
- No
- Can't Say
- Depends on the situation
Frequently Asked Questions
-
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.
-
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.
-
What is the advantage of Instructor led online course?
This is a live class and you can ask your questions in real time.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
What if I am not able to clear the Certification exam in first attempt?
You can take extra attempts for free.
-
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.
-
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.
-
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.