Data Warehousing Community Forum
February 09, 2012, 01:41:00 pm

Pages: [1]   Go Down
« previous next »
  Print  
Author Topic: Star Schema using Transactions  (Read 733 times)
Kabal42
DW Fresher
*

Reputation: +0/-0
Offline Offline

Posts: 2


View Profile
« on: March 06, 2010, 05:17:43 am »
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
Logged
Whoever
Administrator
*****

Reputation: +34/-0
Offline Offline

Posts: 124



View Profile WWW
« Reply #1 on: March 06, 2010, 01:03:01 pm »
Quote from: Kabal42 on March 06, 2010, 05:17:43 am
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?
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.
Logged
If most people said what’s on their minds, they’d be speechless.
Kabal42
DW Fresher
*

Reputation: +0/-0
Offline Offline

Posts: 2


View Profile
« Reply #2 on: March 06, 2010, 06:48:50 pm »
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
Logged
Pages: [1]   Go Up
  Print  
« previous next »
 
Jump to: