What is a Frozen FACT?
3 posters
Page 1 of 1
What is a Frozen FACT?
Hello:
I would like to know the definition of a Frozen FACT. Well, I did refer Kimball's books and I have a decent understanding of what it is, but then I ran into a few people who define it differently, may be more correctly. Which is why I decided to get more opinions of experts. I also have a couple of more questions which may follow soon.
Scenario:
An Account-dimension table has 10 account numbers. Transactions took place against 5 account numbers on Day-1. At the end of the day during ETL, it was determined that one of those 5 account numbers was missing in the dimension table (late-arriving dimension). So, now we have all 5 FACT records in the FACT table but with one of them being an error record.
On Day-2, that error record is corrected by source system and sent to the data warehouse for processing at EOD Day-2.
Now then, my understanding from Kimball's books is, go to the FACT table and destructively update that error record in the FACT table.
Question: Am I going against the concept of Frozen FACT, by destructively updating the error record in FACT table?
I would appreciate if experts in this forum can throw some light on this.
-Sachin
I would like to know the definition of a Frozen FACT. Well, I did refer Kimball's books and I have a decent understanding of what it is, but then I ran into a few people who define it differently, may be more correctly. Which is why I decided to get more opinions of experts. I also have a couple of more questions which may follow soon.
Scenario:
An Account-dimension table has 10 account numbers. Transactions took place against 5 account numbers on Day-1. At the end of the day during ETL, it was determined that one of those 5 account numbers was missing in the dimension table (late-arriving dimension). So, now we have all 5 FACT records in the FACT table but with one of them being an error record.
On Day-2, that error record is corrected by source system and sent to the data warehouse for processing at EOD Day-2.
Now then, my understanding from Kimball's books is, go to the FACT table and destructively update that error record in the FACT table.
Question: Am I going against the concept of Frozen FACT, by destructively updating the error record in FACT table?
I would appreciate if experts in this forum can throw some light on this.
-Sachin
Sachin- Posts : 4
Join date : 2011-02-10
What is a Frozen FACT? (OR a snapshot?)
I thought perhaps Frozen FACT is not the term every one is familiar with, which is why there is no response. So, I decided to call it a snapshot instead of a Frozen FACT.
Please read the original post and replace 'Frozen FACT' with 'Snapshot' and let me know your opinion.
-Dinesh
Please read the original post and replace 'Frozen FACT' with 'Snapshot' and let me know your opinion.
-Dinesh
Sachin- Posts : 4
Join date : 2011-02-10
Re: What is a Frozen FACT?
Snapshot facts are not retroactively updated. It was the state of the business on that day. From a loading perspective, it's always insert.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: What is a Frozen FACT?
Great!
What if there's an error in the snapshot that gets corrected next day? How do you handle that?
-Sachin
What if there's an error in the snapshot that gets corrected next day? How do you handle that?
-Sachin
Sachin- Posts : 4
Join date : 2011-02-10
Re: What is a Frozen FACT?
Depends. The solution maybe to rerun yesterday's snapshot or just update the offending row(s) or do nothing at all.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: What is a Frozen FACT?
BoxesAndLines wrote:Depends. The solution maybe to rerun yesterday's snapshot or just update the offending row(s) or do nothing at all.
1. Rerun yesterday's snapshot?
- Do you mean to maintain two snapshots, in that case, for the same date? One Frozen fact that has error records and the other one RETRO Fact, with the correct record?
2. Updating the offending row(s)?
- That is exactly the OP was suggesting (Also recommended by Kimball in ETL Toolkit)
3. Do nothing at all?
- Could you be more specific?
-Sachin
Sachin- Posts : 4
Join date : 2011-02-10
Re: What is a Frozen FACT?
I tend to prefer option 3... deep down I am pretty lazy...
It gets down to what is the snapshot supposed to represent and what are business policies.
A snapshot is supposed to be the way things were in the operational system at a particular point in time. One can argue that, as long as what is in the data warehouse is an accurate reflection of the state of the data at the stated point in time, then the snapshot should not be updated.
But, the ideal is usually a long way from reality. So you wind up doing either 1 or 2, whichever is easier. In the case of a reload, you have two options... either replace the previous snapshot with a restatement, or load a new snapshot with a different timestamp.
It also depends what the point in time represents. A scenario in Accounting may be when a period is closed. Now, sometimes things don't go as planned and a posting doesn't get in when it should. They may somtimes 'reopen' a period to correct an errant or missing transaction (usually somebody's accrual) the next day. In cases like that, it is usually just best to reload the snapshot.
It gets down to what is the snapshot supposed to represent and what are business policies.
A snapshot is supposed to be the way things were in the operational system at a particular point in time. One can argue that, as long as what is in the data warehouse is an accurate reflection of the state of the data at the stated point in time, then the snapshot should not be updated.
But, the ideal is usually a long way from reality. So you wind up doing either 1 or 2, whichever is easier. In the case of a reload, you have two options... either replace the previous snapshot with a restatement, or load a new snapshot with a different timestamp.
It also depends what the point in time represents. A scenario in Accounting may be when a period is closed. Now, sometimes things don't go as planned and a posting doesn't get in when it should. They may somtimes 'reopen' a period to correct an errant or missing transaction (usually somebody's accrual) the next day. In cases like that, it is usually just best to reload the snapshot.
Similar topics
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Cost plans for projects - One fact table or several fact tables?
» Unknown number of relationships from dimension to fact until fact loaded
» Multiple Fact Tables vs. Consolidated Fact Table
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Cost plans for projects - One fact table or several fact tables?
» Unknown number of relationships from dimension to fact until fact loaded
» Multiple Fact Tables vs. Consolidated Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|