Logging your loads: Data warehouse timestamping:

Introduction

There was an interesting discussion on Data Vault load dates in the Linkedin Data Vault discussion groups, and how you can go wrong by not understanding the different incarnations/types of load dates.
Load Dates (actually load time-stamps) are one on the most important metadata attributes for a Data warehouse. Determining the current "Load Date" seems very easy, until you take things like auditability, restartability , parallelism, Audit Trails/CDC load, message/real time and historic loads into account. In this blog post I'll try to describe the most common load dates used in Data warehousing and how you should capture them.

Types of Load Dates

There are several kinds of time-stamps in the loading process that can be registered within a Data warehouse loading process. While not all types of Data warehouses register the same time-stamps (or call them by the same names), from a logistics process there are just a limited set of dates that can be logged for a Data warehouse. Note that all actions and dates also have corresponding periods in which the process that generates the load date actually runs, but we will not discuss them here.

Load Cycle Date

In batch-oriented ETL architectures the most important date is the Load Cycle Date. It is the functional key that identifies an actual batch run. The granularity of the Load Cycle Date is usually a day, but can under circumstances be hourly or even in minutes or seconds. The Load Cycle Date does not have to represent the actual start or end date/time of the ETL process. The actual start usually lies after the Load Cycle Date, but this depends on the ETL scheduling. The Load Cycle Date is an abstraction from the actual load process date/period. The Load Cycle Date is used both to simplifies querying and maintaining time-lines and as a functional/foreign key to the batch run metrics of a specific load. This way it groups together all records that where loaded during a certain batch run. See the post on the "One Column" for more details on a metadata model that uses the Load Cycle Date.

(Source) Transaction time-stamp

The (source) Transaction (Load) Date (formally the transaction commit time-stamp) is the time-stamp that a transaction record was processed/committed (in a source system) or transaction based source extract. In most batch-oriented ETL architectures this is date is not available because the source system does not keep track of all transactions. But in a data feed from an Audit Trail/Change Data Capture log might use the given transaction time-stamps that can be used as a source for an Transaction Load Date. You can use such a date to reconstruct the source system transaction timeline. While under certain conditions a Data warehouse load process should be able to rely on externally generated time-stamps of e.g. an Audit Trail, some extra precautions are required. You need to verify the external configuration and also the external time service (if applicable) that is used. Also do not forget to check daylight saving time settings. Just to be safe, check incoming transaction dates even if you have a trusted source.

(Source) Application record time-stamps

These are record insert/create and update/modify time-stamps maintained by source systems. Do not trust external application code regulated date mutations in supplying 100% accurate transaction dates. Only an external transaction driven mechanism like DBMS transaction log readers or triggers should be used. Code regulated record dates rely on programmers honoring this in all their code. In most data-centric applications there are several ways for programmers to (inadvertently) ignore setting application record dates correctly and in doing so might be ruining your Data warehouse loading process when you rely on these dates!

Message (systems) time-stamps

For message systems like message queues or message bus systems a message (process) date is sometimes provided.

Data warehouse (insert/update) transaction time-stamp

This time-stamp registers the transaction (commit) time-stamp for either an insert or update into the Data warehouse.

Data warehouse Record Insertion Time-stamp

This is the actual time-stamp of the individual record insertion. It can usually only registered on a DBMS by creating a column that defaults to a time function registering the current date and time.

Extraction/Snapshot Time-stamp

Extraction dates specify when a certain data extract(data dump) was made from a source system database. Extraction snapshot time-stamps specify which time-stamp was used to create a transactional consistent extraction/snapshot set. This snapshot time-stamps, when reliable, are very valuable and can be used with the same caveats as the source transaction time-stamp. For loading strategies using full extracts using transactional consistent snapshots should be the default. Extraction time-stamps can only equal the quality of a snapshot time-stamps when there is guaranteed no data modification during the extraction window.

ETL Process time-stamps?

The (ETL) Process time-stamp(s) signifies the point in time when a certain record or set of records has been processed by an ETL process into a Data warehouse. This can be an independent ETL-(sub)process generated time-stamp  but more often it is actually the Data warehouse transaction time-stamp of the insert/update of the Data warehouse tables. Other Process time-stamps usually lack (transactional) consistency and are only interesting to measure a process duration.


Real Time Process time-stamp 

The (Real Time) Process time-stamp is only important in non staged Real Time scenarios. In those circumstances there is no formal  load cycle and hence no Load Cycle Date. But usually this time-stamp is a synonym for another time-stamp like the message or DWH transaction time-stamp.

Historized (Expected) Load Cycle Date

This is a very special type of Load Date. It is only useful when doing historic loads or when your batch oriented ETL process needs to be extremely robust and cannot rely of fixed “extract and load” cycles. It signifies what the original expected Load Cycle Date should have been if the ETL process had run correctly at that time. In batch oriented loading this date is usually derived from a Source extraction time-stamps  Extraction snapshot time-stamp or the available Transaction Load time-stamps. Note that the Historized Load Cycle Date based on an historical load might not have the same granularity as your standard Load Cycle Date. It might be loaded from saved monthly extracts instead of daily feeds. Naturally, we can not only calculate past but also future(expected) load cycle dates as well.

Loading

Real Time/Message based loading

In real time loading scenarios when other types of Load Dates are not available we only have the Process time-stamp/Message time-stamp or Data warehouse transaction update/insert time-stamp.

Historic Loading

Historic loads are easy to do once you have established the different types of load dates. The only extra item is that in an historic load you have also a Historized Load Cycle Date. This date presents the (approximate) Load Cycle Date that would have been correct if the data warehouse had been available at the time of the data extract.

Restartability and Historic Loading

The Data warehouse load process should be fully restartable. However, the source extracts and stage loads are another matter. While CDC/Audit trail solutions often have a good restartability, often served by the restartability/transactional consistency of the underlying DBMS products, batch-oriented extractions don't match this by default. A good load process accepts that there is not always a 1:1 between source extracts/stage loads and Data warehouse loads. This might not be the default situation, but due to hardware or network issues there might be several extracts waiting for one Data warehouse load to process, or several Data warehouse loads are required to process one supplied source extract. This many-to-many relationship reinforces the idea to add an ETL (or source) driven Extract/Snapshot time-stamp to the process to distinguish extracts, and to use this time-stamp to drive the Transaction Load Date and Historized(Expected) Load Cycle Date. This makes that a batch load can be treated as a special kind of primitive Change Data Capture. In mimicking CDC behavior you increase the robustness of the load routines. A detailed discussion of this technique is a topic for another post.

Reconstructing transaction timelines

When you need to reconstruct (transaction) timelines in your Data warehouse (e.g. in Data Vault satellites) we can  use some of the load dates we gathered. If you have several kinds of load dates it pays of to understand which load date to use. The problem is that choosing a date is always a trade-off between accuracy and consistency. While the Record Insertion Time-stamp is very accurate in relation to the Data warehouse, the (Source) Transaction time-stamp is far more consistent with the other (source) data. Personally I prefer consistency, because accuracy can be offset by a technique I call 'The Traveling Now' which implements system wide reference dates to prevent the inconsistent and inaccurate data that is currently loading being viewed by users (I'll get back to this on another blog post), while inconsistencies need to be resolved with additional processing.

Batch Oriented Loading

For batch-oriented loading the transaction timelines are best driven by Transaction Load Dates when available. If they are not available use the Controlled Extraction Snapshot time-stamp or the Load Cycle Date if no controlled snapshot is provided. Integrated/multi sourced entities (e.g. Data Vault satellites that integrate common attributes over several sources) are best served by a Load Cycle Date. This is required to synchronize the transactional time(lines) across the different sources.

Historic Loads

Historic loads into an auditable DWH that itself maintains a transaction timeline takes some consideration. We consider 2 scenario's: historic loads with the same level of auditability as your normal loads (even if it is just a subset of data and it's temporal granularity differs) and historic loads whose auditability level is lower than your basic loads.

Auditable Historic Loads

If your historic loads mimic your basic loads and when your historic loads contain controlled extraction (snapshot) time-stamps or (source) transaction time-stamps you can use your basic loading routines (either based on the historical load date or the time-stamp it is based on) to reconstruct your transaction timeline. You might even consider and ETL routine that accepts many loads at once as described above.

Sub level auditable Historic Loads

If historic data loads differ considerably from normal loads you might want/need to handle them differently.
For those loads a good Historized Load Cycle Date  is mandatory, since that will be used to reconstruct the transactional timelines.  The Load Cycle Date is useless here because they have no connection with the data to load. Note that deriving a Historized Load Cycle Date  is bit of making up data based on available extract data and metadata. Also, in this scenario the granularity of the Historized Load Cycle Date for the historic loads will probably be different from your normal loads. (e.g. weeks,months or even quarters instead of days).

An alternative approach for those that consider historical loads not to have the same level of auditability as your normal loads is to add an additional time-line and use the Historized load cycle dates to govern that additional timeline and treat your standard transactional timeline as a normal batch load (Normal Load Date). This second time-line is something between a valid timeline and an additional transaction timeline, but it should keep your data fully auditable. Another side effect is that unless you know how to query the historical data this data will not show up on standard queries referencing a point in time of your new valid timeline.

Real Time Loads

Assuming no other information is available real time or message based loads are best served with the message time-stamp (or an ETL process based timestamp) or a DWH insert transaction time-stamp.

Metadata

What to log

My advice is to add all important dates: Transaction Load Date, Process Date, Historized (Expected) Load Cycle Date, extraction/Snapshot Date and Load Cycle Date(as appropriate) to your Data warehouse  when available. This setup allows to mix different types of loading while maintaining full Load Date (metadata) transparency.

Where to log

There are three places to store time-stamps; in your metadata, in your actual data rows as data/metadata, and as time-stamps that drive your temporal registration/timeline construction (in DV satellite load date).

1. Time-stamps you can register either in the metadata or in the data row
1. Load Cycle Date
2. Historized Load Cycle Dates
3. (ETL) Process Dates
4. Extraction (Snapshot) Time-stamp
5. Data warehouse (insert/update) transaction time-stamp
2. Time-stamps you can register only in the data rows
1. DWH Insertion time-stamp
2. (Source) Transaction Date
3. Message (systems) Dates
3. Time stamps used for timeline/Historic Load Date reconstruction (in order of preference)
1. Trusted (Source) Transaction time-stamp
2. Controlled! Extraction/Snapshot Time-stamp
3. Historzized Load Cycle Date
4. Load Cycle time-stamp (not available for Real time loading)
5. Message/Real Time (process) based time-stamp
6. Data warehouse (insert/update) transaction time-stamp

For the sake of consistency and flexibility I always advise to also log time-stamps used for timeline reconstruction separate from the timeline (attributes) themselves.

Conclusion

When logging data loads don't skimp on logging loading time-stamps and use the correct time-stamp to recreate your transactional timelines.  Take this into consideration when designing your Data warehouse (eg Data Vault) ETL and metadata. Separating data and metadata so you can register different incarnations of the Load Date and other metadata from the Data warehouse is therefore considered as a best practice. The Load Cycle ID is a good start, but it does not cover all metadata. This notion is best served with an Audit ID as explained in the  "One Column" post.

Data Vault and data modeling in the Trenches 27th,28th Feb.+ March 6

Intro

While I'm meeting a lot of Data Vault specialists in the field, there are always a lot of questions and assumptions unanswered while working in the trenches. The focus on value and productivity is difficult to merge with delving deep and doing reflection. The  DV in the trenches track is one of the few possibilities to dive into Data Vault without discussing urgent and pressing issues around loading and transforming your next Hub or Link. But of course, no theory survives contact with reality, so we will be doing a lot of reflection on actual implementations, current standards and best practices.

So if you really want to learn about data modeling and learn about Data Vault in a broader scope? Discuss Data Vault 2.0 or understand exotic but oh so practical key satellites? Getting to grips with 'modeling standards', architectures and frameworks? Anchor Modeling, or even what the rest of the MATTER program has to offer? Join me on the coming DV course of the DV in the trenches track.

Schedule

The MATTER program will be scheduling a new iteration of the Data Vault modeling course of the DV in the trenches track. It will be held February 27th and 28th, and an extra day on March the 6th 2013. I'll be teaching this course personally so I hope to see you there ;)

Registration

Registration can be done at the website of BI-Podium here.

Data(warehouse) Architects: Building Bridges

Introduction

Like a lot of of BI and Data warehouse specialists and architects, and I sometimes find myself in conflict with the the architect(s) of the sourcing systems. Be it on data quality, interfacing, data modeling or other kinds of sourcing issues. I then try to advise on changes to increase the quality of the back-end data architecture (components) and infrastructure instead of mitigating this in my DWH solution(s). But due to the nature and scope of the (BI/DWH) project, my position, the infrastructure and the organization there is lot of resistance that makes this difficulty to make this work, despite the better Return on Investment.

BI vs OLTP

The traditional divide between operational and analytic,  between OLTP systems and BI systems is becoming more and more an inhibitor for good Data Architecture. In the past a physical division between BI and OLTP was a given, driven by implementation issues physical and logical separation where the basics for any DWH initiative. This has led to the existence of two separate worlds, the Operational world of operational processes, COTS (Common of the Shelve) systems and operational system integration, and the analytic world of ETL, (custom) Data warehousing and Business Intelligence. But more and more these two world are merging (and rightly so). There are a lot of techniques and trends that bring these 2 worlds together: data federation/virtualization, data vault, combined operational and analytical master data management and operational BI/-Data warehousing. In this light the separation between these two worlds should be transformed into a cooperation.

What's in a name?

Given the merging of BI and OLTP space, what is the role of the Data warehouse Architect? For me, Data warehouse architects are specialized Data Architects, just like Integration and Information System Architects are specialized Data Architects. This role differs from the BI specialist/architect/analyst, who is mainly concerned with the "data into dollars" questions; to make the data actually work for an organization. Data Architects however work on the organizing and structuring of data, be that for operational or analytical usage.

The new Data warehouse Architect

The new Data warehouse Architect understand Data Architecture both from an operational and an analytic standpoint, understand BI and accompanying tooling and architecture, but does not have to be a BI expert/specialist/analyst. He works closely together with other Architects and analysts from both the BI and the operational side. He is literally the bridge builder between the operational and analytical world.

Housebuilding, Data Architecture and Data warehousing

Introduction

Traditional Data warehousing focuses on designing and developing information systems to support an organization's BI initiatives and BI tooling. With the advent of new techniques, technologies, architectures and approaches we see the line between Data warehousing and other classes of information systems (transaction processing, interfacing, analyzing) blurring more and more.

Building a House

Building up your IT structure is a lot like building a house. The same goes for your data architecture. However, a data architecture is usually not a building built from scratch by an Architect, but a building made of a lot of prefab elements like rooms (applications), plumbings (Interfaces) and foundations (IT infrastructure). The end result depends, but it is usually more comparable to the Winchester Mystery House, or an Escher drawing than a regular building we work or live in. 

All elements we buy are usually standardized by the supplier or builder, but still needs serious adaptation before it will fit in your data 'house'. Even then, it won't look pretty since size, look and makeup will usually differ considerably between elements from different suppliers (and even from the same supplier). 'Window dressing' will only make it palatable by the casual observer, not by the persons living inside.

Data Warehousing

While a Data warehouse can be seen as just another room (application), there are some special considerations. Data warehouses are supporting information systems, more a support and bridging structure for your house. They are usually custom built, bridging gaps between standard elements and supporting new types of rooms. This is different from most of the other elements/rooms of your 'data' house (Even if the Data warehouse itself is made up of standard elements). From this new support structure your BI applications (new rooms!) can now be built. 

If our other (prefab) elements where well designed and structured in a way to connect seamlessly we might have no need of this new type custom support structure, but in practice this level of design is difficult and not implemented very much.

But why focus on a support structure for certain types of rooms (applications)? A normal house has just one intergated support structure that supports all the rooms and plumbings. Given current state of IT this overall support structure is usually built around the (IT) organization and is almost always an exercise in custom building, not an element of your 'data house' you can just buy directly of the shelf.

Building Better 'Data (Ware)houses'

Instead of looking directly for a Data warehouse as a supporting structure, we should look at supporting our total Data Architecture, which could include subjects like Data Warehousing, Federation/Virtualization, Data storage, Business Intelligence, Data Migration or data interfacing. We  should focus on building a whole 'Data house' with just one 'supporting structure', of which our familiar 'Data warehouse' can be an intgrated part instead of just another element grafted on our already baroque 'Data house' landscape.

Data Modeling Zone 2012: Dutch Data Vault and FCO-IM

I'll be doing 2 sessions at the DATA MODELING ZONE 2012  on November 12th-15th 2012 in Baltimore, Maryland. This is a great event in the USA to sharpen my data modeling skills and connect with the community! I'll be co-presenting a half day session on FCO-IM in which I focus on transformation of an FCO-IM model to star schema and Data Vault. I will also be presenting a session called "Riding the Dutch Data Vault wave", which is about the rise of Data Vault and data warehouse automation in the Netherlands.

hope to see you there!

MATTER Introduction event

For those that are interested in the MATTER advanced educational program we will organize an information event at the HAN in Arnhem. This session will be held on Thursday the 25th. of October from 13.00 to 17.00 hrs. It is an event in which we will explain the program in great detail. You can meet members of the Program-, Exam- and Advisory Boards as well as course instructors. We will present the different tracks and courses and answer all and every question you might have. We will have external presentations from Juan Jose van der Linden (QOSQO), Lead Architect of Quipu and Harm van der Lek, lead Architect and inventor of BIReady to show some actual results of the concepts we teach in our program.

Agenda:

• 12.30 -13.00 Registration
• 13.00-13.10 Welcome
• 13.10-13.45 Introduction of the MATTER program
• 13.45-13.55 Introduction Data Vault ïn the trenches" Track
• 13.55-14.05 Introduction Anchor Modeling Track
• 14.05-14.20 Introduction Temporal Data Modeling Track
• 14.20-14.35 Introduction Information modeling/FCO-IM Track
• Break
• 14.45-15.30 Quipu: open source datwarehouse automation tool
• 15.30-16.15 BIready datawarehouse automation
• 16.15-16.45 Organisation and costs
• 16.45-17.00 Questions and Closure

We will also do intakes and budget plannings for the program. If you want to attend, pls contact me or Simone Molenaar.

UPDATE:

BI-PODIUM updated their website and opened it up for registration:
http://www.bi-podium.nl/showcase/informatiebijeenkomst_dwh_matter_program

Data Vault "in the trenches" track

"No theory survives contact with reality"

Introduction

On the 21, 22 and 29th of November the MATTER program will start with its first course of the Data Vault "in the trenches" track " Advanced Data Vault Modeling". Since Data Vault and related techniques lend themselves quite well to automation we wanted to offer deeper insights into these techniques in relation to modeling and automation. As a prelude to this first course I'll blog about the track and its contents.

The Data Vault "in the trenches" while part of the MATTER program actually predates it. We already developed this track and decided it would be good to add and embed this in a broader program which in turn became the MATTER program. The goal of this track is to not focus directly on the standards but on the places where we see the real Data Vault casualties; in the trenches. This also meant we would be focusing our courses on BI veterans who already had real life Data Vault experience. The reason for this is that we still saw Pyrrhic victories and actual defeats when Data Vault was chosen as part of BI initiatives, mainly due to a lack of real world experience in all aspects of data (Vault). Also, while experienced Data Vault specialists are available to keep those initiatives on track, they can't be everywhere, and not all projects have them aboard. Since complex Data warehouse initiatives do not become simple ones when employing Data Vault, but actually get more complex because of the lack of serious Data Vault implementation experience we decided to help out by developing the Data Vault track now part of the MATTER program.

The Structure

The Data Vault "in the trenches" track is composed of 3 courses:

1. Data Vault Modeling
2. Data Vault and Data warehouse Architecture and ETL
3. Advanced Data Vault

We had several proposals on how many courses we should have. There is a difficult trade-off between time and costs vs. content. In the end we decided we would take 2 days for modeling and 3 days for the rest (Architecture ETL, DBMS etc.). If there was anything left, we'd make an advanced course to take on those subjects. The agendas of the courses are still growing as we speak, so there is no lack of material, but more an issue with prioritization. The Advanced course is now set on 2 days.

In the trenches

When designing the track we evaluated the usefulness of offering also introductory courses. We came to the following evaluation: Standard introductory training is not the best way to start a large Data vault implementation on your own. Real world experience will help, but there is bit of a catch 22 when you try this on your own. Real world experience in other Anchor Style Modeling techniques like Anchor Modeling or (generic) temporal data modeling would help, but training and experience in those techniques is even rarer.
Controlled teaching and implementation seems to work well, but not everyone has such an opportunity.
If we would offer basic training and implementation this would seriously extend the program and require additional investments.

For those that are really new to Data Vault we advise either self-study with the Data Vault book and doing some serious prototyping first, or enrolling in (standard) Data Vault courses, either on the web or in a classroom. A very good way of learning the basics of Data Vault is to do a combination of Data Vault teaching/training and implementation on your own Data warehouse ("on the job") with external coaching/supervision.

From there you should have enough experience to start digging the trenches (track).

The content

As you might have guessed this track is not about your "mothers" Data Vault ;). Here we try to understand not the basics of Data Vault, but the FULL SPECTRUM of Anchor Style Modeling techniques (including a little Anchor Modeling) and the standards, methodologies and architectures that come with it. We will look at extremes at both ends of the "Data Vault" solution space to learn what drives a good (modeling) and ETL approach. We'll also look at the tension between standardization, optimization and formalization of Anchor Style modeling techniques and their methodologies. We think all this detail is important because even small choices can have a substantial impact on your automation efforts!

(Advanced) Data Vault Modeling course

I'll show a compacted agenda since the track's "bullet sheet" is quite long at 11 pages (and still growing)

• The Data Vault Definition Dilemma
• We start with the discussion on (data modeling) approaches and methodologies, and the issues that arise trying to describe and define them. We'll focus on the tension between standardization, optimization and. formalization.
• Data Vault Entity Types including reference tables
• We'll do an in depth, exhaustive of all modeling constructs and transformation patters that are used, misused, unused or even problematic that are possible when doing Data Vault or other Anchor Style modeling approaches. We'll talk about structures like end dated links, key satellites and key-less hubs. We will also cover basic temporal techniques like "stacking" and hybrids between Data Vault and T3NF.
• Data quality issues and Handling Data MIA (e.g. “Null” Rules)
• We'll try to be complete on all kinds of modeling issues surrounding Data Vault, form Nulls to non-unique keys and how to handle them.
• Modeling Styles, transformations. model analysis and change management
• We'll cover and compare and classify all varieties of Anchor Style modeling techniques and approaches like "Anchor Vault" and classic Data Vault. We'll also dive into model analysis and change management.
• Inheritance and virtual/derived Data Vault entities
• We'll cover the basics of using derivation within Data Vault. We'll do the basics of sub-typing within the Data Vault.

Note: the first scheduled course will also have a free third day in which we will discuss all kinds of subjects including (but not limited to) the Architecture and Advanced courses of this track. Don't hesitate to contact me with questions and suggestions for this extra day.

Dutch Data Vault Introduction talk at TFG Innovation Event

Thursday 1^st of November I’ll be doing an introduction session (in Dutch) at the Dutch TFG Innovation event at the Amsterdam office of IBM called ‘Data Vault: De moderne aanpak voor het realiseren van een centrale “Datapakkluis”’ ('Data Vault: modern approach for implementing a central "Data warevault"').  This is a free event that is being organized by The Future Group. The event is focuses around innovations within IT. It starts at 18.00 hrs and has several sessions on subjects like BI, analytics, architecture and software development. So if you’re interested, don’t hesitate to register. For those that are interested in the full program or want to attend, just download the the invitation. You can also download the poster below right here!

I hope to see you at the event!

TFG Innovation event

Introducing the M.A.T.T.E.R. Advanced Data warehouse educational program

What does MATTER stand for?

Intro

As some of you already know I'm both initiator, instructor but above all Program Director of the MATTER advanced Data warehouse educational program. Since we are currently rolling out our program I did my first official presentation on the Data warehouse automation conference (DWHAutomation). The MATTER program was a main sponsor at this event and was received very well. Here we announced our first scheduled course on advanced Data Vault modeling (see the bi-podium website for details). We are currently in the process of setting up registrations and website and fleshing out parts of our procedures.

Why?

We think that that modern Data warehouse should be valuable; it should add a clear value to organizations. This means they should be usable, but also be built in an agile, quick and cost effective way. This can only be achieved by a strong focus on automation efforts. But this is only feasible if you rely on modeling, transformation and metadata in combination with a robust and generic temporal aware (ETL) subsystem that together will form an agile, automatable and flexible data warehouse Architecture. We think that current efforts by vendors, consultants, clients and users deserve a strong educational program to support their efforts. Since there was no program or course focusing on these subjects we decided to create a whole new advanced educational program, the MATTER program.

Who?

The MATTER program is a unique collaboration between field experts/instructors and the Model-based Information Systems (M-BIS) research group at the HAN University of Applied Science. The commercial parties involved are the Ockham Group, DataMasters(Unseen), Occurro , Lars Röhnback (anchormodeling) en bi-podium. The program is led by the Martijn Evers, Program Director, and Simone Molenaar, Program Coordinator.

What?

MATTER is an unequalled program to gain deeper insights into (agile) data warehouse architectures and methodologies. Its focus and synergy should allow you to create extreme flexible, agile, automatable and cost effective data warehouse solutions, or allow you to make better use of existing data warehouse automation solutions.

Program Focus

• Modeling: Learn advanced (temporal) modeling techniques like Anchor Modeling and in-depth Data Vault. Use information modeling to increase the quality of your data models. Understand the effects of temporalization on your data modeling efforts.
• Metadata: In depth focus on metadata structures to support your data warehouse (automation) efforts.
• Architecture: Focus on advanced and agile DWH architectures using Data Vault and Anchor Modeling. Realize the benefits of fact oriented ETL architectures.
• Automation: Understand how to automate your DWH solutions. Learn the different approaches and ways to automate parts of your data warehouse’s ETL and architecture using modeling, metadata and transformation techniques.
• Temporalization: Understand the effects of time in the database. Understand temporal concepts and their implementations. Understand how to create bi-temporal systems, both for data warehousing as transactional systems.
• Model Transformation: Gain deeper knowledge in modeling by understanding how to transform from and to modeling styles like Anchor Modeling, Dimensional Modeling and Data Vault using a fact oriented information modeling technique (FCO-IM: Fully Communication Oriented Information Modeling) as a transformation language.
• Enterprise Information/Data warehouse system design: Understand how these techniques and methodologies can be used to architect modern flexible and automatable enterprise class Data warehouses or other information systems.
• (Relational) Database Management System implementation: Learn about the background, limitations and implementation details of current mainstream SQL DBMS systems with respect to these modern techniques and architectures. 

How?

Program Overview

The program courses are organized into layers and tracks. Each layer builds on the previous layers and progresses the knowledge and insight of the participants. 

We have a temporal methods layer that contains 3 tracks. It has a clear focus on a certain method or technique: Data Vault, Anchor Modeling and generic temporal modeling. The next layer consists of an Information Modeling track using FCO-IM. Here we use information modeling and use transformations to derive data models like star schema’s, Anchor Models or Data Vaults. The next layer consists of a “Master class” which focuses on architecture and modeling based on the knowledge gained from information modeling and transformations.

Program Contents

The content focus per track is shown in the Matrix below:

MATTER\Track	AM	DV+Arch	TEMP	FCO-IM	Master Class
Modeling	++	++	+	+++	++
Metadata	-	++	-	+	++
Architecture	+	++	+	-	+++
Automation	-	++	+	+	++
Temporalization	+	+	++	-	+
Transformation	+	++	+	+++	+++
Enterprise DWH	+	++	-	-	++
RDBMS	+	++	+	+	+
Certification	Yes	No	No	No	Yes
Implementation	+	++	+	+	-
Related subjects	-	+	-	-	+++

Certification

There is an optional overall certification whose level exceeds the regular Master degree as well as specific Anchor Modeling certifications. The certification process is done in coordination M-BIS research group of the HAN University of Applied Science. The examination consists of an in depth and interactive assignments. The program has its own exam board to oversee the examination and certification process.

The program also has regular course examinations and certifications, as well as a few specific specialist track certifications around Anchor Modeling.

Joining the program

While the program consists of several tracks each with several courses, there is no obligation to follow all tracks/courses. It is possible to just follow individual courses or limit yourself to just (parts of) a few tracks. Some dependencies exist, especially for the end certification. For those interested we do intakes to provide customized programs for all participants. This allows for an optimal result at minimal effort and expense.

When?

The program starts off in 2013, but we are already starting up the Data Vault "in the trenches" track this fall. It will start with a special extended version of the Advanced Data Vault Modeling course. For more information on how and what of the individual courses see the bi-podium website. Individual courses will be spread out so the full program will run for approx 1.5 yr.

There will also be a free information session of the full program for prospective students on Thursday 25 October at the HAN campus in Arnhem from 13.00-17.00hrs

Where?

Locations still need to be announced, but we do know that most of the courses will be held either around Utrecht and the HAN ICA Campus in Arnhem.

Styles of Data Vault modeling PART I: Overview

Introduction

Sometimes we see that given the same input and process, the end result still differs between situations. This also happens when creating (data) models from requirements, and hence also with Data Vault data models. As long as this is intentional and can be explained this does not have to pose an issue. But often it is an indication of lack of knowledge or common understanding. It is usually quite hard to track down where the assumptions differ based on just the end result, and this is no different with the Data Vault approach. One of the main reasons for this difference lies in the somewhat loose definition of the Data Vault, it allows for some variation. This has led to several (closely related) styles of modeling a Data Vault.

In this post I'll introduce different modeling styles of Data Vault I've found to date. We will focus on the modeling part, and not on the architecture part. So we are not discussing differences between source, business or raw data vaults, but mainly different ways to transform the same starting (modeling) scenario into a Data Vault.

The Data Vault approach

Data Vault is a modeling approach/method that assists with creating the central data repository in an Enterprise Data Warehouse. It works on the basis that there is some hub/spoke EDW architecture defined (e.g. an Inmon DW 2.0 architecture). In this architecture the central repository is there for data management while the outlying spokes (Data marts) are used for data delivery to users. Its basic assumption is that you begin by focusing on getting source data in your EDW (not cleansed and restructured data). 

Data Vault however forces you to adapt all your data to fit into the three basic building blocks that are allowed when creating a Data Vault. This means source data should then be reshaped so that it fits those basic data entity building blocks. These blocks are called Hubs, Links and Satellites. 

These building blocks have a very basic makeup: 

•     Hubs are just lists of elementary/independent key values. They implement independent keys. Since Data Vault is interested in the big picture, these are called 'business keys' and they represent common identifiers around the business (invoice number, customer number, employee number).

•     Links are associations between hubs

•     Satellites track attributes associated with hubs and links through time.

Besides the building blocks there are also loading patterns for your central EDW repository. These follow from the definition of data entities in combination with the requirement of loading the source data unaltered into the Data Vault.

Data Vault modeling Styles

However, this recipe is just a global pattern. For example, it is does not work as a complete and detailed (semantic-equivalent) structural data model transformation recipe if we compare it to the transformation algorithms we use within FCO-IM and other Fact oriented Modeling techniques (FOM’s).

Besides the flexibility in definition and design of the Data Vault standard other factors contribute to variations in the Data Vault approach.

1. There is a large history on using similar temporal data modeling approaches with OLTP systems like Veldwijk’s HTC.
2. Additional requirements on flexibility and reuse of data and metadata
3. The advent of new hardware and software like database appliances and in-memory technology
4. The rise of similar EDW oriented modeling approaches like Anchor Modeling

This has led to the rise of what can be loosely called different flavors or styles of Data Vault modeling (especially with regards to end dating links). These can be described as varieties in the underlying structural model transformation strategies. What I present here is by no means a complete list, but it attempts to identify the most important structurally different varieties we currently see to date. We can explore these different varieties of the Data Vault by looking at the definition and transformation process.

Classical (aka Dimensional) Data Vault

In the Classical (I describe this as the ‘Dimensional’) Data Vault all links represent n-ary associations between hubs. These links combine all hub references in a source tabel, both hub references that are part of the source table key, as references that are not part of any key. Because of the auditability requirment we need a “driving” key in the link (which is in fact a representation of the source table key) for proper temporal data management. Such a Link can be considered a “proto” fact table, containing all dimensional references (although time independent) but without the measurements. It will usually be pretty wide. It tries to minimize joins at the expense of duplicating rows when non-driving key elements are updated in the link. This style of Data Vault modeling lends itself for easy staging to a dimensional layer, but link re-usability for e.g. a Business Rule Vault is tricky.

Anchorized Data Vault a.k.a. Anchor Vault

The Anchor Vault focuses on modeling Anchors (note, these are related, but not identical to the Anchors in Anchor Modeling). These can be independent (Hubs) and dependent (links). They are purely driven by source key columns. Here we basically put each source key in its own hub or link and its dependent attributes in a satellite. The main reason for doing this is easier transformation, Anchor/key re-usability and easy loading and managing the temporal aspects of your data (e.g. timeline completion and standardization). Those considering complex loading (e.g. Change Data Capture), or abundant re-use of entities for a Business Rule Vault, should consider an Anchor Vault.

Strong Anchor Vault

With the Strong Anchor Vault independent foreign keys (foreign key elements not part of a candidate key) get their own many-2-many link. This means we transform one-to-many relationships (foreign key references) in the source to many to many link in the Data Vault. Naturally many-to-many relationships also get their own link. Changes to cardinalities, higher order relationships and relationship inheritance can be elegantly modeled this way. Because each independent foreign key must have its own link (together with its own transaction satellite) we create considerably more join paths. You also need to change your end dating process. Note that I do not consider horizontally grouping these many-2-many links together is a good idea (but elaborating on that point merits another post). Note that “merging relationships” (actually subtyping relationships)  with different cardinality becomes possible (but not required). Either over time or over similar relationships (through abstraction).

A tool that uses strong anchorization is Quipu (www.datawarehousemanagement.org)

Weak Anchor Vault

The Weak Anchor Vault differs from the Strong Anchor Vault by creating foreign key links (end dated links) for independent foreign keys relationships (they actually resemble satellites holding references to other Hubs). These can either be singleton or grouped. Here we track changes to Anchor references over time. We can group/ungroup independent foreign keys across foreign key Links. This decreases the number join paths and load complexity in comparison with the Strong Anchor Vault, but flexibility is slightly decreased. This and similar styles have been in use for OLTP systems for many decades to implement bi-temporal operational information systems. It has been independently advocated and implemented by several people like René Veldwijk (Veldwijk's HTC: http://www.faapartners.com/downloads ), Harm van der Lek (Generic Data Vault) and many others under a variety of different names. BIready (www.BIready.com) is a tool that internally uses a weak Anchor Vault.

Elementary Anchor Vault

The elementary Anchor Vault re-factors each element into a separate entity. This means that each attribute has its own satellite (singleton satellites and singleton foreign key links). This can be done on a Weak or Strong Anchor Vault. The effect is more or less an Anchorized and temporalized version of 6NF. It is still not an Anchor Model because for that you need to refactor all key attributes/relationships into their own entity.

Related modeling techniques

There are several related modeling techniques to Data Vault. They all are concerned with temporalization and (key) separation. Non-temporal (key) information is stored in entities that are used as 'Anchors' for temporal entities. I call this class of temporal modeling techniques 'Anchor Style Temporal Data Modeling' (not to be confused with Anchor Modeling, which is just one variety).

Next

In the next posts I will discuss the different styles in more detail and show some examples.