0

A slightly silly question in some ways as this is just about terminology.

If I have a fact table that records facts that change overtime with respect to the dimensions the fact belongs to, what would such a fact table be called? In fact is there a particular name?

For example the fact table might be storing an instance of a ticket in a help desk system and have a Status dimension. The ticket would change status over the course of it's lifecycle from Open to Resolved. I need to be able to store a fact record for each state change for historical reporting.

Steve Homer
  • 3,852
  • 2
  • 22
  • 41
  • 2
    I don't think there's any standard terminology for this, but the term "time stamped accumulating snapshot fact table" is used in [this article](http://www.kimballgroup.com/2012/05/01/design-tip-145-time-stamping-accumulating-snapshot-fact-tables/) which may in fact apply to your scenario. – Pondlife Jan 28 '13 at 18:19

1 Answers1

2

If you have a limited number of status changes (< 6), you could put all of the dates in one row of the fact table. It's ok to denormalize a warehouse fact table.

In an operational data base, I'd call this type of domain table a date range table.

This book might be helpful: Temporal Data & the Relational Model.

You can also Google "temporal database" and see if anything turns up helpful to you.

Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
  • No unfortunately in this case the ticket could potentially bounce backwards and forwards between any of the potential statuses so there's no upper bound to it. To be honest I'm happy with the schema I've devised and was more curious to see if there was a name for it I didn't know and Google wasn't helping. I'll check out the book though thanks for that. Temporal modelling and relational modelling often throw up interesting questions. – Steve Homer Jan 29 '13 at 09:42
  • Great. With that many state / date changes, date becomes a fact table. – Gilbert Le Blanc Jan 29 '13 at 13:51
  • I may consider building two tables - one to record each "transaction", and one to record the process flow as a accumulating snapshot, only keeping the most current status of each ticket. I.E. if a ticket gets closed, but then reopened later, it moves back to status "Assigned", and the closed date is nulled out until it gets closed again. – N West Feb 04 '13 at 16:33
  • @N West: If you order the transaction table in newest to oldest order, the first transaction is the snapshot information. The only reason you would need a separate table is if there are columns you want to keep for the snapshot that your don't want to keep for the transactions. – Gilbert Le Blanc Feb 04 '13 at 16:51