Data Warehousing Community Forum Data Warehousing Community Forum
The Warehouse of Data warehousing knowledge ...
 
Home Help Search Login Register
*
Welcome, Guest. Please login or register.
Did you miss your activation email? 		July 31, 2010, 02:44:50 am


Login with username, password and session length


Data Warehousing Community Forum > Data Warehousing > Data Warehousing Concepts > Topic: Star Schema using Transactions
Pages: [1]   Go Down
« previous next »
  Print  
Author Topic: Star Schema using Transactions  (Read 274 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: 113


Forum Don


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
You don't get it.  I built this place. Down here, I make the rules. Down here, I make the threats. Down here, I'm God.
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  
Data Warehousing Community Forum > Data Warehousing > Data Warehousing Concepts > Topic: Star Schema using Transactions
 « previous next »
 
Jump to:  



© Copyright 2006-2010 DW Forum. All rights reserved

contactSanghala | Cyber Militia | Powered by SMF | SMF © 2006-2009, Simple Machines LLC | Dilber MC Theme by HarzeM