Orders Fact Table

View previous topic View next topic Go down

Orders Fact Table

Post  DavidStein on Tue Sep 07, 2010 4:38 pm

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?

DavidStein

Posts : 24
Join date : 2010-04-01

View user profile

Back to top Go down

Re: Orders Fact Table

Post  ngalemmo on Tue Sep 07, 2010 5:02 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Orders Fact Table

Post  BoxesAndLines on Tue Sep 07, 2010 7:58 pm

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

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

View user profile

Back to top Go down

Orders Fact Table

Post  sachinh4u on Tue Sep 14, 2010 1:46 am

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.

avatar
sachinh4u

Posts : 5
Join date : 2010-08-30

View user profile

Back to top Go down

Re: Orders Fact Table

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