Taking sustainability and agility in data modelling to the next level

Intro

Both authors (Frederik Hofstra and myself) of this whitepaper are involved in a data warehouse project with a university in the Netherlands. This university implemented a new SAP module; Student LifeCycle Management. A comprehensive module covering all processes from enrolling students, to credit scoring, to progress reports, to eventually graduating. This module is also the main source for master data like the academic structure of programmes and modules, as well as the organisational structure (faculties, divisions, subdivisions, etc.) and the student information.

The strategy of the university is to deliver a service to their organisation that is capable of delivering data in various ‘truths’ to people or information systems and with an array of functionality (e.g.: reporting, analysis, etc.).

The solution architecture consists of two storage layers. The first one is based on Data Vault principles and heuristics and the second one is a dataset layer. The latter is the object of discussion in this paper. This solution architecture is currently available in production mode and is - as such - a proven solution.

Abstract

 This paper is all about delivering quality data products. However, quality is by definition a relative construct. Its requirements can only be fixed by the time the user needs the data. Context - as we use the word in this paper - is a function of the person involved at a specific moment in time with the quality attributes he desires. And these contexts change all the time. Accommodating these constant changes is partly covered by continuous innovation in technology. However, the use of innovative technologies does not free us from proper software design. This paper addresses a software design perspective to offer the required agility.

It is a continuing effort in trying to evolve the work of giants like Dr. R. Kimball, W.H. Imnon, B. Devlin, C.Imhoff, D.Linstedt and many many more.

Download 2011 - Damhof, Hofstra - KimballRevisited-Contextisourguide

In my previous post on this topic I talked about  saying 'I don't know' when you do not know how long it takes to finish (say) a project.

But, when pressured enough, engineers are going to guess - and I completely understand!

STOP PRESSURING US.

When planning, if you don't know the answer, don't guess. Explain in your plan exactly what you are going to do to get the answer. Then, when more information is available, update the plan. 

Now, why isn't senior management keen on this? And this is really bugging me, because I want to understand. We - as engineers - really don't know...really...we don't. Why would you insist on an answer you know is not correct at all??

My take on this: because it's not easy. It means that management have to participate throughout the project to make decisions about each tasks as sufficient information becomes available (Agile manifesto principle 4)¹.

¹Integrating CMMI and Agile Development- Paul E. McMahon -2011

Wednesday November 16^th 2011 Ralph Hughes from Ceregenics was in the Netherlands. Ralph is author of the book 'Agile Data Warehousing: Delivering World-Class Business Intelligence Systems Using Scrum and XP'. Ralph is currently under contract to write more books on the topic of agility in data warehouse development.

I had been in contact with Ralph for some time; he wanted to know more about data vault, getting the facts, how it is actually used, what customers use it, how they develop and deploy, how it contributes to agility and how it impacted the business.

Of course, anything can be explained in writing or conceptually, but the 'real proof of the pudding, is in the eating'. Opportunity knocked when Ralph was in the Netherlands for his TDWI course on Agile data warehousing. He asked me whether or not I could arrange some customer visits in Amsterdam. Customers that use and deploy Data Vault and have attained a high agree of agility.

Tom Breur and me were hosts for Ralph and we visited the Free University (client of mine) and BinckBank (client of Tom), both in Amsterdam. Hans Hultgren (Genesee Academy) happened to be in the Netherlands that week and joined us as well. We met with both management and technical team members of the university and BinckBank.

Both clients were particularly interesting because their data warehouses are in production and in a mode of constant change. Both clients showed a remarkable predictability and reliability in coping with these changes. Change equated to 'business as usual'. I remember Ralph asking an engineer 'how long does it take to deploy a new data element to the warehouse?' The engineer replied: 'do you want to know the lead-time including my coffee break?'.

Ralph, Tom, me and Hans were impressed with the accomplishments of these clients in getting their data warehouse deployment in control while constantly adding value/changes to the business in a predictable fashion. 

I will not transcribe the whole interview in this blog - that is simply too much - send me a note if you want to know more. Interesting differences between Free University and BinckBank were the fact that they used different automating techniques and also the level of business key integration differed slightly. Free University used templating (generating XML and import in Business Objects Data Services) for data warehouse automation and the data warehouse was driven by business keys. BinckBank used Quipu for data warehouse automation and the data warehouse was partly driven by business key, and some by surrogate key (see also my presentation on the Data Vault advanced seminar about different Data Vault species - slide 25-30). In terms of software development methods, BinckBank used the Scrum method and Free University was based on waterfall/iterative with lots of lean practises being used.

I will try to summarize both visits from the perspective of me and Tom, particularly slanted towards Agile software development, by asking my blog readers, three questions: 

1. Why is it that you can build and deploy extremely small particles in Data Vault and not in other approaches, without having an increase in the overhead and coordination of these particles? In other words; 'Divide and Conquer to beat the Size / Complexity Dynamic'¹
2. Why is it that you can re-engineer your existing model and guarantee that the changes remain local? Something that is hugely beneficial in data warehouses that - by definition - grow over time.
3. Why is it that - as your (Data Vault based) data warehouse grows - your costs grow ‘merely’ in linear fashion initially, and as you approach the end state marginal growth in cost decreases exponentially (as opposed to exponential cost increase for Kimball warehouses)?

I want to thank Free University as well as BinckBank for offering their time, their energy and enthusiasm to the general cause of knowledge sharing. Of course I want to thank Tom Breur and Hans Hultgren for putting in their time as well. 

My special thanks of course to Ralph Hughes as being an open minded, inquisitive and knowledgeable peer. It was great being your host in the Netherlands. 

^{1 - Gerald M. Weinberg - Quality Software Management - 1992}

Photo #1: Left in the corner sits Ralph Hughes, next to him Tom Breur. On the other side the Free University; Jaap Roos (project manager), Dorien Heijting (Data Warehouse Engineer), Erwin Vreeman (Project Lead).

Photo #2: Sitting with the american flag - Ralph Hughes and Hans Hultgren. At the top of the table - BinkBank: Michel Uittenbogaard (Data Warehouse Engineer) and on the right Paul Delgman (BI manager). 

Photo #3: Sitting near the window looking down: me, myself and I

It is hard to admit that you don't know something - this is human nature. But a lot of the time, the pressures mount up. 

CFO: "Ronald, we need the product to be done asap, when do you think it can be ready?"
Me: "well, I can tell you when we will finish, but I will not know exactly what will be done" (I don't know)
CFO: "Huh? Aren't you the expert? We pay you for being the expert, so spit it out"

Now - ask yourself - what kind of consultant are you? Will you eventually cave in to the pressure and call out a number? Or do you dig your heels and persist in 'I don't know'. If you call out a number; please be reminded that the software graveyard is strewn with the carcasses of partially completed projects that were three to five times larger than anyone dreamed¹.

There is a subtle difference between speed and progress. You can drive with 200 miles per hour, but without a map, your progress will be zero. In other words, you need some kind of 'map'/plan and some kind of thoughtful analysis of the problem you wish to solve.

But even so, a plan is just what it is; 'a plan'. THE PROJECT WILL NEVER GO ACCORDING TO PLAN. Why; because most plans I come across envision some kind of fixed end-result. This end-result is often described by means of requirements. Even if we were to embark on a monumental requirements gathering process where we interview all people involved and write down all quality attributes, we would still not have a complete requirements specification that covers the quality demands of our business users (simply because they themselves do not fully comprehend them²). But  apart from that; such an elaborate process simply isn’t attainable.

If the finish line is not precisely known, the number of miles to the finish line is not known either. So - writing down in your 'plan' that the project will take '42' weeks to finish, is just another way of caving in to the pressure of calling a number. Don't do it.....resist....Do not comply.

¹ Humprey - Managing the Software Proces - 1990
² Which is especially true for data products where the future use of the data is - by definition - not fully known
³ M.Cohn - Agile estimating and Planning - 2006 

I often get asked by clients to help them jiggle¹ their decision support or data management efforts. The pattern that unfolds can be described as follows:

1) As any consultant I always want to know why I am being hired. In other words, I ask: 'what are your current problems?' 'Any new challenges on the horizon?'

2) Client; 'we need new tech/tools'

3) Me: 'Then you don't need me, just write a check to the vendor. But again, what problem are you trying to solve?'

4) Client: 'Look, we have all the tech money can buy (me: I’m not kidding, this actually happens), but it is just not working at the moment'

5) Me: 'Hmm, why is it not working for you?'

6) Client: 'it's expensive, changes take forever, business is extremely unhappy and we keep buying more expensive hardware to increase performance'

7) Me: 'ok, so leadtime, quality and costs are a problem?'

8) Client: 'aye'

9) Me: 'and why do you think new tech will help you out?'

10) Client: 'we think this new appliance stuff - super-dooper-in-memory-shared-nothing-all-in-one-box - can save our asses'

By now, a quote comes to mind from Einstein: 'The significant problems we face cannot be solved by the same level of thinking that created them.'

11) Me: 'so how does this new super-dooper tech stuff help increase quality?'

12) Client: 'ehhhhh'

13) Me: 'Is it an idea to first perform a root-cause analysis? Lets try - together with stakeholders - to specify the problem'

14) Client: "We know the problems - we just need tech'

Obviously I am polarizing a bit, but this pattern seems to repeat itself over and over again. Of course, it could also be me ;-), but I do not think I am that special.

There is a lot of publicly available research which proves that technology is an extremely small part of any IT failure. By far the biggest part of the problem is management itself. More precisely: IT governance, or the lack thereof, usually is the core of the problem. Allowing technology to be purchased without proper IT governance is the same as creating waste.

It would be much better to just donate the money to charity.  

If management in IT is incapable of clearly explaining by whom and how decisions are made regarding the use of IT, then how do you expect conformance to principles? Or how do you expect to attain some kind of sustainable robust/pervasive architecture? How do you expect infrastructure (including shared and standard applications) to accommodate the needs of the many? How do you cope with business needs and how to align it with IT? How are investments in IT justified? Who is prioritizing? Who monitors the ROI (and acts on deviations)? Etc.

Obviously in the pattern I described above, I urge my clients to invest in a proper root-cause analysis and retrospective (what went wrong and how can we improve). Then I will try to establish some leading principles, all in the context of data management and decision support. These leading principles will be translated to an 'organizing logic for data, BI applications and infrastructure, captured in a set of policies, relationships and technical choices to achieve desired business and technical standardization and integration'².  

Technology choices, tool choices, vendor choices can be logically derived from this exercise. And don't be surprised if it turns out that you already have all the technology/tools/skills/people you need.....

15) - client: 'ehh....Ronald, that's a lot of work - are you sure - I just wanna buy tech.'

16) - me: 'aye - I am sure - but don’t be fooled - you, my dear management, are in the center of the problem that needs to be solved. It will be hard work...'

Two scenario's unfold now:

1) - Client: bye, bye, Ronald - I think we need another consultant. This happens to me occasionally, and I am perfectly fine with it. I am congruent with myself in my consulting because I sincerely believe this is how it can work. I would not be congruent with myself if I went along in a technology selection process of some kind or implementing some tool that will never solve the real problem.

2) - Client: ok - lets do this.

1 - A jiggler gets an organization unstuck by providing a small change in how the client sees the world

 2 - IT governance, 2004, Weill, Ross

For those of you not present at the Data Vault automation seminar (oct.6 2011); hereby the foreword of the conference proceedings. We are still debating how we will publish these proceedings - will let you know.

¹ Data Vault was published way before 2007 by of course Dan Linstedt himself and others as well (M.Ketelaars for example)

May 2011 I was given an opportunity to be part of the first group of pioneers that field-tested the IQCP program. IQCP stands for Information Quality Certified Professional and is organized by IAIDQ - the International association for Information Quality and Data Quality.  To be fair; I really do not like certifications. It is said that with certification, clients can have some kind of objective measurement regarding knowledge and skill. Well, for the majority of certifications this is utterly deceiving.

That being said, why did I go for IQCP?

For one thing; IAIDQ is a not-for-profit organisation and vendor-neutral. People involved in IAIDQ are people with a passion to spread the word with regard to quality in general and Information Quality in particular. 

Second; the certification process was extremely well prepared, grounded in known research and adheres to widely accepted standards and regulations such as the ISO/IEC 17024 and the Standards for the Accreditation of Certification Programs, published by the USA National Commission for Certifying Agencies (NCCA, 2002).

Third; I was not mainly interested in the certification as such....To put in poetic; I was interested in the journey, not the destination. The IQCP certification is based on a reference list of books and articles, carefully selected by people who know their stuff. The certification was a ‘carrot on a stick’ to me, pressuring me to actually study and invest time. The deadline was end of July 2011 - I have never in my life read so many books, but I have never in my life been so authentically interested in a subject as well. I just kept reading, because there is so much clever stuff written out there.

Fourth and maybe the most important reason; in may 2011 I also happened to follow the legendary PSL (problem solving leadership) workshop given by Jerry Weinberg, Esther Derby and Johanna Rothman. What I have learned is immense and influences me daily. The root of this workshop is - in my opinion - founded in quality principles; learning, continuous improvement, root-causes, self organizing team, people, communication, understanding the problem, understanding context and so much more. The people I met in this workshop were incredible and a huge inspiration. A lot of the attendees came from the world of context-driven testing and agile software development. Both - in my opinion - strongly rooted in quality principles. 

It somehow all got together when I was asked to participate in the first batch of people to go for IQCP.

For me; IQCP was the means to an end - I have studied amazing books (for a reference list download this pdf) go to written by people like Edwards Deming, Joseph Duran, Jerry Weinberg, Kaoru Ishikawa, Masaaki Imai, Richard Wang and so many more. My library has grown very rapidly lately and I have still so much more to read (learn). Somehow, all I have read, learned and still learning are pieces of a puzzle that fit seamlessly in my field of expertise #datamanagement #decisionsupport #datagovernance #dataquality #datavault #architecture #softwaredevelopment.

The journey never ends....

My gratitude goes to Tom Breur, IAIDQ, Jerry Weinberg, Esther Derby, Johanna Rothman, those crazy canadians ('it is what it is'), Testsidestory, Olav, Markus, Griffin and so many more.

ps. I passed the exam.....I am now an Information Quality Certified professional 

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)  

"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}

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.