Project Assignment 13

Simple Data Warehousing

The CEO of Neo Record Corp knocks on your office door.

"I know you're busy working on the Web version of OmniTunes," he says, "but I have another project for you. Our sales and marketing department need some monthly reports on all transactions that are occurring on the OmniTunes network. These reports will only run once a month, but have to cover all the songs in the system."

"Well, that's a ton of data to cover," you reply, "we'll probably need to create a data warehouse that's specialized for generating reports like this."

"Sounds fancy! Well, just realize that each night all the nodes on the OmniTunes network send us any transactions that occurred. So each night we have new data on what songs were sold and for how much. We just need to get all that data in a nice monthly report, ok?" he says.

Create a simple data warehousing plan

This assignment intends to illustrate the work necessary in creating a simple data warehouse.

Starting with your music_app database, assume that each month a report must be generated that lists a song title, the album name, the artist name, the genre name, and the average price paid for that song.

1. In one or two sentences, describe the goal of the data warehouse.
2. Describe the structure of the data warehouse schema, using text, create table statements, and/or a diagram.
3. Explain (at least a paragraph) why you have chosen the design you are recommending.
4. Describe in moderate detail the processes and queries involved in updating the data warehouse.
5. Describe or implement the SQL query that would generate the monthly report.

Hints

The report-generating query should perform few, if any, joins and conduct few, if any, calculations.

This assignment is meant to be answered descriptively, not just technically, so spend some time thinking about how the whole data warehousing process would have to work.

To be completed by Friday, April 4