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

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 Modeling Zone

Last fall I was at the first Data Modeling Zone in Baltimore, USA. This fall there will be 2 conferences dedicated to data modeling. One in Baltimore,USA 8-10th of October and one European one in Hanover, Germany on 23rd and 24th of September.

Content

There will be a lot of info on FCO-IM, Model transformations, Data Vault, Anchor Modeling and much more.

Presentations

I'll be doing 2 presentations on the European Zone. One on Information Modeling and implementations around Data Vault, Anchor Modeling etc. and one on the success of Data Vault and Data warehouse Automation in the Netherlands.

DMZ Promotion

For those that would like to attend the organization has set up individual promotion codes for each of the speakers - my code is EversDMZPromo. Anytime someone registers for DMZ Europe with this code on DataModelingZone.com or DataModelingZone.eu, you will receive 100 Euros off the registration price. For every two people that register using your code, one student from a local university will be allowed to register for the conference for free. The promotion code is active up until the date of the conference and can be used any number of times. Please use the code if you intend to come

Hope to see you there!

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.

One (Metadata) Column To Rule Them All

One Column to rule them all,

One Column to find them,

One Column to bring them all  

and to the metadata bind them!

 Introduction

There is usually ONE column that is all over your Data warehouse. It might be called process_id, run_id, step_id or audit_id and it usually points over to a set of (logging) tables that hold all kinds of metadata for your Data warehouse platform. While this column is sometimes seen as mandatory (e.g. with Anchor Modeling or Data Vault) it is in general always seen as a good idea to have for any Data warehouse. There is usually just one column, but sometimes there are more (a composite metadata key) . While this column usually shows the actual load/ETL metrics for load batches/actions its implementation and definition varies from implementation to implementation.

The One (Metadata) Column pattern

I think that we should try to standardize on the semantics of such a column. This is easy if you have a good metadata model. The intersection of the static metadata (models,entities)  and metrics metadata (load runs and load cycles) with the ETL control metadata (etl moldules, etl step numbers, etl step order) should always lead you to the metadata on the lowest grain where you can define your most detailed process information. On this grain I would do my central logging and create the One Metadata Column (as a surrogate key). The metadata column's meaning is then directly related to this logging entity, and should not change during the operation of the Data warehouse. This does imply that a well designed and complete metadata model that covers all (definition, operation, automation and management) aspects is essential for having a good usable   metadata column. Since the Column is defined as the surrogate key on the lowest grain of your model, there can only be one. If your metadata model does not have a lowest grain (but e.g. several disparate ones) you apparently have gaps in your model and you need to extend/complete it before you can define your One Column.

Such a column has many advantages because it decouples your data and metadata to the maximum extent without losing information. It insulates the data from the metadata model changes through the use of a surrogate key, which then becomes the one point of connection bewteen these 2 models.
A few disadvantages to this approach are the more complex queries to tie data and metadata together, and coping with real time or near real time data proceses.

My preference of the column name is audit_id because such a column does not have to reflect a load run or process run, and not even an ETL step (but even an action within a load step).

I would think this pattern is esp. important with e.g. Data Vault to maintain audit-ability  so we not only know when but exactly what was loaded, how and form where. For DWH Automation efforts I think a metadata model and this pattern are very important aspects to implement consistently.

Metadata Model Example

I've created a minimal metadata model as an example. Here we see a very simple model that can be used in such a pattern. The following tables are in this model:

1. Data Objects: Here we store source and target tables
2. Load templates and Load template versions: Here we store which load templates/processes are defined
3. Load Batches: A Load batch is a collection of load actions/directives
4. Load Cycles: Each batch is loaded in regulated intervals. This is recorded in the load cycles.
5. Load directives: Records which entity is loaded from which source under a certain batch and with a certain template.
6. Load Actions: Records on which cycle which load directive was executed.

The column we need here is the Load action id. It is the surrogate key of the table with the lowest/widest granularity in the metadata model. Referring to this column we can answer all our metadata oriented questions around when, where, who, how and what.

Conclusion

The One Metadata Column Pattern is a useful and often implemented pattern within Data warehousing. I think it would be better if there is a consistent way in which to define and implement this. I hope this post helps to understand how to do this in a more structured fashion.

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