What is a Frozen FACT?

View previous topic View next topic Go down

What is a Frozen FACT?

Post  Sachin on Thu Feb 10, 2011 11:47 am

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

Sachin

Posts : 4
Join date : 2011-02-10

View user profile

Back to top Go down

What is a Frozen FACT? (OR a snapshot?)

Post  Sachin on Fri Feb 18, 2011 11:00 am

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

Sachin

Posts : 4
Join date : 2011-02-10

View user profile

Back to top Go down

Re: What is a Frozen FACT?

Post  BoxesAndLines on Fri Feb 18, 2011 2:12 pm

Snapshot facts are not retroactively updated. It was the state of the business on that day. From a loading perspective, it's always insert.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: What is a Frozen FACT?

Post  Sachin on Fri Feb 18, 2011 2:55 pm

Great!

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

View user profile

Back to top Go down

Re: What is a Frozen FACT?

Post  BoxesAndLines on Fri Feb 18, 2011 9:32 pm

Depends. The solution maybe to rerun yesterday's snapshot or just update the offending row(s) or do nothing at all.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: What is a Frozen FACT?

Post  Sachin on Mon Feb 21, 2011 2:27 pm

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

View user profile

Back to top Go down

Re: What is a Frozen FACT?

Post  ngalemmo on Mon Feb 21, 2011 5:12 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: What is a Frozen FACT?

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