+2 votes
203 views
by (162 points)
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.

1 Answer

0 votes
by (98.9k points)
 
Best answer

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   |
    +-------+------------+-----------+--------------+--------+
    

 

by (98.9k points)
A new and short answer is required for this answer , you can add any youtube video in comment for reference purpose too !
asked Nov 11, 2023 by (98.9k points) Draw a STAR schema for Video Rental.

Related questions

+1 vote
0 answers 56 views
0 votes
1 answer 65 views
+2 votes
0 answers 110 views

Doubtly is an online community for engineering students, offering:

  • Free viva questions PDFs
  • Previous year question papers (PYQs)
  • Academic doubt solutions
  • Expert-guided solutions

Get the pro version for free by logging in!

5.7k questions

5.1k answers

108 comments

537 users

...