snapshot facts: daily versus effective from & to

View previous topic View next topic Go down

snapshot facts: daily versus effective from & to

Post  elementary on Fri Jan 25, 2013 5:50 am

concerning a periodic snapshot fact table:

in my current mission we are facing a multitude of snapshot facts ...

I understand the well-known example of an inventory snapshot, for which, on any given day, all products, see their quantity on hand being stored.
typically in inventory this is very dense.
On the other hand there could be examples for which the information is less dense.

I'm just wondering when you guys are typically using the periodic snapshot as defined by kimball, typically resulting in many records - and when you are using a model in which you use effective from & effective to dates (in the case that a certain situation most likely will exist for a number of time periods (days) in a row.

What do you see as pros & contras for these two scenarios - an when to apply which?

Any help would be considered here!

tx.

elementary

Posts : 4
Join date : 2012-05-18

View user profile

Back to top Go down

Re: snapshot facts: daily versus effective from & to

Post  umutiscan on Fri Jan 25, 2013 9:52 am

It depends on the requirement. In some cases one snapshot in a time period can satisfy the requirement. For example business are usually interested in the EOD account balances of the customers and they do not interested in all changes.

In some cases all changes may be necessary to take a decision. For example a business user may need to list the customers that entered in the blacklist in any time. If you hold the blacklist data as daily snapshots, you may never report the customers that entered into the blacklist in the morning, and got out of the list two hours later. In this case you may track all blacklist events.

And it also depends on the data behaviour. For example, designing a slowly changing metric as a daily or monthly snapshot can be a poor desing from data size point of view.


Last edited by umutiscan on Fri Jan 25, 2013 3:06 pm; edited 1 time in total

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: snapshot facts: daily versus effective from & to

Post  BoxesAndLines on Fri Jan 25, 2013 10:34 am

I try to avoid using effective and end dates in a fact table. Sure Kimball wrote a tip on it and some folks see that as a glowing recommendation to use throughout the warehouse, but I see it as a solution for a very specific problem. If you're going to do a Kimball warehouse, you will use lots of disk space. That's part of it. A true snapshot fact is the worse culprit. OTOH, the simplicity of querying a snapshot fact and the ability to trend data over time, is a really nice thing to have.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: snapshot facts: daily versus effective from & to

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