Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Orders Fact Table

4 posters

Go down

Orders Fact Table Empty Orders Fact Table

Post  DavidStein 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?



Posts : 24
Join date : 2010-04-01

Back to top Go down

Orders Fact Table Empty Re: Orders Fact Table

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

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

Back to top Go down

Orders Fact Table Empty Re: Orders Fact Table

Post  BoxesAndLines Tue Sep 07, 2010 7:58 pm

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

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

Back to top Go down

Orders Fact Table Empty Orders Fact Table

Post  sachinh4u 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.


Posts : 5
Join date : 2010-08-30

Back to top Go down

Orders Fact Table Empty Re: Orders Fact Table

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum