Retail Point of Sale Fact Table Question

For a retail, point of sale fact table I am thinking of taking what Ralph said in class that the "grain is the beep" literally.

So talking this through, everytime an item is scanned as a "sale" that would go into the fact table.

Does this also mean that rows that might come over in ETL, such as a row that is not a "sale" but a "tender" of a SKU, or a "return" of SKU, should go into their own fact table?

Something like:

FactSales
FactTenders
FactReturns
FactDiscount
...etc.?

Or should I put all point of sale rows into one fact table, FactSales and just filter on the different transaction types from a DimTransactionType key?

Thank you very much for any insights.

Brad

I would go with one fact and include a transaction type dimension. I would include classification codes as attributes to ease selection (such as a 'this is a sale' transaction type indicator). This would allow you to flag multiple types (such as sales and returns) as sales transactions.

The reason I ask this is because the FactSales table could be drastically reduced if it was only, sales, line item corrects and trx voids which only make up ~25% of the rows. The rest are some other transaction type that we care about, but not as much as these 'sales' types.

Thank you for your insights.

I think one fact is better.

Each transaction requires an action at the POS. If you grab everything, you can answer "Total $ in Sales", "Total $ in Returns", and "Net Dollars" as well as "Total Sales Transactions", "Total Returns", "Total Discounts", "Total non monetary transactions", and "Total Transactions". The transaction volumes can be more useful than the sales. It can be used in staffing models, it can be used to measure the effectiveness of changes in processes.

Enlightening points, thank you very much for that!

ngalemmo wrote:I would go with one fact and include a transaction type dimension. I would include classification codes as attributes to ease selection (such as a 'this is a sale' transaction type indicator). This would allow you to flag multiple types (such as sales and returns) as sales transactions.

I agree


_____________________
What are Backlinks| Article Marketing Robot Review