Fact Table Design for transactional model

[TimothyTan]

If my OLTP table is based on transactional model, how do i implement my fact table.

For example,
Below shows the simplified OLTP table. In addition to credit and debit, 'deletion' of a transaction is possible.

ID Date TransactionType Amount RefID
001 28/11/03 Credit $100 null
002 01/12/03 Delete $100 001

How do i ensure that the total amount is correct?
Should i just change the sign of the delete record to -ve?

 

[blom0344]

If you wish to keep the greatest detail in your datawarehouse design (and be able to track individual bookings) you should store both credit and debit records.
On the reporting side you can then create correct calculation by using objects that have negative values where required like: sign*measure

where sign can be either 1 or -1 based on the calculation model you use.

T. Blom
Information analyst
tbl@shimano-eu.com