How would you model this data for a Data Warehouse?

mrochester

mrochester

mrochester

Soldato
Joined
29 Sep 2003
Posts
5,820
Location
Newcastle upon Tyne
Hi all.

I've got some data I need to bring into a data warehouse. Its data showing films and tv shows and I need to be able to find the following from it once in a data warehouse:

1) See all films or shows where the director is X or IN X,Y.
2) Count how many TV shows there are and how many films there are.
3) Find TV shows that star X.
4) Count Movies by Release Year.

The data has the following columns:

show_id (unique)
type (values = 'movie' and 'TV show')
title
director (values = name of single director or names of multiple directors in one column)
cast (values = name of single cast member or names of multiple cast members in one column)
release year (values = YYYY)

There are a number of other columns but I'm not interested in those. The model needs to be a star schema.

What I'm not sure of is what to use as my fact table other than the year and a bunch of surrogate keys that join to dimension tables for type, director, cast. Also, the cast and director columns can contain multiple values per row and is this going to cause a problem?

Many thanks.

M.
 

The Geezer

The Geezer

The Geezer

Soldato
Joined
13 Aug 2010
Posts
3,114
I wouldn’t call that a data warehouse, it’s nothing more than a simple database which you can run sql commands against.

Is this an assignment you’ve been set in school, college, Uni or is Blockbuster coming back ?
 
Last edited:

Semple

Semple

Semple

Soldato
Joined
5 Mar 2010
Posts
12,342
Is this more of an educational activity? Or is this something that you actually want working? If the latter then I'm pretty sure most media server software - eg plex, can do this for you. Would save reinventing the wheel.
 
You must log in or register to reply here.
Back
Top Bottom