Information-Driven Business
How to Manage Data and Information for Maximum Advantage

Robert Hillard

The evolution of the data warehouse data model
by Robert Hillard

When Ralph Kimball wrote “The Data Warehouse Toolkit” (published 1996) it defined Dimensional Modelling in a way that immediately demanded attention by data warehouse practitioners worldwide. The book and the techniques it described were not new and were consistent with the approach we had used for the better part of a decade, what the book did do that was foundational was to describe the approach in a consistent and considered way with a terminology that could be used by everyone.

There are many similar challenges that data warehouse designers face on every project. For instance two challenges we are often called upon to decide how to handle changes to source system models and the proper handling of changes to reference and master data.

The former is usually handled by splitting logical entities when creating physical tables separating attributes and relationships that have a higher probability of changing. The latter is commonly handled in one of three ways. One method one sees non volatile and volatile attributes are split into two tables (with a one to many relationship).  Another method has the current attribute values are held in one table with changes over time maintained in a second table (again one to many). The final common approach has changes across a number of concepts tracked in an audit table which is only intended for forensic purposes.

In my book, Information-Driven Business, I spend considerable time explaining the underlying approaches to modelling structured data and to measuring the benefits of different approaches.

Over recent data warehouse projects, we are using a variant of the first method of handling history that has been formalised as “The Data Vault”.  The Data Vault techniques put forward by Dan Linstedt formalises both of these issues and makes sensible design recommendations. In particular, it adopts an approach using “hub”, “link” and “satellite” tables.

Originally, there was an attempt to patent these concepts, but this application was rejected and he has now been able to adopt a free approach and is promoting his concepts through books, training and his web site: www.danlinstedt.com.

comments powered by Disqus


blogThe Information-Driven Business blog is published monthly:

   2018   2017   2016   2015   2014
   2013   2012   2011   2010


Also featured from the Information-Driven Business blog:

Fashioning the future
We know that the future is coming, but it is sometimes hard to imagine what it will look like. The very clothes we wear are a good place to start, after all the industrial revolution was arguably fashioned by the … Continue reading

Navigating the future with lifelong learning
Flourishing in the future of work requires all of us to embrace lifelong learning. But you can’t train for jobs of the future when you don’t know what you don’t know. While most discussions on education concentrate on funding, it … Continue reading

Opportunities beyond startups
Is it just me or has the world gone mad for startups and writing software? Don’t get me wrong, I am a big fan of startups and all that they bring to the economy. However, if you read the business … Continue reading

Email works too well
Everyone who regularly feels overwhelmed by their email would agree that there is a problem.  The hundreds of articles about the issue typically make the same assumption and are wrong. Writer after writer bemoans email as inefficient and an obstacle … Continue reading

The Internet was a mistake, now let’s fix it
Each generation over the last century has seen new technologies that become so embedded in their lives that its absence would be unimaginable. Early in the 20th century it was radio, which quickly become the entertainment of choice, then television, … Continue reading




© 2010-2018 Robert Hillard