Bootstrapping a Dimensional Mart

I'm in the process of designing an ETL process to bootstrap and maintain a dimensional mart. The maintain part is pretty straight forward - load dimensions (mostly type 2) the load facts. The challenge I'm having is bootstrapping all of the existing historical data into the data mart. I want to ensure integrity with existing warehouse data and reflect all historical changes according to the thread that we already have.

This wouldn't be too bad of a problem except that we have a lot of historical data and the existing warehouse is normalized. So, for a given dimension we could have several relational tables changing relative to each other over time.

The best solution we've come up with is stepping through the data day by day and loading it for that "objective date". So, we start at day x and extract the data from the perspective of that date. We then ETL that data into the mart. Then we perform the same for day x+1. For our small data set this works pretty well, but I can't imagine the problems if we were interested in type 2 changes down to the second vs. once a day.

This would seem to be a challenge any time a historical rather than a transaction system is used as a source. Even if you're coming from an existing dimensional model and redesigning it, the same problem would exist.

Is there a magic bullet somewhere?

I'm sure if you spent more time on it, you would probably come up with a process to do it all at one time... but... since it is a one time effort, you are probably better off going with the orgininal plan.

Hello ngalemmo,
Thanks for the reply. In your experience is this not a common situation? Basically I'm just trying to figure out if this is something where I need to suck it up and apply brute force or if there is an elegant solution that I'm missing.

We did actually spend a good bit of time analyzing the challenge and potential solutions. I built some pretty unwieldy queries that threaded the data historically, but they just worked for the simpler cases.

Thanks again!