×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Multiple fact tables combined into 1

Multiple fact tables combined into 1

Multiple fact tables combined into 1

(OP)
This is just a simple question. Every time I read an article they use the classic video rental store as an example and only show the sales facts for the rental side. We all know that video rental stores also sell products including food and videos used and new.

We should have multiple facts for each discrete process, rental and sales and then combine them for combined sales.

Obviously rental will have more date fields but the customer dimension is the same.

My question is what would the erd for the oltp system look like one sales or 2 sales tables and inventory tables and what would the final combined fact table look like or am i off base here.

I combined two types of sales form 2 fact tables before and we had no problems.

If anyone knows of a site using a completed solution please provide.

Thanks in advance

Vanya

 

RE: Multiple fact tables combined into 1

I think you're asking what the schema would look like for the data warehouse, not for the OLTP system?

Anyways, I've never done a video rental data mart, but this would be my first thoughts for a dimensional model:

CODE

factSales
---------
DateKey,
TimeOfDayKey,
StoreKey,
CustomerKey,
ProductKey,  (would contain an attribute in the Product dimension to determine rental or otherwise),
SalesAmount,
TaxAmount,
TransactionID

factRentals
---------
DateRented, (to Date Dimension)
DateReturned, (to Date Dimension)
StoreKey,
CustomerKey,
ProductKey,
DaysAllowed,
DaysCheckedOut,
DaysOverdue,
TransactionID

One fact table for sales (rentals or actual sales), and one fact table to track rental metrics.  One thing I am not sure about would be penalties for overdue rentals.  If the sales fact table is intended to track all store revenue, then it would make sense to put it in the factSales table.  If penalties are thought of as separate, then you might put the penalty assessed and penalty paid amounts in the factRentals table.  

I wouldn't speculate on the Inventory fact tables--I think this would be heavily dependent on the business.  For example, I would have no clue if videos which are currently checked out would be included as being in stock.   

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close