Operational Data Vault

Q&A:ODS as a Data Vault – working with BI EDW?

This is a very short entry that discusses Operational Data Vault (or ODS modeled in a Data Vault method).  There are a lot of moving pieces to this concept, this is one thing that I encourage you to seek my assistance with – it is NOT easy.   If you have comments, thoughts, or other experiences I encourage you to add your COMMENT to the end of this posting.

How does the ODS in a data vault model implementation work with the BI EDW?

ODS in a Data Vault Model implementation is similar to Operational Data Warehousing. The Data Vault model can be effectively utilized in the context of building an ODS (operational data store).  That said, there are some caveats, changes, and things to be aware of if you are going to go this route.

#1: the Data Vault model (v1.0 and v2.0) are targeted at capturing history.

The ODS – typically only captures transactional history, all other information (generally) that it captures or is supposed to capture is up to date, and contains no history.

Because of this, the DV  model (if utilized as an ODS) needs to be slightly modified as follows:

  • the load date in satellites, is moved out of the PK and in to the attribute list
  • the load end date in satellites disappears
  • more often than not, the links “act” and are modeled as transactional links.

This satisfies the “no history” clause, as well as the transactional history clause of the pure definition of the ODS.

Why “no history” in the ODS?

Generally, the minute you PUT history (descriptive in nature) in the ODS, it immediately reverts to the data warehouse world.  Why? because it inherits all the problems of meshing, managing, producing a historical view for queries – and it will over time, as it collects history, cause problems with query timing.

Please do NOT confuse the ODS with the Data Warehouse and the nature of their tasks.  Even “limited history” in the ODS (like the past three days etc…) can cause issues with synchronization, and make time based queries problematic.  (I will explain this shortly).

#2: the ODS isn’t an ODS unless it is capturing & producing (in real-time) enriched operational transactions.

Let me explain…  A Data Warehouse (particularly a Data Vault), generally is NOT an operational data warehouse (although as of 2010, these situations are becoming more and more common).  So in this case, the ODS is meant to be the “mother of all source systems”, ie: a landing and enrichment zone, for all source systems to reconcile their data set.  This, oddly enough, blurs the lines between an ODS and what a Master Data system is supposed to do.  (the differences here are not the focus of this post, however I will say this: master data isn’t master data without accurate and up to date metadata lineage and forced source system alignment).

#3: Objectives for data access might be different between the ODS and the Data Warehouse, and variable query performance *may* be an issue

Generally, the requirements for an ODS are different than a Data Warehouse, and occassionally (although less-so these days), the hardware and software underneath can struggle with performance in responding to ad-hoc queries for both “analytical deep data over history” along side of the typical ODS ad-hoc query of “get me the most recent data across the board for this business subject.”

These are just some of the pieces that make an ODS “different” from a Data Warehouse (any data warehouse, Data Vault or not).  However, all of that said, let’s now chat about how & why you CAN integrate the two by leveraging today’s technology, and a single Data Vault model.

Enter: the Operational Data Vault System

The ODV (or operational Data Vault) is, first and foremost, and operational data warehouse.  It inherits all the requirements and all the necessary restrictions that a true operational system has – including but not limited to: up-time, resiliancy, reliability, hot backup & restore, redundancy (at the system level), resource loading, and guaranteed query performance response times.

That said, as long as you accept these principles, you CAN (and many systems have done this since 2012) build a single Data Vault model that houses BOTH the “ods” view of the business AND the data warehouse view(s) of the business.

This is called the operational data vault.

Due to the fact that the Data Vault standards dictate that we store the lowest level of grain, AND raw data (unmunged, unchanged) data in the vault, we can then proceed to hook it in as a point of  capture and historical storage for all operational data.  What that means business process-wise is that the ODV *must* be enabled and hooked in to the message queues, to receive & SEND real-time data / exchange real-time data with the operational systems directly.

Once this has been properly accomplished, the transactional data across the enterprise can be subscribed to and enriched when needed, by the operational data vault / operational data warehouse.

Now, what would stop you from doing this? or succeeding properly?

For starters, the infrastructure of the organization – if it is incapable of delivering and/or utilizing real-time message queues, then the point of an ODS (data vault enabled or not) is really a moot point.

Second: the systems capabilities which would house the Operational Data Vault.  If these systems are taxed, overflowed, or simply can’t perform with guaranteed response times in an operational sense (because remember they are collecting operational data with a full historical timeline), then an ODV may seem like a good idea, but in fact, will be detrimental to the organization – BECAUSE the organization is unwilling or unable to “turn the entire BI/EDW in to a fully operational system” with all the requirements.

Please note, that is all the time I have for now to discuss this subject.  If you are interested in additional details, please contact me.  I’d be happy to help you set this up in your organization if you are interested.

Hope this helps,

Dan Linstedt

Tags: , , , , ,

No comments yet.

Leave a Reply