Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Fact/Dimension Reload Design

Go down

Fact/Dimension Reload Design Empty Fact/Dimension Reload Design

Post  surfinwind Thu Feb 11, 2010 12:38 pm

Hello fellow DW/BI practitioners!

I am in process of designing ETL for an enterprise-wide DW. One of the ideal functionality is to have a parameter driven capability to re-load historical fact transactions along with dimensions. What ETL metadata needs to be present within facts/dimensions in order to design an automated process for such re-loads? ... assuming dimensions are standard type 2.

Here is what my thoughts are:
Both dimension/facts need to have columns for source system effective and end dates. Thus, type 2 dimension can be re-built using these dates. Dimension fact relationship can be built by chaining dimension/fact data on the effective date.

This assumes source system has effective date logic for storing the history.

Any other columns that would help in this design?
Any other recommendations/experiences for handling the above described requirement or perhaps relevant links on this or other forums or articles?

Thank you in advance for sharing recommendations/thoughts!

Michael

surfinwind

Posts : 6
Join date : 2009-02-03

Back to top Go down

Fact/Dimension Reload Design Empty Re: Fact/Dimension Reload Design

Post  ngalemmo Wed Feb 17, 2010 2:22 pm

I've had the misfortune to do reloads before but I havn't had the need to do anything different from an ETL standpoint. The only challenge you may have is with the type 2 dimensions, not with the ETL, but with the source system. If you needed to reload a dimension, would the source system actually have the change history? More often than not, the answer would be no. Short of loading chronoligically from backups, there isn't much you can do about it.

In general, avoid reloading dimensions if at all possible.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Fact/Dimension Reload Design Empty Thank you for the reply :)

Post  surfinwind Thu Feb 18, 2010 11:27 am

Not sure if the I'll be able to completely avoid having to re-load some history data into type 2 dims and facts, but I appreciate your response and recommendation!

surfinwind

Posts : 6
Join date : 2009-02-03

Back to top Go down

Fact/Dimension Reload Design Empty Re: Fact/Dimension Reload Design

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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