Tag Archives: Business Intelligence

Certified Anchor Modeler

As of today, I am certified as Anchor Modeler. My thanks go to Lars Rönnbäck (UpToChange.com), the best teacher you could have, as well as Juan-José van der Linden for inviting me and to Essent for hosting the course.

While the community of Anchor Modelers is still quite small, it will likely expand as the concurrent-reliance-temporal model is extremely interesting. The notion of positors and reliance combined with the positing and changing time is quite advanced. I’m looking forward to combining this with Martijn Evers’ notions about timeline choices with respect to Consistency/Accuracy/Availability.

DataVault Cheat Sheet Poster v1.0.9

This poster displays the most important rules of the Data Vault modelling method version 1.0.9 on one A3-size cheat sheet. I decided to not add personal interpretation and keep the sheet as close to the original specs as possible.

You can find the rules that were used for this poster on the website of Dan Linstedt.

DataVault Cheat Sheet v109 (A3) PDF

A version where the Colors of the Data Vault have been used, is available as well:
DataVault Cheat Sheet v109 (A3, color) PDF

Time Dimension Generator v1.41

In a data warehouse we often use a table containing information about dates. This is called a Time dimension (it should rather be called a Date Dimension, but let’s not quibble). This table is used to deal with calculations regarding time in SQL queries a bit easier. It enables us to attach meaning to dates as well, by letting us add attributes to them. Using time-shifted columns in the table we can then say things like “give me all the sales of the same period last year, for period X to Z”. Given the right query, of course.

This tool generates a table you can load into a data warehouse. For more information, please see http://en.wikipedia.org/wiki/Dimension_(data_warehouse) (it needs improvement but it’s a start). Note that it is available in the Dutch language only, due to specific features that depend on the Dutch local calendar (like working days). I may (on request) rebuild it for international use, but please check the following alternatives first: http://it.toolbox.com/wiki/index.php/Create_a_Time_Dimension_/_Date_Table or http://www.ipcdesigns.com/dim_date/.

Start the Time Dimension Generator (Dutch language version only).

Templator v1.0

This tool provides a template-replacement-service. You provide a template with variables, a signle line with the names of the variables and an number of lines (say, N) with replacements (CSV-format). The result will be N templates, with all the variables replaced by row 1..N from the replacements. Those templates will be present in a single download.

What do you use it for? Well… currently I use it as follows: create an ETL-workflow in BODS. Export the workflow to XML. Replace the items that are variable with variable-names. Save this as a template. Now, when a new workflow is necessary, it is just a matter of copying the variables, their replacements and the template into the Templator. Then, run it and use the download as an import (after removing superfluous begin and end-tags).

It works best if you have highly similar flows based on highly similar inputs that differ only by up to ten variables. Beyond that, it tends to become more cumbersome to gather the replacement values than to copy and paste the original workflow. Your mileage may vary, however.

Start the Templator or the Dutch language version.

Form follows function

The required functionality determines the form of a data warehouse. This presentation aims to show how the form and architecture of a given data warehouse are determined by the challenges a company will meet when changing data in information. Solutions for those challenges shape the data warehouse, both technically and organizationally.

Please note that the presentation is in Dutch.


Deze presentatie gaat over de manier waarop de vorm en architectuur van een data warehouse worden bepaald door de uitdagingen die een bedrijf ontmoet bij het veranderen van gegevens in informatie. De oplossingen voor de uitdagingen geven het datawarehouse zowel technisch als organisatorisch vorm.

Deze presentatie kan tegen reiskostenvergoeding worden gehouden en duurt ongeveer 45 minuten.

Download the presentation (make sure to check the license before using it)

Anchor Modeling

Anchor Modeling is a new method of modeling a domain in a database. The method splits up all the attributes in their own table. This seems complex, but this actually simplifies maintenance. Furthermore, the method is flexible, quite resilient to change over time, does not need updates and is highly scalable.

These are good properties for a data warehouse model. In the article I explain how Anchor Modeling works and why you should at least take a look at it.
The article appeared in november 2009 in Database Magazine, Dutch magazine for database professionals. However, the magazine is now defunct and superseded by Business Information Magazine.

Download the PDF

Reasons for failure in data warehouses

This article discusses the reasons why some data warehouse projects fail. The focus is on the question whether the resemblances to standard IT projects may be greater than the differences, and where the differences could be found. A number of guidelines are given that help to recognize and prevent project failures.

Original publication in Juli 2009, reworked in September 2009. Please note that the article is in Dutch.

Faalfactoren bij Data Warehouses

Dit artikel gaat over waarom data warehouse projecten falen. Het focus ligt op de vraag of de overeenkomsten met gewone projecten misschien groter zijn dan de verschillen, en waar eventuele verschillen in zitten. Er worden ook richtlijnen gegeven om die extra faalfactoren te herkennen en te voorkomen.

Oorspronkelijk gepubliceerd in Juli 2009, tekst licht bijgewerkt in September 2009.

Download the PDF