Short cutting Temporal Joins

Introduction

When you create temporal data models, either with Data Vault, Anchor modeling or any other way, you end up with several tables with timelines in them. These timelines often need to be combined for going to Data Marts or other processing. This combining is done with a temporal JOIN

Example

• Table Product
• Table Product Cost
• Table Product Price
• Calculating Product Revenue = Sales Price- Manufacturing Cost

Conceptually the query will look like this (using the Allen operator)

SELECT "product cost".cost                            Cost,

       "product price".price                          AS Price,

       "product price".price - "product cost".cost    AS Revenue,

       "product cost".period INTERSECTS "product price".period AS Period

FROM   "product cost"

       INNER JOIN "product price"

               ON "product price".id = "product cost".id

WHERE  "product cost".period OVERLAPS "product price".period

The issues here are twofold, how to implement this in plain SQL, and what to define for Revenue for the omissions. Here we could state that the revenue is undefined, so in this case the first period is not available. Alas, this is not always the correct result.

SQL Implementation

There are a lot of ways to construct an SQL query that implements an intersection of 2 timelines. The most simplest are checking overlap between each 2 periods. This will quickly create unwieldy queries when more timelines need to be intersected. A more sophisticated approach rewrite, given x,y,z are periods:

y OVERLAPS y OVERLAPS y to NOT EMPTY(x INTERSECTS y INTERSECTS z) and to

say a = (x INTERSECTS y INTERSECTS z) then a.start_data

max(x.start_date,y.start_date,z.start_date) to min(x.end_date,y.end_date,z.end_date) is a valid period, which means:
max(x.start_date,y.start_date,z.start_date) < min(x.end_date,y.end_date,z.end_date)

Basic Greatest/Least  SQL Query

Timeline Alignment

All these kinds of queries only work correctly when the different tables have aligned their timelines (have the same start and end dates for each key). For the end date this works ou as long am 'high end date' is used. For start dates additional records need to be created with empty/default/null values to align on a 'low start date' (say 1753-01-01).

DBMS implementation

These types of queries work in Both Oracle, Teradata and SQL Server. In Oracle and Teradata these queries can be used because the functions greatest and least are defined. In SQL Server however this is not the case.

SQL Server (T-SQL) implementation

To create this type of query in SQL Server we need both a Least and Greatest function. we can either code a .NET assembly for this, or an user defined function. Alas the udf won't perform at all. A trick here is to rewrite a scalar udf to an inline view udf. These will get optimized and peform OK. We only need to (CROSS) APPLY them to the query to get our wanted result.
One of the issues here is that you cannot use the helper functions in indexed views, you need to substitue the case statement, and duplicate it both in the select and where clause.

Helper functions

Here are the SQL Server helper functions greatest and least and their usage pattern.

1. CREATE FUNCTION [dbo].[ivf_least] (@1 datetime=null,@2 datetime = null ,@3 datetime = null,@4 datetime=null,@5datetime = null)
2. RETURNS TABLE WITH SCHEMABINDING
3. RETURN
4. (WITH coal(a1,a2,a3,a4,a5)
5. AS (SELECT COALESCE(@1,@2,@3,@4,@5),COALESCE(@2,@3,@4,@5,@1),COALESCE(@3,@4,@5,@1,@2),COALESCE(@4,@5,@1,@2,@3),COALESCE(@5,@1,@2,@3,@4))
6. SELECT case when a1<=a2 and a1<=a3 and a1<=a4 and a1<=a5 then a1  
7. when a2<=a3 and a2<=a4 and a2<=a5 then a2
8. when a3<=a4 and a3<=a5 then a3
9. when a4<a5 then a4
10. else a5 end as out from coal);
11. GO
13. CREATE FUNCTION [dbo].[ivf_greatest] (@1 datetime=null,@2 datetime = null ,@3 datetime = null,@4 datetime=null,@5datetime = null)
14. RETURNS TABLE WITH SCHEMABINDING
15. RETURN
16. (WITH coal(a1,a2,a3,a4,a5)
17. AS (SELECT COALESCE(@1,@2,@3,@4,@5),COALESCE(@2,@3,@4,@5,@1),COALESCE(@3,@4,@5,@1,@2),COALESCE(@4,@5,@1,@2,@3),COALESCE(@5,@1,@2,@3,@4))
18. SELECT case when a1>=a2 and a1>=a3 and a1>=a4 and a1>=a5 then a1  
19. when a2>=a3 and a2>=a4 and a2>=a5 then a2
20. when a3>=a4 and a3>=a5 then a3
21. when a4>a5 then a4
22. else a5 end as out from coal);
23. GO

Using this functions in the following way:

1. CREATE TABLE SAT1 (DV_ID int,START_DT datetime,END_DT datetime) PRIMARY KEY (DV_ID,START_DT)
2. GO
3. CREATE TABLE SAT2 (DV_ID int,START_DT datetime,END_DT datetime) PRIMARY KEY (DV_ID,START_DT)
4. GO
5. select
6.         SAT1.DV_ID,
7.         GREATEST_DT.out as START_DT,
8.         LEAST_DT.out as END_DT
9. from SAT1 inner join SAT2 on SAT1.DV_ID=SAT2.DV_ID
10. cross apply ivf_greatest(SAT1.START_DT,SAT2.START_DT,NULL,NULL,NULL) as GREATEST_DT
11. cross apply ivf_least(SAT1.END_DT,SAT2.END_DT,NULL,NULL,NULL) as LEAST_DT
12. where
13. GREATEST_DT.out < LEAST_DT.out -- assuming an [Closed,Open) period
14. GO

One "Data Modeling" approach To Rule Them all

The Task

In a previous post on implementation (data) modeling styles I talked about needing a 'universal data modeling technique/method' that could be a starting point for all your structural model transformations (semantically equivalent derivations), be that dimensional, normalized or Anchor Style Modeling. Such a modeling technique should allow for easy transformation, it needs to be agnostic to most transformation techniques, but should still guarantee several levels of semantic equivalence. It also should allow for (database) model re-engineering and allow for data representations on any desired "level" (conceptual, logical). It should facilitate conceptual data integration and facilitate temporal aspects as well.

The magic wand of Semantic Equivalence

It is important to realize that all implementation modeling techniques like dimensional and Data Vault rely on a certain class of model transformations I call fully semantic equivalent. These class of transformations preserve (parts of) the data (they preserve the functional, join, multi value dependencies as defined in the RM) and hence provide a degree of data tracability from one transformation to the next. However, they DO make some aspects more difficult, especially maintaining all kinds of (complex) constraints. These kinds of transformations are usually biased towards certain aspects. Things like constraint minimization and standardization are best served by (highly) normalized models. Manual temporal processing and schema change impact isolation are best served by Anchor style modeling techniques like Data Vault. User access and data navigation are best served by (logical) Dimensional Models (see the OASI concept of van der Lek).

The Candidates

For me there where only 2 serious options, namely an anchored version of 6NF on the logical level,  or a Fact Based Modeling technique (FOM) from the NIAM family: FCO-IM, ORM or CogNIAM on the conceptual level. Other techniques like OWL are poor on constraint modeling and derivation and hence lack the desired easy (semantic equivalence) transformation. 6NF is an interesting candidate on the logical level since it is irreducible, time extendable and  is able to house constraints and derivations. It does not contain key-tables (anchors) by default, but we can create an anchored version (A6NF) that creates an anchor for each key that has a functional dependency. Date, Darwen an Lorentzos showed how to formally define temporal features in the relational model as well. However, apart form conceptual aspects like classification and quantification, verbalization and specialization/generalization  normalized models also do not directly abstract away from relationship implementation (which is also a bonus, but not in this scenario), which, given the myriad types of surrogation strategies used in data sources is considered a deficiency here. Abstracting away from foreign keys to relationships/roles gives rise to some additional issues but all in all FOM's can do all of this nicely (as far as it goes). The Fact Based modeling family lacks something in operators, esp temporal ones, but that can be remedied with a conceptual query language and simple conceptual temporal extension. (Another issues is that we need to facilitate robust and customizable data model reverse engineering, something current FCO-IM only has in a simple form.) I think that of all the FBM dialects FCO-IM lends itself best for understanding model transformations/derivations because it's focusing on fact types. For me this means that FCO-IM is an ideal candidate to use both as a modeling technique and as a modeling method (actually a diagramming method btw). Coupled with the fact that FCO-IM is taught and researched over here in the Netherlands means that it was an easy choice for me to make.

The One Model Methodology

The result is that I depend on FCO-IM for my transformation strategy analysis, and that it has become an important part of the MATTER program. It allows me to analyze, verbalize, display and derive implementation data model schema's in a consistent and generic way. This allows me to understand arbitrary implementation modeling styles in just one conceptual data model as a restructuring of fact (types). This way I resolve arbitrary diagramming, modeling, designing of data model schema's in a set of consistent, complete and correct fact restructuring directives.

Derivations

Transformation vs Derivations

FCO-IM is usable in describing the class of fully semantic equivalent transformations since it conceptually captures dependencies. The actual restructuring is done on the conceptual schemas of FCO-IM itself. a Model transformation first becomes (conceptual) model (schema) standardization and from there model schema derivation. We call this structural transformation or model restructuring, but in fact it is model schema derivation strategy. 

Implementations vs Definitions

As long as FCO-IM diagrams cannot be implemented directly we would actually transform an FCO-IM diagram to an an Anchorized 6NF model. This would be our idealized implementation model. from here all our implementation models become derivation models. Hence, implementation models are logical models that are logically derivable and controllable from a A6NF schema, while they are conceptually derived from a FBM model. Some implementation modeling styles that are closely related to A6NF (like normalized or Anchorized styles) lend themselves for creating central data repositories, while others (like dimensional) lend themselves to become derivated abstraction layers on top of this. Alas, in real world implementations we are usually forced to materialize some of these derived implementation modeling styles directly in databases, creating extra overhead that needs to be managed by some sort of data automation.

Implementation Modeling Styles?

Implementation data modeling styles are not 'physical' data modeling styles. Physical  is a misnomer from the ER and SQL database world. a Physical data model is what is stored inside a database like indexes and table spaces. Implementation modeling styles are logical model deviations used for logically accessing data, but also serving some non functional requirements around presentation, processing, performance and maintenance. They are artifacts created to counter the poor separation of concerns within current data toolings like SQL DBMSes, ETL tools and bad data management, poor data quality, poor temporal support etc etc. They are used for restructuring database schema's to separate and handle these concerns. In a TRDMS, a true relational database management system we would generate just 1 logical schema. Data Vaults and Dimensional models as we know them now would not be needed.

The Mission

For a lot of BI professionals however, Information modeling using FCO-IM is terra incognita and semantically equivalent model transformations (if done at all) are done using basic ER diagramming, which prohibits good standardization, transformation and (formal) derivations and hence understanding of implementation data modeling styles. ER modeling is just about visualization/drawing/noting model schema's, and do not help understanding this. To make understanding implementation data modeling styles better and more objective we need to educate professionals in this respect. Also since derivation and transformation go hand in hand, they need to understand the role of the relational model and model derivation as well, not just the (technical oriented) artifacts like Data Vaults and Dimensional Models.

Your Chance!

In December we start our MATTER FCO-IM track so BI professionals can dive deep into this aspect of information modeling. We start withg 3 days hand on FCO-IM (8-10 dec.). See for yourself how FCO-IM works and facilitates good data modeling. See BI Podium website for more info.

Reprising the MATTER Data Vault in-the-trenches track this fall

This fall the MATTER program will reprise the Data Vault in the trenches track. This track, meant for the more experienced Data (Vault) modelers/architects/BI specialist  focuses on the broad subject of Data Vault, Anchor Style modeling, 'Temporal data modeling' and even some Anchor Modeling. This is a 'no hold barred' track, so if you want to have thorough insights into Data Vault and other techniques this is your track. We'll deep dive in all the issues and opportunities that these kinds of model approaches have to offer.

I will personally teach the track's upcoming installments:

1. Data Vault & Temporal Data Modeling in the trenches (11-12 sept.)
• Focusing on (advanced) modeling constructs like key satellites, model transformation, model optimization, temporalization, model segmentation and many more issues.
2. Data warehouse and Data Vault oriented Architecture, metadata & ETL (planned for begin oct.)
• Focusing on Data Architecture, Raw/Rule Vault, business data models, metadata, transformation & generation & Data logistics.
3. Advanced Data Modeling & Data Vault subjects (23-24 oct.)
• Focusing on advanced modeling concepts like sub/super-typing (specialization/generalization), abstraction, multiple keys, virtualization, ...

See the Data Vault agenda or BI-podium website for more details. If you have questions around the track's qualifications, information or contents, don't hesitate to contact me or comment below this post and I'll get back to you.

Data Vault vs Anchor Modeling: Who Is The One?

Introduction

Yesterday Data Vault and Anchor Modeling went head to head on the DWH Next generation Conference organized by BI-Podium and Vissers & van Baars recruitment. It was a great conference with over 300 attendees and lot's of sessions on Anchor Modeling and Data Vault

Anchor Modeling

Anchor Modeling has been invented by Lars Rönnbäck and Olle Regardt. It is a highly normalized anchor style modeling approach that has some aspects of 6NF. It looks somewhat similar to Data Vault, but there are a lot of 'gotcha's'. It is a closed business model driven approach with it's own simple business/information model technique and just like Data Vault uses a small sets of building blocks and adds time to the data. There have been several blogs on Anchor Modeling, by +Richard Steijn here, Hennie de Nooijer here and WorldOfIntelligence here.

Data Vault vs Anchor Modeling

Lars made a start with comparing Anchor Modeling and Data Vault here. Is there any truth to glean from the the specific items, their meaning and the score? Not really, because THE Data Vault flavour does not exist that we can compare with the tight definitions of Anchor Modeling. I found it more the Agile Data Vault style of +Hans Patrik Hultgren compared to Anchor Modeling. Many of the issues mentioned stem either from (past) best practices or lack of detailed standards on e.g. temporal data management. Most are not essential or irrevocably some differences run much deeper. Interestingly most techniques currently in use in Anchor Modeling(except the annex helper tables for bi-temporal modeling) are also used at (some) Data Vaults.

The Hidden Snag: Auditability & Adaptability

If fact, most aspects of Anchor Modeling we can apply to Data Vault or Anchor Vault as well, but auditability creates some serious issues. Anchor Models can never be guaranteed 100% audtiable in all circumstances, and proving Anchor Models are semantic equivalent with (arbitrary) source models requires some serious additional effort. This also means the transformations and loading routines have different issues that with a Data Vault, esp. on complex non auditable transformations (e.g. time line repairs). The flexibility of Data Vault flavors allows us to adapt to very different environments without losing the source data. The tightly defined Anchor Models can be easier to use due to their (internal) business model driven nature coupled with their high normalization. All this does not mean that source driven Anchor Models do not exist or are impossible. On the contrary, we see many people designing source driven Anchor Models, just as some create Business Model driven Data Vaults.

It's my party and I'll Vault how I want to.

It is clear to me that unless we address the issue of formally addressing data integration and source data models with integration modeling comparing Data Vault and Anchor Modeling is not really going to work. Instead we should focus on combining approaches. In fact my presentation on the DWH Next generation Conference showed a nice example of combining Anchor Modeling and Data Vault. As discussed in my post on data modeling styles they are just edge cases of a whole family. Since DV needs to work on a vastly larger range of architectures, it cannot be so tightly defined as Anchor Modeling.  It is this flexibility that proves that when we look at Data Vault in a flexible way, it should be better 'by definition' at capturing sources in an Achor Style, while Anchor Modeling is better at handling business models in an Anchor Style modeling technique. This is exactly how I see the usage of such patterns, not either/or but used both in the right context.

An Example

Here is the example to show we can use Anchor Modeling and Data Vault together effectively.

Challenge

žIntegrate disparate Business Keys
¡Keys not integrated

¡No Master Key
¡Want a 1-1 time variant mapping between system keys
¡Internal de-duplication should be possible
¡Business Rule Driven mappings
¡Efficient and flexible implementation

Resulting Model

The result of this challenge is the following model. It contains 3 normal hubs (with corresponding sats and links not shown). It contains 3 1-1 key mapping ties (end dated links) that register which Business Keys need to be unified. It contains 1 Anchor (a Business Key-less/Empty Hub) that connects it all together. Since none of the source keys is a true business key, and we do not want to invent our own, an Anchor is an ideal construct to use here since it does not contain a business key(just a surrogate key). We assume here that we have (complex) business rules that allow us to map the different keys to the central Anchor.

žSolution

Classical Single BK Source Driven Hubs

¡No Multi key or changing key etc. so no Anchor required

žEfficient 1-1 Key Maps/Ties

¡Efficient

¡Business Rule Driven, so stable cardinality

¡Dynamic and traceable

žEmpty Hub/Anchor to tie all Hubs to

¡No “Master” Business Key required

Architecture

The Architecture we see emerging clearly uses Data Vault for capturing sources, while it will use Anchor like constructs for connecting them together in a slightly more flexible way that Data Vault does. This way both techniques are used where their strengths lies.

A Unifying Style

While advances in Data Vault and Anchor Modeling are nice, i'd like to state that choice between and adaptability of the methods/approaches to circumstances by practitioners is also important. But for this we need to understand all the detailed differences between all these modeling techniques. Especially when we want to combine them. If we want this then we're back at a generic Information modeling or logical modeling technique to unify all Data Vault and Anchor Modeling styles. While Data Vault and Anchor Modeling try to seriously simplify the construction of Data Warehousing within their won context, WE still need to evaluate the approaches in our clients context. For this we need all the detail, so we can make a informed, independent, correct decision on which techniques to use. Alas, with Anchor Modeling and Data Vault using their own nomenclature, this does NOT make it easier for experts to understand and trade-off methodologies. Only the 'workers' doing the implementation have it far easier once an architecture and methodology has been chosen and automated. This realization was one of the key drivers for the MATTER educational program and also my research in unifying Anchor Modeling and Data Vault modeling using Fact Oriented information modeling and model to model transformations using FCO-IM.

In the end, Chosen Ones only exists in stories. For us mortals, as Codd said, only correct, consistent and complete and utter information hiding will save us from having to understand all these modeling techniques. In the mean time we'll be forced to thoroughly understand all aspects of data modeling, be it abstract information models or specific implementation modeling styles.

Implementation data modeling styles

Introduction

Business Intelligence specialists are often on the lookout for better way to solve their data modeling issues. This is especially true for Data warehouse initiatives where performance, flexibility and temporalization are primary concerns. They often wonder which approach to use, should it be Anchor Modeling, Data Vault, Dimensional or still Normalized (or NoSQL solutions, which we will not cover here)? These are modeling techniques focus around implementation considerations for Information system development. They are usually packed with an approach to design certain classes of information systems (like Data warehouses) or are being used in very specific OLTP system design. The techniques focus around physical design issues like performance and data model management sometimes together with logical/conceptual design issues like standardization, temporalization and inheritance/subtyping.

Implementation Data Modeling

Implementation Data Modeling techniques (also called physical data modeling techniques) come in a variety of forms. Their connection is a desire to pose modeling directives on the implemented data model to overcome several limitations of current SQLSDBMSes. While they also might address logical/conceptual considerations, they should not be treated like a conceptual or logical data model. Their concern is implementation. Albeit often abstracted from specific SQL DBMS platforms they nonetheless need to concern themselves with implementation considerations on the main SQL platforms like Oracle and Microsoft SQL Server. These techniques can be thought of as a set of transformations from a more conceptual model (usually envisaged as an ER diagram on a certain 'logical/conceptual' level but see this post for more info on "logical" data models )

Classes of Data Modeling Styles

It would be good if we could analyze, compare and classify these techniques. This way we can assess their usefulness and understand their mechanics. Apart from the methodology/architecture discussion around information system design and modeling (e.g. for Data warehousing: Kimball, Inmon, Linstedt or a combination) there is not a lot of choice. If we ignore abstractions like Entity/Attribute/Value patterns, grouping (normalization/denormalization), key refactoring, versioning, timestamping and other specific transformation techniques we end up with nominally 3 styles of modeling that are being used right now: Anchor Style, Normalized and Dimensional. All three relate to an overall model transformation strategy (from a logical/conceptual data model), and all three can come in highly normalized varieties and some even in strongly "denormalized" (better is to talk about grouping like in FOM/NIAM/FCO-IM)  ones (and anything in between). This means that the definition of each style lies in a basic transformation strategy which then is further customized using additional transformations, especially grouping.

Grouping vs. Normalization

Normalization can be seen from 2 sides. As an algorithm to decrease unwanted dependencies going from 1NF up to 6NF, or as a grouping strategy on a highly normalized design (6NF or ONF) grouping down to 5NF or lower. Besides being a model transformation process it is also used as a name for a family of related data models using ONLY this model transformation process.

Normalization Style

Standard normalized data can be in either 1NF to 6NF (which is the highest normal form). Normalization is the simplest and most fundamental family of transformation styles. It is currently mainly used for OLTP systems.

Anchor Style Modeling

What I call Anchor style modeling (also called key table modeling) is becoming more and more accepted, especially with Data warehousing. Data Vault and Anchor Modeling are both methods that rely in this family of techniques. The basic idea is to split (groups of) keys into their own entities (called hubs, anchors or key tables) and split of all other info in additional entities (non key table entities called leafs, satellites etc.). Another property of this family is that temporal registration is never done on key tables but only on the non key table entities. From there the different techniques and standards, as well as goals, diverge leading to several related techniques that are used to design Data warehouses as well as OLTP systems in different fashions.

Dimensional Modeling

From a transformation perspective Dimensional modeling is based on mapping basic 5NF models as directed graphs to a forest of trees where the root nodes are fact tables and all other nodes being dimensions. Also, all history is kept in dimensions using basic versioning. Fact tables then do a versioned join to the dimensions.

Implementation Modeling style properties

Apart from Normalization, implementation modeling styles have been mainly invented to overcome issues in database software, e.g. current mainstream DBMSes. They make (sometimes serious) compromises on aspects like normalization and integrity against performance and maintenance. Dimensional modeling overcomes some performance and some simple temporal limitations, and also covers some limitations of BI tools (mainly pattern based query generation), while at the same time isn't hampering usage and understanding too much. Anchor Style techniques help out with serious temporalization inside DBMSes while at the same time add extra flexibility to mutations of the data model schema.

Temporalization and Schema Flexibility

In a future post I'll discuss this classification's effect on temporalization, schema management and schema flexibility in more detail.

One Model to rule Them All

Ideally we would like to have a generic 'super' (conceptual) modeling technique or style that is able to direct and control all these techniques. A base (conceptual) modeling style that allows us to generate/transform to any target physical modeling style. We could use 5NF from the Relational Model, but there are also other considerations like the fact that Anchor Modeling more related to 6NF. We will explore this notion of a super model in an upcoming post.

Conclusion

Most modeling techniques can be expressed as transformation strategies, which in a generalized form describe families of related styles of implementation data modeling techniques. Apart from their corresponding implementation approaches these 3 styles actually cover the backbone of most implementations of current information systems be it Data warehouses or OLTP systems.

The structure of the MATTER DWH program

Introduction

In this blog post I'll show the most recent version of our detailed  track and course overview. Note that this is still a concept and is subject to change (although I hope not significantly). I will not discuss the contents of individual courses or tracks in this post, but instead focus on the overall structure of the program.

"Broad where we must, Deep where we can"

As blogged earlier, the MATTER program has the following tracks:

The program's track design reflects one of the basic principles of our program: " Broad where we must, Deep where we can". Because of this we decided to have a 3 layered approach in our program.
Layer 1 is broad and teaches basic and advanced methodology/technology tracks for Data Vault, Anchor Modeling and Temporal Data modeling. The Architectural and ETL track have been integrated with the Data Vault track because there is a lot of overlap there. We don't assume all students want to follow all the layer 1  courses when doing the program, and some students might only want to follow some of the courses in this layer without following the rest of the program. This layer is probably the most dynamic one as well. We want to offer possibilities to extend knowledge without trying to teach everything. Expect more (optional) courses in the next iteration of the program (basic Data Vault, Dimensional Modeling, Agile etc.) with also additional partnerships.
In the 2nd layer we go deeper and try to connect knowledge from layer 1.  We will still focus on specific methodology and architecture discussions. The main track here is on Information Modeling. Also the Architecture course is formally in this layer.
 The Master Class is where things come together and we tie all kinds of knowledge from the different tracks into a more consistent whole. We will discuss mechanics of creating modeling approaches by understanding modeling techniques and their place within (DWH) Architectures. We will also focus on the relation between architecture, methodology and modeling.

Course Overview

The following table shows all courses of the MATTER program, their intended level, prerequisites and number of days.

ID	Name	Track	lvl	Program Prerequisite                        Days
TEMP INTRO (I)	Time in the Database	TEMP	250	SQL, ER,3(T)NF, SQL	1
TEMP MOD (II)	Temporal Data Modeling	TEMP	350	TEMP INTRO (I) + Dimensional Modeling	1
AM MOD (I)	Anchor Modeling	AM	300	TEMP INTRO (I)	2
AM ADV  (II)	Anchor Modeling Architecture	AM	350	AM MOD (I)	1
AM IMPL  (III)	AM Implementation	AM	400	AM ADV (II)	1
AM CERT	AM Certification	AM		AM ADV (II)	½
AM ADV CERT	Advanced AM Certification	AM		AM CERT+AM PROJECT	½
DV MOD (I)	Advanced DV Modeling	DV	350	Professional DV knowledge (e.g. DV Cert, DV course+DV exp)	2
DV ARCH (II)	DV+DWH  Architecture	DV	350	DV MOD (I)	3
DV ADV (III)	DV Advanced Architecture & Modeling	DV	400	DV ARCH (II)	2
FCO-IM INTRO (I)	Hands on FCO-IM	FCO-IM	250	Basic database modeling/design	3
FCO-IM CASE (II)	FCO-IM case	FCO-IM	300	FCO-IM INTRO (I)	3
FCO-IM TRANS (III)	Transformation algorithms+case	FCO-IM	400	FCO-IM CASE (II)+ Dimensional Modeling Mod 2of3: DV MOD (I),AM MOD (I) ,TMP MOD (I)	4
DWH MASTERCLASS	Fact oriented + Temp. DWH Arch.	MSTR	450	FCO-IM TRANS (III) + : AM ADV (II)  or DV ARCH (II)	2
EXAM & CERT	Examination+CERT	MSTR		MASTERCLASS	1

In other blog posts I'll discuss tracks and courses in more detail. (if you have specific questions or interest in a course just contact me.)

Course Dependencies

The following diagram shows the detailed dependencies between the tracks and their courses. For example, we assume students following the FCO-IM transformation course understand two out of three data modeling techniques from our program (Anchor Modeling, Data Vault or generic Temporal Data Modeling). As you can see from the dependency arrows we combine knowledge gained from information modeling track and architecture courses in our masterclass on modeling and automation to understand advanced architectural concepts which should underpin our data warehouse designs.

Course subject matter matrix

We also have a detailed subject matter matrix per course to give an indication on what type of subjects will be taught in each course.

Course\MATTER	Modeling	Metadata	Arch	Autom	Temp	Trans	EDW	RDBMS	Impl	Related
TEMP INTRO (I)	-	-	-	-	++	-	-	++	++	-
TEMP MOD (II)	+	+	+	+	++	++	-	+	+	-
AM MOD (I)	++	-	+	-	-	+	-	+	+	-
AM ADV  (II)	+	+	+	-	++	+	+	-	-	+
AM IMPL  (III)	-	-	+	+	+	-		++	++
AM CERT	++	+	+	-	+	+	-	-	-	-
AM ADV CERT	++	+	+	-	+	+	+	-	-	-
DV MOD (I)	++	+	+	+	++	++	+	+	++	-
DV ARCH (II)	+	++	++	++	+	+	++	++	+	+
DV ADV (III)	++	++	++	+	+	++	++	-	+	++
FCO-IM INTRO (I)	++	-	-	-	-	++	-	-	-	-
FCO-IM CASE (II)	+++	-	-	+	-	+	-	+	+	-
FCO-IM TRANS (III)	++	+	+	+	+	+++	-	+	+	-
DWH “MASTERCLASS”	++	++	+++	++	+	+++	++	+	-	+++
EXAM and CERT	++	+	+++	+	++	+++	++	-	-	+

Course Subjects:

We'll do a LOT of (different styles/techniques/approaches) modeling within the courses of the program.

Metadata
We'll discuss the important concepts around metadata modeling and usage.

Architecture
We put current and future (Data warehouse/ETL) architecture and their construction in perspective.

Automation
Covers all subjects related to tooling and automation of Data warehouse design and implementation

Temporalization
All time related subjects like timelines and their implementations.

(Model) Transformation
This subject is mainly model 2 model transformation like 3NF to a Data Vault

Enterprise Data Warehouse

(R)DBMS systems
This stands for understanding Relational theory as well as actual DBMS systems like Oracle and SQL Server

Implementation
Here we will focus on implementation details on e.g. SQL or ETL tools.

Related knowledge
Here we put all related non core subjects that we will address like:

• Enterprise and Information Architecture
• Data Quality
• Data Management and Data Governance
• Security and Privacy
• Agile development
• NoSQL and Big Data
• Business Rules