Data Modelling

Modelling: Operational, Data Warehousing & Data Marts

This course is organised by EC Integrators in partnership with Genesee Academy (

Effective Data Modellingis the cornerstone to building business intelligence and data warehouse applications that provide significant business value.

This course teaches data modelling and covers the main data modelling techniques related to each of these three major architectural layers. In this class you will learn data modelling using the normalized (3NF) modelling approach for operational systems, the ensemble data vault modelling approach for the data warehouse, and the dimensional (Star Schema) modelling approach for data marts.



17-19 November 2014
Melbourne CBD
$2,950 excluding GST

Early Bird Registration by 20 Oct 2014: $2,600 excluding GST

This course covers the core principles of data modelling through lectures and hands-on labs and exercises. Providing a solid overview of current techniques for modelling operational systems, data warehouses, and data marts.

In covering these areas, the course considers data modelling and design using normalised data modelling 3rd Normal Form for Operational Systems, Ensemble Data Vault modelling for the Data Warehouse, and Dimensional modelling (Star Schema) for Data Marts.

3NF Modelling. The course will start with the core fundamentals of database design including identifying the core entities and relationships, creating logical model designs, defining attributes and key structures, and developing entity relationship diagrams (ERDs).

Within the scope of this modelling approach, the lessons will cover business rules, normalization, validation rules, reference tables, key constraints, identifying and non-identifying relationships, recursive relationships, redundancy, subtypes and supertypes, and relationship cardinality. Students will become comfortable with the core rules and best practices approach to 3NF modelling.

Data Vault Modelling. The training will continue with the fundamentals of data warehouse modelling for the enterprise data warehouse (EDW). Within this section the core concepts of data warehousing are presented including the focus on integrated, non-volatile, time-variant and subject oriented data. The Data Vault modelling approach is presented including business keys (Hubs), relationships (Links) and context/history (Satellites). The course continues with core business concepts, ensembles, unified decomposition, concept constellations, and natural business relationships. The course considers how to model your enterprise data warehouse, modeling techniques for agility, operational support, auditability, and enterprise data integration. Lessons, exercises and labs are focused on best practices for architecting and modelling your data warehouse for long term success.

Dimensional Modelling. Next the training will cover dimensional data modelling based on current best practice interpretations of the Kimball Star Schema dimensional modelling approach. First lessons will begin with the fundamentals of dimensional modelling including the purpose and structure of Facts and Dimensions, denormalisation, the concept of Slowly Changing Dimensions (SCDs) and the main Dimension Types (Type 2 and also covering Type 0, 1, 3, 4 and 6), and then the modelling and design of solid dimensions and encouraged forms of Star Schemas. The course continues with defining and designing Snow Flake models, the encouraged and acceptable practices for deploying these concepts. Lastly, the course will cover physical data model considerations and DW/BI deployment topics.

Target audience

The class is perfect for DWBI management, project managers, all forms of modelers and architects including Information Modelers, Data Modelers, Data Architects, Information Architects, and Business Analysts. Also for Business Intelligence and Data Warehousing managers, Master Data Management (MDM) professionals, designers, project leads and project owners, DW DBAs, Data Mart designers, Integration Experts, ETL Developers, and Functional Area Representatives. 

Course Presenter Profile
Hans Hultgren

Hans Patrik Hultgren is a Data Warehousing and Business Intelligence educator, author, trainer, and advisor.

Hans is currently working on Business Intelligence and Enterprise Data Warehousing (EDW) with a focus on modelling and architecture including Ensemble and Data Vault modelling

Hans works internationally primarily in Nordics and EU (based in Stockholm), USA and Canada (based in Golden, CO) and down under in Sydney.

Hans was with the University of Denver for 20 years and now continues an education focus with hybrid course delivery including e-Learning components. Courses delivered through Genesee Academy use blended and On-Demand DWBI training videos at

Recently published book Modeling the Agile Data Warehouse with Data Vault which is available on Amazon websites.

Course Structure

The classroom days are scheduled in a location that includes table space for each student to work, white boards, flip charts, and a presentation projector used during the lectures. The classroom days are engaging and highly interactive with the students. These days include lectures, labs and exercises.

The classroom course runs 09:00 to 17:00 each day. Students arrive for coffee and introductions beginning at 08:30. Class starts promptly at 09:00.

Day 1

  • Introduction to Data Management

  • Data Modeling Primer

  • Database design principles

  • Data modeling approaches & techniques

  • The Operational ERD

  • Exercises

  • Modeling Lab: 3NF - Lab work, presentations and discussions

  • Moving to the Dimensional Model

  • The Presentation Layer, DWBI

Day 2

  • Understanding the Star Schema

  • Exercises

  • Modeling Lab: Star Schema - Lab work completed in groupse

  • Dimensional Modeling and the DW

  • The EDW & the Federated DW

  • Type 2 Dimensions & the Snow Flake

  • Moving to the EDW

  • Data Modeling for the EDW

  • EDW requirements and constraints

Day 3

  • Optimized techniques for specific criteria

  • Ensemble & Data Vault Modeling - Hubs, Links and Satellites

  • Exercises

  • Modeling Lab: Ensemble Data Vault

  • Data Vault Lab Presentations

  • Presentations and Discussions

  • Data Modeling – putting it all together

  • Applying approaches in practice - Pros and Cons

  • Workbook exercises

  • Q&A / In Class Assessment