Fact table for transactional data

View previous topic View next topic Go down

Fact table for transactional data

Post  JV99 on Mon Nov 07, 2011 1:43 pm

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

JV99

Posts : 1
Join date : 2011-11-07

View user profile

Back to top Go down

Re: Fact table for transactional data

Post  ngalemmo on Mon Nov 07, 2011 11:49 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Fact table for transactional data

Post  VHF on Mon Nov 14, 2011 5:28 pm

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.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Fact table for transactional data

Post  ngalemmo on Mon Nov 14, 2011 5:49 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Fact table for transactional data

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