Category Archives: Data Modelling

The Flaws of the Classic Data Warehouse Architecture

This CDWA has served us well the last twenty years. In fact, up to five years ago we had good reasons to use this architecture. The state of database, ETL, and reporting technology did not really allow us to develop something else. All the tools were aimed at supporting the CDWA. But the question right now is: twenty years later, is this still the right architecture? Is this the best possible architecture we can come up with, especially if we consider the new demands and requirements, and if we look at new technologies available in the market? My answer would be no! To me, we are slowly reaching the end of an era. An era where the CDWA was king. It is time for change. This article is the first in a series on the flaws of the CDWA and on an alternative architecture, one that fits the needs and wishes of most organizations for (hopefully) the next twenty years. Let’s start by describing some of the CDWA flaws.

The first flaw is related to the concept of operational business intelligence. More and more, organizations show interest in supporting operational business intelligence. What this means is that the reports that the decision makers use have to include more up-to-date data. Refreshing the source data once a day is not enough for those users. Decision makers who are quite close to the business processes especially need 100% up-to-date data. But how do you do this? You don’t have to be a technological wizard to understand that, if data has to be copied four or five times from one data storage layer to another, to get from the production databases to the reports, doing this in just a few seconds will become close to impossible. We have to simplify the architecture to be able to support operational business intelligence. Bottom line, what it means is that we have to remove data storage layers and minimize the number of copy steps.

Great read from BEye Network. Part 1 and Part 2.

Data Modelling – Pros and Cons

How to insulate an organization against change – Data Modelling. Read more on its Pros and Cons.

Industry experts concur — to a degree. For one thing, says veteran data warehouse architect Mark Madsen, a principal with consultancy Third Nature and author of Clickstream Data Warehousing, what proponents such as Kalido and Sybase mean by “judicious” use of a data modeling tool takes an awful lot for granted.

“It presupposes that if you have all of your systems’ data models in a tool, then changes that are imposed will be easy,” he says, citing system changes, upgrades, and merger/acquisition activity that brings new systems into the fold as three among many common disruptions. “That’s like saying that because you have a map of outer Mongolia, a trip from one side to the other will be a simple matter of driving.”

Madsen isn’t entirely dismissive, just skeptical. “I accept that having the data models together and linked will help things like compliance efforts. For example, Visa [requires] that you control access to all databases that contain credit card numbers,” he acknowledges, “but that also presupposes that the models are somehow kept up to date, something that is generally pretty unlikely and usually a manual process.”