Data Management & Decision Support

Why Data Vault is supported by all ETL tools

Recently a discussion raged on LinkedIn regarding the 'ETL tools that support Data Vault OUT OF THE BOX' (link). I gotta be honest - I was annoyed by the discussion and was stupid enough to display this  by commenting kind of harshly. I would like to apologize to everyone and especially to Daan.

In this blogpost I would like to explain my point of view regarding this question. 

In the above mentioned discussion I commented very briefly 'All ETL tools support Data Vault". Allow me to explain this by paraphrasing an argument that was also used by Daan in the subsequent comments. He mentioned that technology brought about efficiency gains in the last 20 to 30 years. I agree with that, the data is quite clear about it ;-). Trying to explain these gains I leave to applied science, but I would like to take one tiny piece of the puzzle and put it in the context of my remark that 'all ETL tools support Data Vault'.

One of the 'variables' in the function of this tremendous leap - in my opinion - is uniformity. Organizing uniform systems (I use the term 'systems' in the broadest sense - People, Technology, Processes) opened the door towards repeatability, predictability, limiting waste and improving quality. In writing this I think Dr. W. Edwards Deming would agree with me.

Now, back to the subject of ETL and Data Vault. With Data Vault we design the system of modeling and logistics of data in advance. Bothg go hand in hand. What we want to achieve is uniformaty as much as we possibly can. Uniformity in modeling, balanced with the uniformity in loading. 

Let me elaborate some more.

In Data Vault and more generally speaking in 'systems thinking', all objects in a system are interrelated. How I construct a data model has a strong impact on the way I (can) construct the loading (ETL). With Data Vault we standardize the data model as much as we can (there are quite some heuristics in Data Vault, it should not be applied in some dogmatic way), in a limited number of constructs (hub, link, sat). But we also design the loading constructs, which are also extremely limited in number (hubload, linkload, satload). Every load construct has got a standardized pattern, see the figure regarding the pattern for a hub load.

If I were to translate this to SQL it would be something like: INSERT <distinct values> to HUB where NOT EXIST in HUB. Of course any ETL tool would support such a simple construct! Data Vaults are thus being build with SSIS, Informatica, InfoSphere, Business Objects, Pentaho,SAS etc...

Please be advised that in real live Data Vault implementations the above is a simplified example. The principles however remain unchanged;

- A limited number of loading patterns

- The patterns are standardized in type

- The patterns are simple

- The patterns can be executed asynchronous

- The type of patterns can make use of parallel loading

I would like to summarize the above with two words; uniformity and automation. Because of uniformity in modeling and logistics we open the door towards repeatability/automation. Making it a lot cheaper to maintain, but also easy to change or supplement (testability is designed in the system, as well as repairability). Agile software development find great support by these kinds of systems (this is worthy of an entirely new blogpost ;-)).

We now can design a predictable system of loading data in a data model. We have created a uniform structure of the data in the data warehouse, opening the way for more uniformity towards Kimball datamarts as well (be it in-memory, on file, virtualised, etc..).

Uniformity and automation have ignited a wave of innovation in the Netherlands. Innovation led by independent consultants and consultancy firms - that saw great opportunity in the daily problems they face - to take the data logistics to a new level of automation; metadata driven ETL.

(want to know more - come to the Data Vault automation seminar, October 6. Various suppliers of Data Vault automation software are talking and showing their stuff. The organizers decided to keep entrance fees extremely low (100 euro) in order for everyone to contribute, learn and improve themselves and the industry - register now)  

 

 

 

 

For the love of Data (Quality)

"Cleaning the lake or reducing the pollution from the factory" - is an analogy used by Thomas Redman. It perfectly paints a picture of data quality issues 'we' all face in our data management projects. 

In projects we often have to struggle against forces that 'just wanna create the freekin report'. Whether the data is wrong is of no concern. In these instances the goal apparently is the information system ('our DWH is running', 'The report is build' or 'SAP is live') and not the data. Put in other words; data is often treated as a by- product and the information system is the main product.

Lets take a closer look - by using (among others) Richard Wang's analogy with a manufacturing process¹:

I have never seen any manager, CEO or foreman happy with a succesful implemented assembly line but a lousy product. Have you? In software engineering I sometimes have the feeling we lost touch....

The Information System - be it a Data Warehouse, a report or an ERP - is not the purpose, it is a means to an end. And the end should at least be sufficient data quality (where data quality is defined in the perpective of the customer, fit for his/her task). 

The cool thing about the (somewhat) oversimplified analogy of Richard Y Wang is its usefulness for another reason. It stresses the system perspective you gotta have on dealing with data quality issues. You cannot go about 'cleaning your lake while the factory is still polluting'. Producing quality Information Products is executed by means of a system. Now, do not translate this 'system' into 'information system'. This system consists of people, processes and technology. Dealing with data quality issues requires a system perspective to really add value in terms of better quality products and a 'greener' environment.

So - do not blame your ERP department for creating bad data

So - do not blame your report builder for creating useless reports

So - do not blame the person entering the data

Maybe, something to consider; who do you think is accountable for organising the 'system'? Yes - management should embrace quality in its DNA...

I know I am corny - management should have read Deming, Juran, Crosby in their MBA's. Knowledge that is like half a century old.

 

^{1 - Richard Wang - A Product perspective on Total Data Quality Management - feb.1998, Communications of the ACM}

Data Vault Introductions - Download

For those who want to familiarize themselves with Data Vault, I hereby offer the links to some papers I wrote (sometimes with others) and a presentation I gave recently (see other post for details):

Published originally in Dutch for Database Magazine, an article that is the first part of a triptych regarding the 'Next generation Enterprise Data Warehouaing', this part was an introduction into Data Vault named 'Letting go of the idea of a single version of the truth'  - August 2008 - Download

Published originally in Dutch for Database Magazine, an article that is the second part of a triptych regarding the 'Next generation Enterprise Data Warehouaing', this part deals with the post-Data Vault processing, the business rules in particular - November 2008 - Download

Published originally in Dutch for Database Magazine, an article that is the final part of a triptych regarding the 'Next generation Enterprise Data Warehouaing', this part deals with 'Development processes in Data Warehouse environments' - June 2009 - link

Published in Belgium for BI-community.org, a keynote article titled: Data Vault, Business Objectives for next generation data warehousing - January 2011 - Download

And finally a link to the presentaion I held on the Advanced Data Vault seminar, May 5 & 6 in Baarn, the Netherlands: link

**July 2nd,2011 - Tom Breur wrote a good piece "Tom's ten data tips", very much Data Vault related.

Advanced Data Vault Seminar May 5 & 6 2011

On may 5th and 6th Dan Linstedt and myself hosted an Advanced Data Vault seminar, excellently organized by DIKW-Academy. I think the attendees got good value for their money. Value, no training could ever generate. 

We had great (international) attendance and very good presentations of mainly the engineers who acutually do the work and lead the innovation.

The level of adoption is now growing at a speed which is both exellent, but also worrying. The latter mainly because we - the DV standards committee - see an increasing rate of failed Data Vault projects mainly because companies/consultants underestimate a Data Vault based architecture.

It is Intriguing that the seminar was attended by DV-user-companies, independent consultants and DWH boutiques. There was NO attandance of the big service integrators, who (btw) do a lot of Data Vault implementations....Either they think they get it (ahum) or their management is not willing to invest (the latter I heard a lot). 

For my presentation on the 6th - here's the abstract:

Data Warehousing is still being ridiculized by popular literature and opportunistic vendors (and sometimes analysts) in the Business Intelligence domain - they tend to call it 'traditional' as opposed to their 'silver bullet technology'.

However, data warehousing has evolved and Data Vault - although undervalued by many - is fueling this evolution. Data Vault methodology enables architects to (finally) embed data warehousing into the Enterprise Architecture, something they struggled with the last 15 years. In the Netherlands, Data Vault sky rocketed innovation in the data warehousing scene. Accelerators in terms of frameworks and software are being build by experts in the field and several product vendors picked up on it. The presentation of Ronald Damhof will briefly discuss the evolution of data warehousing as it stands today, the position of Data Vault in the Enterprise Architecture, the different species/forks that exist in Data Vault and the automation that comes with it.

This presentation is of course only slides - the dialogs complete the presentation. You want the dialogue? 1) You should have been there 2) Keep on eye on the DIKW-academy website or follow me on Twitter (@RonaldDamhof).

The presentation can be viewed here.

One time event with DIKW, Dan Linstedt and friends

Data Vault training is proliferating big time and I tend to frown my eyebrows regarding the quality. I started the hype (;-)) in Data Vault in 2007 at the Tax Authority and invited Dan Linstedt to the Netherlands to come, train and certify us. 

After that memorable session with 27 tax people I decided together with Dan Linstedt, to organize more training sessions. So I involved DNV (CIBIT) and we organized another extremely memorable session with 50 (!!) seniors in the field of data warehousing. People like Michael Doves, Martijn Evers where there at the time.

Those were the times.....

I have parted from these training days - the repitition was killing me. I am just not cut out to be a teacher in this regard.

We have come a long way -  Data Vault has arrived and innovation is booming in the field of metadata & model driven data warehousing. Standardization, industrialization and thus more professionalism in our field is improved big time. 

But....

Those that actually implement Data Vault know that reality is not the same as Power Point. There are hard design decisions and trade offs to be made. We encounter bitchy sources, "architects" ;-), "is it a standard or is it not", and all sorts of hard architectural, modelling and logistics challenges.

DIKW asked me whether I would be interested in a one-time event with Dan Linstedt that covered either more advanced subjects or stories from the battlefield and how they/we solved it. I liked this approach of DIKW and asked several of my expert collegeas whether they would be interested in joining me and Dan at this event.

It is going to be extremely interesting; good topics, hard topics, nerdy topics ;-) 

Join us at DIKW, may 5th and 6th 2011. 

 

 

Dan Linstedt & Ronald Damhof; lets be clear about the Raw Data Vault

Last week I wrote a post on the Raw Data Vault that got some good insightful comments. This post is a joint effort of me and Dan Linstedt regarding this subject.

In his book – published this week - Dan mentions a Raw Data Vault as well. We have discussed this and came to the conclusion that the Raw Data Vault as mentioned by Dan in his book is in fact the actual DV (integrated on the HUB’s, using business keys). He used the term ‘Raw’ to distinguish from the Business Data Vault.

Let us be clear; the Raw Data Vault as described in my blog post “Data Vault Schools” is not the same Raw Data Vault as described in Dan’s book. In fact it’s a fundamental difference with regard to DV methodology as Dan intented it. This is in line with my blog post "the case against the Raw Data Vault".

We both agree that there is no way to generate everything, because identification of the business keys has to happen. We do however acknowledge the possibility that, if you can specify the business keys, there are options to generate the model.

Ronald Damhof & Dan Linstedt

 

 ***update***

The first two comments on this post are valid and we felt to be more precise on the terminology. 

1) Raw Data Vault = A term that should no longer be used in DV methodology. If it is used in formal writings, communications, blogs or whatever, then it resembles a  Data Vault (integrated on the HUB’s, using business keys etc..) as defined by Dan Linstedt.

2) Staging Vault (thx Bas)= A DV style model - 100% generated from the source. It is not part of the DV methodology.

I will also update my other 2 posts to reflect this terminology.

DV - the case against a Raw Data Vault

My blog "Data Vault schools" got quite some attention. My goal was to open up the discussion on the mishmash of terminology that plagues the Data Vault community. I seem to have hit a nerve. The comment of Dan Linstedt on this post was (nearly ;-)) spot on.

Now, let me stir up some more ;-)

The confusion is centered around the terminology of the Raw Data Vault and the Business Data Vault/EDW+/Staging Out. In his comment Dan is basically differentiating the terms into two layers: Raw Data Vault and Business Data Vault (@Dan; correct me if I am wrong).

Now, here's the kicker. What is meant with Raw Data Vault? Here in Holland (BI) service providers are selling data warehouses like they are selling fitness appliances - "in 2 weeks you lose 10 pounds!! - buy me". Or to put it in wording of these service providers - "in two days we can build you a (data vault) data warehouse! - buy me".

What these guys are doing is generating a data vault model directly from the source. It's 100% source driven. The loading scripts are also generated of course. This scenario basically follows the "Raw School" as I called it in my previous blog. I sometimes have the feeling that "generating" for these guys have a become a goal in itself.

But what do you have?? In my opinion you are now the proud owner of a copy of the source in a screwed-up structure. If you want a copy of the source - why not copy the source? If you want history with it, why not create a persistent staging where the structure of the source is kept? I really do not see any added value in making Raw Data Vaults as postulated in this paragraph. Please comment if you disagree.

But let me strengthen my case a bit. In general one can say that it is our job to propagate (meta)data in order to overcome a semantic gap between the authentic source and the user (see figure).

 

 Now, what happens if you make a Raw Data Vault? Every source is copied into a new structure, there is zero integration, zero business rules. Is the challenge of conquering the semantic gap any closer? No, in my opinion it is not. There is an additional argument against this kind of Raw Data Vault; existing interfaces, reporting, queries that exist on the source are now more difficult to port (the structure has changed) as when compared to a persistent staging.

Next - the Business Data Vault. I do not agree with Dan Linstedt that Business Data Vault, EDW+ and staging out all are the same. Let me be more specific; EDW+ is a term I coined in November 2008 in an article (Download Damhof_DBM1108_ENG[2]) me and Lidwine van As wrote. Although we called it CDW+ we also used the term EDW+. The term "staging out" was (I think) first used in an architecture of one of the most successful data warehouses in the Netherlands (Retail company Albert Heijn - http://www.grey-matter.nl/pubs/AH_Pallas_NL.pdf). Although they initially called it a Data Staging area downstream, it was renamed to "Staging Out".

Let me be clear; logically, the EDW+, CDW+ and Staging Out have similar objectives. They are architected for reasons of re-useability of business rules (and its outcomes) or shared dimensional structures, maintenance and auditability. 

The Business Data Vault is only a valid term in the "Raw School". Since this school still got to overcome 100% of the semantic gap they need to "Data Vault the Raw Data Vault" (you guys with me still?).....

In my opinion there are four (optional) logical layers: (1) Staging - either persistent or not, (2) Data Vault, (3) Staging out/EDW+/CDW+, (4) datamarts. 

The usual disclaimer: just to separate between logical and technical architecture, especially with regard for the datamarts this is important. Datamarts for example can be cubes, virtual, pushed in memory or whatever. 

 

***update***

The term Raw Data Vault as used in this post should be translated to Staging Vault. Conform a blog post I wrote with Dan Linstedt regarding terminology.

Lets demystify the BICC

It is an old subject, I know - it has been talked about for decades (I feel old). But I still have a lot of client interactions that result into a discussion about the BICC (Business Intelligence Competence Center) - so it still seems to be actual.

Let me be blunt; the BICC, positioned as a central organizational unit (either virtual or not) that covers all tasks from data to product, is ridiculous. There are still strong forces in BI-fantasy-land that advocate a monopoly for the BI competence center ("BI is sooo special it need to be organized special"). Often I hear the BICC as being some kind of old-fashioned politburo that centrally plans and controls data delivery and function delivery (be it reporting, analysis, mining or whatever). 

It is a hoax - aint it?

Lets demystify the BICC a bit and focus the discussion instead on ownership - the remainder can be derived (responsibilities, organizational design). 

Ownership

- Who owns the business process of information usage and exploitation. I always like to have clear ownership in this regard, preferably the CFO or the CIO (not the CTO!). This ownership it to be operationalized centrally as well as locally. With local I mean that the ownership is delegated to the various business processes that have a strong informational need. For example; Risk Management, Process Optimization or Customer Loyalty. These local entities need to be empowered with facilities to accomodate their own business processes with information. The BI community tends to define this as self service. With central I mean the part of the ownership that entails shared processes with regard to Information Management; account management for the local entities, business vocabularies, data quality issues and findings, consultancy to local entities,etc..

- Who owns the technical system and the infrastructure? In most cases this would be ICT management. Whether his shop runs business intelligence, business transaction systems or all kinds of middleware - he needs to worry about all kinds of ITIL and ASL defined processes. Again, he can also delegate his responsibility to either an exploitation manager and an application development manager. 

With this division in ownership we basically orchestrated the start of demand and supply. Where supply and demand meet, price and quality is negotiated. I like that....

 So lets summarize, we got:

• an owner of the process of information usage and exploitation
• the delegated entities of this ownership with a local and a central part
• the owner of the technical system
• the delegated entities of this ownership

Have I coined the term BICC yet? Nope....

Does it exist? 

Well, if it does; the central part of the process owner would probably best describe it.

 

Data Vault schools

Lately I have been involved in many discussions- online as well as off line -  regarding Data Vault architectures. It seems that three ‘schools of thought’ are developing in the Netherlands. Separating and recognizing these three schools is important.

For the folks not familiar with Data Vault – google it, read one of my articles (e.g Download Damhof_DBM0508_ENG) or go to http://danlinstedt.com/ .

-       Raw school; this school of thought makes a distinction between a raw data vault and a "business" data vault. The raw part is a copy of the source where entities are basically split up in a Hub, Satellite or Link construct. The structure is derived (nearly 100%) from the source structure and no integration is yet achieved. The rules for determining the target types are pretty fixed and can therefor be automated.  Of course the associate loading scripts can be generated. This is a pretty straightforward structure transformation. One can argue whether or not this is some kind of persistant or historical staging environment. The "business" data vault (I do not like this terminology - but to distinguish it from its raw brother I use it here) is where the business rules are executed, where integration is achieved and where the result is stored. This part of the data logistic is typically a value-transformation and not so much a structure transformation.This school advocates that making datamarts is again a structural transformation (which can be automated, business rules ae already executed) to star- or snowflakes.

  

-       Classic school; this school of thought advocates a strategy where the Data Vault model is designed according to a mix of the conceptual data model of an organization and the structure of its business transaction systems.Going into te Data Vault there is a light integration of data on the main business entities of the organization (based preferably on the conceptual data model). Designing the Data Vault data model is a creative process, loading the model can be generated. Business rules are executed going into the datamarts/downstream to the enduser. The later is a structural and a value transformation. This school of thought resembles the ideas of Dan Linstedt’s original methodology the closest.

 

-       Mixed school; this school follows the classic school by designing a Data Vault model based on both the conceptual data model of the organization and the structure of its business transaction systems. It does however modularize the downstream business rules execution. The main differentiator for modularizing is maintenance, re-usability and auditability. When business rules are considered to be generic it might be wise to only develop it once and execute it many times. It might also be wise to audit the changes of the outcome of business rules over time. The staging out drafted in the figure below is modelled Data Vault style. 

Just to separate between logical and technical architecture, especially with regard for the datamarts this is important. Datamarts for example can be cubes, virtual, pushed in memory or whatever.

In my next blog I will deliberate on the implications of these 'schools' regarding data logistics and the 'let's generate it all" lobby....;-)

 

***update***

The term Raw Data Vault as used in this post should be translated to Staging Vault. Conform a blog postI wrote with Dan Linstedt regarding terminology.

 

 

 

 

Change always comes bearing gifts

This post has also been published on B-Eye-Network

A story.....

• Vendor X sells its ERP to a company in Healthcare;
• Client wishes to setup its informational environment (data sharing, BI, CPM etc..) right from the start;
• Vendor X pushes the 'standard' solution' they sell;
• Client decides to decouple their informational environment from its source(s) for several reasons (heterogeneous sources, sustainability, compliance, adaptability etc..);
• Vendor X deploys their ERP;
• Client starts to design and build the informational environment;
• Interfaces between ERP of vendor X and the informational environment are developed;
• The ERP of vendor X off does not offer functional interfaces ('X keeps pushing their standard product'), so client needs to connect on the physical level;
• Going-live is near; of both the ERP and the new informational environment

And then change management of vendor X regarding the ERP kicks in.

Client: 'What's your release schedule for patches'?

X: 'Every 2 weeks' 

Client: 'Huh'?

Client thinks: 'Damn, how can I keep up with this change schedule?'

Client: 'Well, can you tell me anything regarding the average impact of these patches?'

X: 'Well, they can be very small and very big' 

Client thinks: 'Ok, what are you NOT telling me' 

Client:'Ok, but this ERP is like 15 years old, so give me an overview of the average impact'

X: 'Basically anything can happen'

Client thinks: 'o, o'

Client: 'Ok, but the majority of these changes are of course situated in the application layer, not the data layer?'

X: 'Well..anything can happen.'

Client thinks: 'Is it warm in here?'

Client: 'Anything? Also in the data layer? Table changes, integrity changes, domain type changes, value changes?'

X: 'Aye'

Client thinks: 'Ok - I'm dead'

Client: '...at least tell me that existing structures always remain intact and the data remains to be auditable - extent instead of replace for example'

X: 'Huh'?

Client thinks: 'Well, at least I am healthy...'

Client: 'hmm...just a side note, we use Change Data Capture, I assume that these changes are fully logged?'

X: 'Nah - log is turned off, otherwise we can't deploy the changes' 

Client thinks: '..hmm....is my resume up to date?'

My point; do not assume your vendor (of any system) to engage in professional application development and a change management policy that takes into account the simple fact that data of these information systems need to be shared with other information systems in your company.

Change management and professional application development needs to be important criteria regarding the selection of information systems.