Okay, we have the need to model a "source" fact table that has discrete facts about people viewing online movies, on demand. This fact table will have one record, per view, including start date/time and viewing duration. The time dimension is broken into a 15 minute grain (96 records per day) and very rich. Let's call this fact: MOVIE_VIEW.
Then, we want to somehow "roll" up these movie views into another star schema with the same time dimension, but such that a single view from the MOVIE_VIEW fact table will "explode" into multiple records in the new MOVIE_DEMAND table. The reason for doing this is we want to be able to track peak demand for movies viewed.
A MOVIE_VIEW fact corresponds to the start of the movie and the movie run length, and is singular for each view, whereas MOVIE_DEMAND is an aggregate of a movies demand, in 15 minute increments. So, if a movie is 90 minutes long, and played to completion, it would generate 6 MOVIE_DEMAND records.
Is this a good way to do this? Are there better ways?
TR
Is this
Then, we want to somehow "roll" up these movie views into another star schema with the same time dimension, but such that a single view from the MOVIE_VIEW fact table will "explode" into multiple records in the new MOVIE_DEMAND table. The reason for doing this is we want to be able to track peak demand for movies viewed.
A MOVIE_VIEW fact corresponds to the start of the movie and the movie run length, and is singular for each view, whereas MOVIE_DEMAND is an aggregate of a movies demand, in 15 minute increments. So, if a movie is 90 minutes long, and played to completion, it would generate 6 MOVIE_DEMAND records.
Is this a good way to do this? Are there better ways?
TR
Is this