Fact table for transactional data

Hi,

I am new to BI, my company deployed an ERP system about a year ago, now we are moving toward to DW / Cube implementation.
We are in the stage of defining our own DW (Fact and dimensions tables).
My question is
Transaction tables such as Sales line , purchase line, etc. (contains data field calls [status] as example, which could be open, delivered / received, Invoiced or Paid); this status field kept changing while it goes thru the business process (updated to delivered after user performed packing slip on the sales order ; A customer refund (credit note) will change it from "Invoiced" to "delivered" stage.
We want to include transactional data into our Sales Fact table, what is the best way to upload / handle data from OLTP to our DW?
Should we update existing record in DW when the status of sales order changed or create a new record for the same sales order line (ledger like fashion) and added a column to identify the Sales line as “Current”?

Thanks,
JV

Business process are usually a good parallel to fact tables that are required in a dw. From what you describe there are probably at least three: orders, fulfillment, invoicing, and possibly AR. Orders would contain customer orders as they are received and accepted. Fulfillment would contain shipments/receipts by a customer with a back reference to the order. Invoicing would contain invoice information with a back reference to fulfillment and order.

Each would be collected individually and used for various analysis. You may then wish to create an aggregate from these facts that provides an order-to-cash picture in one place.

In my Sales Fact table I include Sales Invoice Lines, Credit Memo Lines, and Debit Memo Lines. I flip the sign on Credit Memo Line amounts during ETL.

You do not want to have multiple versions of a transactional fact record in the DW with a "current" flag (such as you might do with a SCD2 dimension record.) You should always be able to aggregate (sum) all the records in a transactional fact table and get a meaningful result.

When transactions have been updated in the source system you should either (a.) update the fact record in the DW or (b.) add a "delta" fact record that represents the change that was made. For example, if QuantitySold on an existing line item changed from 10 to 5 you would leave the original fact record alone and then add a new fact record with a QuantitySold of -5. Queries will then return an aggreated net value of 5.

As VHF points out, when looking at invoicing, it is a good idea to include all related transaction types in a single fact. However, it is also important to understand that 'sales' means different things to different groups. Often salespeople will refer to orders as 'sales', while accounting will refer to invoicing (when revenue is actually recognized). Sales orders and invoicing are two different things and should be collected in separate fact tables.

While invoicing is naturally transactional by nature (an invoice is final when released, any changes occur in credit and debit memos), orders are not. How you deal with changes to an order in a fact table will depend on business requirements.