1

I am designing a movie rental data warehouse

I want the fact table to consist of movie rentals/returns but I'm getting confused.

The movies can be returned at any store so I need to show that.

I have these dimensions: time, customerinfo, movie info , and store

I don't see where I would show if it is a rental or a return if they are separate records?

What would be my options for designing the star schema to display this information I have no idea where to to put it and my head is about explode.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Chris James
  • 11
  • 3
  • It sounds like you are designing an operational database instead of a data warehouse. Perhaps you need another table to maintain the current status for each movie. – chance Apr 18 '12 at 15:30
  • I already have an operational database, but I'm trying to convert it into a data warehouse with the star schema. I've never created a data warehouse before and all the data warehouse examples I've seen don't seem to have something like "order types" or in my case movie return or rental , assuming "customer actions" is my fact table thank you for the response "chance". – Chris James Apr 18 '12 at 15:39
  • It's not really clear what you mean by "rental" and "return". Are you talking about the times at which the customer rents the movie and then returns it? If so, it's common to have dimensions for date and time of day. So the fact table would be called "FactMovieRental", you would have dimension keys for the rental period start and end, and one possible grain would be "RentalDurationInHours". But I may be completely wrong here, because I may have misunderstood the question. – Pondlife Apr 24 '12 at 11:43

1 Answers1

0

Start with the transaction level and build from there...

FactMovieTransaction
    CustomerKey
    TransactionTypeKey
    StoreKey
    MovieKey
    DateKey
    TimeKey
    <fact 1>
    <fact 2>


DimCustomer
    CustomerKey
    CustomerID (ex. 0000123478)
    CustomerName (ex. Peter Anderson)

DimTransactionType
    TransactionTypeKey 
    TransactionType (ex. Return, Rental)

DimStore
    StoreKey
    StoreID (ex. 234, 212)
    StoreName (ex. BlockBuster#243, BlockBuster#212)

DimMovie
    MovieKey
    MovieName (ex. StarWars)
    MovieDescription
    Genere

DimDate
    DateKey
    Date (ex. 1/1/900 - 1/1/2999)

DimTime
    TimeKey
    Time (ex. 00:00 - 23:59)
Bill Anton
  • 2,920
  • 17
  • 23