Orders Fact Table

I've been reading more and more Kimball literature and I'd like clarification on something.

Transaction Fact Tables are rarely revisited because they do not need to be updated, deleted, etc but typically only appended to.

He goes on to describe incoming order ftransaction fact tables where the level of granularity is the individual line item.

However, in my system these line items can be edited, added to, deleted etc in place. Therefore, my Orders transaction fact table would have to be repeatedly updated for changes. "Transactions" would not have a definite beginning and ending point.

So, is this not a Transaction Fact Table at all? Is the best that I can deliver in the modules which are run this way is an update in place without history? Kind of like a SCD1 with dimensions?

Incidentally, how common are systems which run certain modules like quotes, orders, purchase orders, receivers, etc this way? Is this a terribly uncommon situation to have?

Opinions?

I guess, technically, if you update in place, it would be a snapshot of transactions.

But, depending on your need, there are other ways to approach this. If you need to maintain a history of changes to those lines, you can create a true transactional fact or an accumulating snapshot fact.

In a true transactional fact, you add an additional date (activity date) and when a line changes, you derive the net change and insert the net change into the table. The current state of a particular line would be the sum of all rows for that line. Net change would be represented by one or two additional rows (you need two rows if dimensional keys change, only one row if just the measures changed).

In an accumulating snapshot you add two dates... an effective and expiration date, and add new rows when changes are found, expiring the previous version of the fact. (Sort of like a type 2 dimension).

In either case, users can reconstruct a line at any point in time by either summing up to the activity date or filtering rows in effect as of a particular date.

Are you referring to a tranasaction dimension or an accumulating snapshot fact? See here.

If business only interested in latest record, then its better to add flag field 'Lastest_Flag' . New record can be appened with Flag as 1 and updated previous record with 0 (zero). By this way table will have history can be maintained and easy to retrive the data from fact table.
This is SCD 2 method only, but acting on fact table.