Multiple fact tables combined into 1

[DataStiletto]

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

 

[RiverGuy]

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:

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.