Updating facts

Hi. I am new to dimensional modeling and am wondering if this is a proper approach. Should I be reading up on a technique that I am unaware of?

 

The following block is explained in the lines after it. Assume daily loads.

 

----

I intend to have the following fields in my fact table:

 

item_sk, transaction_date_sk, action,  status, current_flag, current_on_date, not_current_on_date, 3-4 more SKs for other descriptive info (such as library card #).

 

R1: 123, 456 (SK for 4 days ago), out, complete, Yes, yesterday, SK for NULL

 

R2: 123, 789, request, pending, Yes, 3 days ago, SK for null.

R3: 123, 334, request, pending, Yes, 2 days ago, SK for null.

 

(not yet in the table- look when i reference it in the lines below)

R4: 123, sk for today, returned, 0, complete, Yes, today, SK for null.

 

-----------

 

Think of this as a library where you can check books out, check them in and request them.

 

The row R1 means that the book has been borrowed by someone 4 days ago.

 

Rows 2 and 3 (R2 and R3) mean that two other people have requested the book 1 day after it was checked out and 2 days after it was checked out. There are two active requests for the book and these are pending requests.

 

R4 means that the book was returned today. When the book is returned, I would insert R4 and update R1. In R1 I would set the current_flag to "No" (as the book is now returned), not_current_on_date to today.

 

Two people have requested the book and may pick it up in the next couple of days. The member in R2 comes in tomorrow to pick up the book. I would then insert R5:

 

R5: 123, 555 (SK for tomorrow), out, complete, Yes, tomorrow's date, SK for NULL.

 

At this point, I would also update R4 to reflect today's date in the not_current_date field. A better option may be to have another reserved date to mark the end of the transaction and instead of "SK for null" have a second reserved date with a special meaning.

 

My questions are:

 

1. I am new. Does this seem like a reasonable approach or does it smell like a newbie doing things wrong to you more experienced folks.

 

2. Is it normal to update existing rows in a fact table like this instead of just inserting new rows?

 

3. General comments around this? Pointer in the right direction.

Meant to add... After r5 is added I would change r2 from pending to complete and enter a date when pending status completes in the not current on date.

It looks like you're trying to model a process flow. This is best accomplished using the accumulating snapshot fact table. Per Kimball, "A row in an accumulating snapshot fact table summarizes the measurement events occurring at predictable steps between the beginning and the end of a process. Pipeline or workflow processes, such as order fulfillment or claim processing, that have a defined start point, standard intermediate steps, and defined end point can be modeled with this type of fact table. There is a date foreign key in the fact table for each critical milestone in the process. An individual row in an accumulating snapshot fact table, corresponding for instance to a line on an order, is initially inserted when the order line is created. As pipeline progress occurs, the accumulating fact table row is revisited and updated. This consistent updating of accumulating snapshot fact rows is unique among the three types of fact tables. In addition to the date foreign keys associated with each critical process step, accumulating snapshot fact tables contain foreign keys for other dimensions and optionally contain degenerate dimensions. They often include numeric lag measurements consistent with the grain, along with milestone completion counters."

Boxesandlines,

Thank you for your response and willingnessto help me out. I am currently doing a little rresearch on what you said and will get back to you if I have more questions. I am sure I will. I read up on this a while ago but seem to have forgotten what I read!

With an accumulating snapshot, the table may look something like this. Each check out would start with a request and then proceed to be checked out, and finally returned.

 

 

item_sk, request_date_sk, member_sk, check_out_date_sk, check_in_date_sk

 

This process may be a little "too well defined". At any point in time, a book may have 0 requests on it or 1000 requests on it.

 

If the table is defined as above (in other words define the process as being issue a request, check out, then check in and have one row for the entire process), then there will be no way to determine / report on queue lenghts.

 

Any thoughts?

You can a number of indicators to help the queries. For example, Open Request Indicator, Check Out Indicator, Check In Indicator. These will simplify the querying for the current state of the process. If you want to know Queue length then the query is simply Current Date - Open Request Date where Open Request Indicator = 1.