Fact/Dimension Reload Design

View previous topic View next topic Go down

Fact/Dimension Reload Design

Post  surfinwind on 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

View user profile

Back to top Go down

Re: Fact/Dimension Reload Design

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

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

View user profile http://aginity.com

Back to top Go down

Thank you for the reply :)

Post  surfinwind on 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

View user profile

Back to top Go down

Re: Fact/Dimension Reload Design

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