Training

 

 

Popular Courses

Browse Our Free Resources

  • whitepapers
  • whitepapers
  • webinars
  • blogs

Our Locations

Training Centres

Vancouver, BC
Calgary, AB
Edmonton, AB
Toronto, ON
Ottawa, ON
Montreal, QC
Hunt Valley
Columbia

locations map

Calgary

550 6th Av SW
Suite 475
Calgary, AB
T2P 0S2

Toronto

821A Bloor Street West
Toronto, ON
M6G 1M1

Vancouver

409 Granville St
Suite 902
Vancouver, BC
V6C 1T2

U.S. Office

436 York Road
Suite 1
Jenkintown, PA
19046

Other Locations

Dallas, TX
Miami, FL

Home > Training > Data Warehousing > Dimensional Modeling Workshop Training

Dimensional Modeling Workshop Training

Course#: WA1818

This 3-day course introduces students to best industry practices for designing data warehouse data structures and databases.

What you will learn
 

After completing this course, the student should be able to:

 

  • Understand and apply the concepts and principles of data warehousing
  • Understand and apply basic data warehouse architectures and development processes
  • Understand and apply basic data warehouse data design techniques
  • Ensure that business requirements are identified and included in your design
  • Gather, organize and prioritize business requirements
  • Choose between an appropriately normalized data model or a more dimensional model
  • Understand how and when to use aggregation
  • Understand and apply different modeling techniques for different data warehouse structures
 
Audience
 
  • Developers and administrators involved in data warehousing
  • Business and technical data warehouse team members
 
Prerequisites
 
  • Our “Introduction To Data Warehousing” or equivalent knowledge
  • An understanding of data modeling, especially entity-relationship modeling
 
Duration
  3 days

Outline of WA1818 Dimensional Modeling Workshop Training

1. Introduction to Data Warehousing

  • Scope and levels of modeling
  • Kinds of data
  • The framework for data modeling
  • Challenges in data management
  • Major characteristics of data warehouse
  • Types of data and data models:

2. Introduction to Dimensional Modeling

  • Definition and components
  • Entity, Attribute, Relationship
  • Keys, Derived Data
  • Levels of data models and rules by level
  • The high level and detailed data model
  • Facts and dimensions
  • Normalization

3. Building the Data Warehouse Model

  • Operational vs. informational data
  • A data controlled environment
  • Progression of data in a data controlled environment

4. Levels of Data in the Enterprise

  • Four types of data and systems
  • The warehouse and decision data model
  • Sources of warehouse and decision data
  • Definition and rules
  • The corporate model
  • The business area model

5. Information Gathering

  • Facilitated sessions
  • Interviews
  • Information gathering techniques

6. Building the Central DW Model

  • Remove pure production data
  • Support time and history
  • Add aggregates
  • Find the right granularity of data
  • Create fact tables and dimensions
  • Merge like data from different tables
  • Create arrays of data
  • Separate data based on its stability
  • Embed relationships in the data
  • Add external data

7. Modeling Aggregates

  • Types of aggregate data
  • Overall process for aggregating
  • Trade-offs with aggregate data

8. Modeling Time and History

  • Short Term And Long Term View
  • Four ways of handling time and date
  • History vs. time-series data
  • Capturing business changes
  • Importance of business time dimension

9. Building Data Marts

  • Types of data marts
  • Trade-offs with data marts
  • Definition of fact tables and dimensions
  • Dimensions and dimension hierarchies
  • Creating multidimensional arrays
  • Corporate reference tables
  • The star schema
  • The snowflake schema

10. Optimizing the Data Warehouse Design

  • Safe compromises to data
  • Merge like tables (eliminate 1:1)
  • Create arrays of data (violate 1NF)
  • Split data based on stability and usage
  • Add indices
  • Encode-decode data
  • Aggressive compromises to data
  • Store derived data
  • Summarize data
  • Add redundant data or relationship
  • Add partial or transitive dependencies (violate 2NF or 3NF)
  • Critical factors in data design
  • Number of occurrences of each table
  • The ratio of one table to another
  • The queries that use the data
  • The data accesses made by each query
  • The load factor for each query (number of times performed).
  • The steps of optimization

11. Data Warehouse Technology

  • Categories of warehouse tools
  • Review of major products

12. Summary and Conclusion

  • Selected warehouse projects
  • Critical Success Factors

13. Case Studies

  • Selected mini-exercises
  • Complete group case study (moderately sized)
  • Complete individual case study (large)

14. Glossary

We regularly offer classes in these and other cities. Atlanta, Austin, Baltimore, Calgary, Chicago, Cleveland, Dallas, Denver, Detroit, Houston, Jacksonville, Miami, Montreal, New York City, Orlando, Ottawa, Philadelphia, Phoenix, Pittsburgh, Seattle, Toronto, Vancouver, Washington DC.
*Your name:

*Your e-mail:

*Phone:

*Company name:

Additional notes:

We have received your message. A sales representative will contact you soon.

Thank you!.

more details
buy this course

Register for a courseware sample

It's simple, and free.

 

Thank You!

You will receive an email shortly containing a link to download the requested sample of the labs for this course.