Question

Suppose that a data warehouse consists of the four dimensions, date, spectator, location, and game, and the two measures, count and charge, where charge is the fare that a spectator pays when watching a game on a given date. Spectators may be students, adults, or seniors, with each category having its own charge rate.

a) Draw a star schema diagram for the data warehouse.

b) Draw the base cuboid [date, spectator, location] and apply any four OLAP operations.

Answer 1

Suppose that a data warehouse consists of the four dimensions, date, spectator, location, and game, and the two measures, count and charge, where charge is the fare that a spectator pays when watching a game on a given date. Spectators may be students, adults, or seniors, with each category having its own charge rate.

a) Draw a star schema diagram for the data warehouse.

 

b) Draw the base cuboid [date, spectator, location] and apply any four OLAP operations.

Base Cuboid [date, spectator, location]:

+-------+------------+-----------+--------------+--------+
| Date  | Spectator  | Location  | Count        | Charge |
+-------+------------+-----------+--------------+--------+
| Jan 1 | Students   | Stadium A | 500          | 7500   |
| Jan 1 | Adults     | Stadium A | 300          | 9000   |
| Jan 1 | Seniors    | Stadium A | 200          | 6000   |
| Jan 1 | Students   | Stadium B | 700          | 10500  |
| Jan 1 | Adults     | Stadium B | 400          | 12000  |
| Jan 1 | Seniors    | Stadium B | 100          | 3000   |
| Jan 2 | Students   | Stadium A | 450          | 6750   |
| Jan 2 | Adults     | Stadium A | 250          | 7500   |
| Jan 2 | Seniors    | Stadium A | 150          | 4500   |
| Jan 2 | Students   | Stadium B | 600          | 9000   |
| Jan 2 | Adults     | Stadium B | 350          | 10500  |
| Jan 2 | Seniors    | Stadium B | 50           | 1500   |
+-------+------------+-----------+--------------+--------+

OLAP Operations:

1. Roll-up (Rolling up from Day to Month in the Date dimension):

   • Aggregate the data for each month.

   +-------+------------+-----------+--------------+--------+
   | Month | Spectator  | Location  | Count        | Charge |
   +-------+------------+-----------+--------------+--------+
   | Jan   | Students   | Stadium A | 950          | 14250  |
   | Jan   | Adults     | Stadium A | 550          | 16500  |
   | Jan   | Seniors    | Stadium A | 350          | 10500  |
   | Jan   | Students   | Stadium B | 1300         | 19500  |
   | Jan   | Adults     | Stadium B | 750          | 22500  |
   | Jan   | Seniors    | Stadium B | 150          | 4500   |
   +-------+------------+-----------+--------------+--------+
   

   Drill-down (Drilling down from Month to Day in the Date dimension):

   • Expand the data to show each day within a month.

   +-------+------------+-----------+--------------+--------+
   | Date  | Spectator  | Location  | Count        | Charge |
   +-------+------------+-----------+--------------+--------+
   | Jan 1 | Students   | Stadium A | 500          | 7500   |
   | Jan 1 | Adults     | Stadium A | 300          | 9000   |
   | Jan 1 | Seniors    | Stadium A | 200          | 6000   |
   | Jan 1 | Students   | Stadium B | 700          | 10500  |
   | Jan 1 | Adults     | Stadium B | 400          | 12000  |
   | Jan 1 | Seniors    | Stadium B | 100          | 3000   |
   | Jan 2 | Students   | Stadium A | 450          | 6750   |
   | Jan 2 | Adults     | Stadium A | 250          | 7500   |
   | Jan 2 | Seniors    | Stadium A | 150          | 4500   |
   | Jan 2 | Students   | Stadium B | 600          | 9000   |
   | Jan 2 | Adults     | Stadium B | 350          | 10500  |
   | Jan 2 | Seniors    | Stadium B | 50           | 1500   |
   +-------+------------+-----------+--------------+--------+
   

   Slice (Slicing for a specific Spectator Type, e.g., Students):

   • View the data for a particular Spectator Type across all dates and locations.

   +-------+------------+-----------+--------------+--------+
   | Date  | Spectator  | Location  | Count        | Charge |
   +-------+------------+-----------+--------------+--------+
   | Jan 1 | Students   | Stadium A | 500          | 7500   |
   | Jan 1 | Students   | Stadium B | 700          | 10500  |
   | Jan 2 | Students   | Stadium A | 450          | 6750   |
   | Jan 2 | Students   | Stadium B | 600          | 9000   |
   +-------+------------+-----------+--------------+--------+
   

   Dice (Dicing for a specific Date and Location, e.g., January 1, 2023, and Stadium A):

   • Focus on data for a particular date and location.

   +-------+------------+-----------+--------------+--------+
   | Date  | Spectator  | Location  | Count        | Charge |
   +-------+------------+-----------+--------------+--------+
   | Jan 1 | Students   | Stadium A | 500          | 7500   |
   | Jan 1 | Adults     | Stadium A | 300          | 9000   |
   | Jan 1 | Seniors    | Stadium A | 200          | 6000   |
   +-------+------------+-----------+--------------+--------+
   

 

Comments

A new and short answer is required for this answer , you can add any youtube video in comment for reference purpose too !