Bootstrapping a Dimensional Mart

View previous topic View next topic Go down

Bootstrapping a Dimensional Mart

Post  jsperson on Mon Aug 29, 2011 2:22 pm

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?


Last edited by jsperson on Mon Aug 29, 2011 3:30 pm; edited 1 time in total (Reason for editing : clarified ETL process)

jsperson

Posts : 2
Join date : 2011-08-29

View user profile

Back to top Go down

Re: Bootstrapping a Dimensional Mart

Post  ngalemmo on Mon Aug 29, 2011 5:13 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Bootstrapping a Dimensional Mart

Post  jsperson on Mon Aug 29, 2011 5:41 pm

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!

jsperson

Posts : 2
Join date : 2011-08-29

View user profile

Back to top Go down

Re: Bootstrapping a Dimensional Mart

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum