Course description: Advanced Data Warehousing Workshop: Multi-Dimensional Modeling

This is an advanced workshop covering the subject of multi-dimensional modeling (MDDM), consisting of two parts. Part 1 presents all of the basic MDDM concepts, terminology, and techniques, including guidelines, heuristics and metrics where appropriate. Part 2 of the workshop further extends the coverage of MDDM modeling, by investigating a well-chosen set of advanced modeling techniques, which are representative of situations where the models have to be built for large (corporate) data warehouses or broad-scope data marts.

In Part 1, you will be able to produce Star- and Snowflake models, corresponding to a given set of end-user requirements, basically expressed as queries and hypothesis for which the DW should provide an answer. By taking part in this part of the course, you will also understand the differences and the positioning/mapping between E/R modelling, Data Warehouse modeling, and multi-dimensional modeling. The advanced MDDM techniques in the Part 2 workshop have several major advantages, up and above the learning of the techniques themselves: Studying advanced modeling issues and their solutions is highly advantageous for understanding the positioning of Star- and Snowflake modeling in the company's overall (corporate) data architecture. Similarly, advanced MDDM modeling techniques are very instrumental for studying the positioning of Temporal E/R models (in most cases, the base technique for corporate warehouses) versus dimensional models and for understanding when and how BOTH approaches may be combined.

For a detailed survey of the techniques covered by both parts of the workshop, please refer to the topical course description and agenda available below.

The workshop is designed as a highly interactive workshop. In the first part, approximately half of the time spent on the subject is in applying the techniques in exercises which are part of a consistently developed, non-trivial case study. This case study is most likely to be a "Supply and Inventory Management" warehouse development project.

The second part of the workshop consists of individually developed case study investigations of advanced MDDM modeling techniques. In this sense, the workshop becomes easily adaptable to specific customer contexts.

The workshop is designed so that it can be used in a standard classroom setting as well as in the context of given data mart development projects (mentored data warehouse development projects).

View this course in other countries

Roadmaps that contain this course are:

• Data Warehousing Implementation

Back to top

This course is for Data warehouse modelers with a prime interest in dimensional modeling for OLAP data marts, data base administrators and data warehouse data administrators, business intelligence experts and information analysts with an interest in multi-dimensional data warehouse modeling, and project managers and technical professionals with an interest in MDDM and a good background knowledge of data modeling (E/R modeling for traditional OLTP databases).

Back to top

You should:

• Have knowledge of the basics of data warehouse enablement and of OLAP information analysis in particular
• Be familiar with data modeling for traditional (OLTP) database applications
• Be familiar with relational database development and with relational database management systems in general

Back to top

• Apply MDDM basic principles to defined business requirements
• Produce dimensional models (Star- and Snowflake models) for non-trivial data warehouse modeling projects
• Position MDDM with respect to ER modeling and to Temporal ER modeling in particular

Back to top

Introduction to Data Warehouse Modeling

• Review of data warehousing and data warehouse modeling
• Differentiate data warehouse modeling from OLTP modeling
• Discuss data warehouse modeling approaches and investigate their area of applicability
• State the basics of analytical data processing and its relationship to data modeling

Multi-Dimensional Modeling - Methodology

• Define the base concepts of Multi-dimensional Data Modeling (MD modeling): terminology, definitions and notation techniques
• Apply initial MD modeling techniques
• Discuss various MD modeling hints, tips and guidelines
• Apply solution validation issues and techniques
• Define the detailed modeling of dimensions

Non-Temporal Design - R-OLAP

• Describe the most important design issues associated with a given multi-dimensional data model in a given OLAP query context: Star schemas and Snowflake schemas
• Identify the features of DB2 that help improve the handling of table associations (joins) and typical OLAP queries in an MDDM context
• Explain how Star-Join support, provided by DB2, works
• Propose solutions for the design issues covered
• Evaluate the effects of various alternative design techniques
• Utilize Cubing Services to improve R-OLAP and M-OLAP performance

Non-Temporal Design - M-OLAP

• Apply M-OLAP techniques to build analysis solutions
• Deliver a functional architecture, for producing a M-OLAP solution, based upon the review of the functions present in today's industry-leading M-OLAP tools
• Decide whether M-OLAP or R-OLAP techniques would better suit a particular set of analysis requirements

Temporal Modeling and Design

• Explain the basic and advanced concepts of Historical Data Modeling
• Discuss the different approaches of Fact Analysis

Agenda

Day 1

• Welcome
• Introduction to Data Warehouse Modeling
• Case Study Introduction
• Multi-Dimensional Modeling - Methodology (Modeling Process Overview)
• Case Study 1 and Review
• Multi-Dimensional Modeling Methodology (Base Concepts and Terminology)
• Case Study 2 and Review
• Multi-Dimensional Modeling - Methodology (More Concepts and Terminology)
• Case Study 3 and Review

Day 2

• MDM - Methodology (Star versus Snowflake Models)
• Case Study 4 and Review
• MDM - Methodology (State versus Event Facts)
• Case Study 5 and Review
• MDM - Methodology (Factless Facts and Fact Identifiers)
• Case Study 6 and Review
• MDM - Methodology (Dimension Roles)
• Case Study 7 and Review
• MDM - Methodology (Solution Validation)
• Case Study 8 and Review

Day 3

• MDM - Methodology (Detailed Dimension Modeling)
• Case Study 9 and Review
• Case Study 10 and Review
• Non-Temporal Design - R-OLAP: Basics
• Non-Temporal Design - R-OLAP: Design and Implementation
• Case Study 11 and Review
• Case Study 12 and Review

Day 4

• Non-Temporal Design - M-OLAP
• Temporal Modeling and Design with Inline Case Studies

Back to top