Fact/Dimension Reload Design
2 posters
Page 1 of 1
Fact/Dimension Reload Design
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
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
Re: Fact/Dimension Reload Design
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.
In general, avoid reloading dimensions if at all possible.
Thank you for the reply :)
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
Similar topics
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Joining Type II Dimension Hierarchy directly to Fact versus through a Dimension table
» Suggestions on Dimension/Fact design
» Help settle a design arguement - Fact Column or Dimension row?
» Work Order / Customer Order Design - Dimension or Fact
» Joining Type II Dimension Hierarchy directly to Fact versus through a Dimension table
» Suggestions on Dimension/Fact design
» Help settle a design arguement - Fact Column or Dimension row?
» Work Order / Customer Order Design - Dimension or Fact
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|