Data Warehousing Community Forum

Hi I want to create a star schema, that allows me to analyse products that are commonly purchased togeather.

The operational database consists of a products table, an Transaction table (transaction header) and TransactionLines table... amongst others.

Im struggeling on creating a fact table..

Dimension tables... Time , Product , Transaction??

IM slightly lost... what metrics would I use in the fact table and what dimension tables would I use?

Any help would be greatly appriciated

Well, what your Fact table comprises would depend completely on exactly what metrics you want to analyse.

Normally a Sales/Purchase fact would have metrics like Quantity/Units Sold, Sales Amount (calculated from Units Sold x Unit Price), Sales Location (if you have multiple stores' data) etc.
The easiest way would be to denormalize the Transaction and TransactionLines tables and use all the non-dimension columns in there as facts.
Time, Product etc would be the dimensions.

If you could post the structure of your Transaction and TransactionLines tables, it would be easier for us to suggest something.

My Operational Database looks like:


Products (ProductID, ProductName, SalesPrice, CostPrice, SupplierID)

TransactionHeader (TransactionID, PaymentMethod, CustomerID, StoreID, TransactionDate)

TransactionLine (TransactionID, TransactionLineID, ProductID, Quantity, Price )

Store (StoreID, StoreName, Region )

=============================================================================================

I want to create a star that will allow me to find out, each month - the most common products that are purchased at the same time...

Or something alonf those lines,

Any ideas?

Cheers