Expiring rows in a SCD
2 posters
Page 1 of 1
Expiring rows in a SCD
Toy example for a real problem:
In the ETL i'm writing, I have a source table "foo". It is eventually loaded to dim.foo in the DW. "foo" has just two columns: "bar" and "fum". "bar" is my source business key.
Now, one of the changes that can happen in the source system is that a row in "foo" can be deleted, indicating that the data in column "fum" no longer applies to the key in column "bar". To detect this condition, I run a query that joins the new source data and my current dim table to find the now-absent rows. (There is no transaction data available to me to tell me that this has happened.) I will then mark these rows in my dim table as no longer active or relevant.
Question: Is this a reasonable approach? Is there a better way??
In the ETL i'm writing, I have a source table "foo". It is eventually loaded to dim.foo in the DW. "foo" has just two columns: "bar" and "fum". "bar" is my source business key.
Now, one of the changes that can happen in the source system is that a row in "foo" can be deleted, indicating that the data in column "fum" no longer applies to the key in column "bar". To detect this condition, I run a query that joins the new source data and my current dim table to find the now-absent rows. (There is no transaction data available to me to tell me that this has happened.) I will then mark these rows in my dim table as no longer active or relevant.
Question: Is this a reasonable approach? Is there a better way??
gbritton- Posts : 6
Join date : 2013-11-18
Re: Expiring rows in a SCD
In a lot of cases there isn't a requirement to do this, but if you need to, what you describe is a reasonable way to do it. You never want to physically delete rows from a dimension.
Similar topics
» Fact table granulartity to small or just built wrong
» Versioning Fact rows?
» Converting Columns into Rows.
» how to "reduce the duplicated rows"
» Transposing from columns to rows
» Versioning Fact rows?
» Converting Columns into Rows.
» how to "reduce the duplicated rows"
» Transposing from columns to rows
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|