The “catch” with data warehouse automation tools

During my evaluation of several data warehouse automation tools such as BIReady, Quipu and RapidAce, I have come to some sort of conclusion that is crucial to the success of using these tools.

As most of these tools take the source data models as a starting point, you better make sure it is correctly modeled. Even with BIReady that takes a “business” model, you need to have a good model. By correctly modeled, I mean that preferably your source is modeled according to 3NF. When reverse engineering an existing database model, make sure primary keys and foreign keys are defined.

If not, you can be sure that the resulting generated data warehouse (datavault) models are pretty worthless.

I noticed this when using some of the tools on a source model that I have at hand from a client. This model is basically based on flat wide files loaded into (flat wide) tables. Primary keys are sometimes not defined. Foreign keys almost do not exist at all. Normalization is not done.

You can argue whether this is a true source model. It is not, that is true. But it is all we have. A situation that you will probably encounter very often.