Datawearhousing

COMP9318 (12S1) ASSIGNMENT 1
DUE ON 23:59 21 APR, 2012 (SAT)
Q1. (30 marks)

Consider the restaurant section of http://www.eatability.com.au. One of the basical functionality is that a registered user can rate restaurants. E.g., look at the \Add a Review for 168_" part at http://www.eatability.com.au/au/sydney/168/.

You were asked to help the company to design a star schema to analyze various ratings on restaurants. You also need to list or draw hierarchies associated with each dimension. You only need to show up to three hierarchies for any dimension.1

Write down an MDX query that lists the top-5 restaurants in NSW in terms of food scores in 2011; for each of the restaurant listed, display their scores (food, service,
. . . ).

SELECT { Measures.[Food_rate], Measures.[Ambience_rate], Measures.[Service_rate],Measures.[Value_rate] } ON COLUMNS
HEAD( ORDER({[ Restaurant ].[ Australia ].[ Sydney ].[NSW].MEMBERS},
Measures.[Food_rate],
BDESC ),5
)ON ROWS
FROM [ Review]
WHERE [Calendar].[2011]

Q2. (20 marks)
Consider the following base cuboid Sales with three tuples and the aggregate function
SUM:

Location
Time
Item
Quantity
Sydney
2004
PS2
1400
Sydney
2005
PS2
1500
Melbourne
2004
PS2
1100
Melbourne
2005
XBox
1700

Location, Time, and Item are dimensions and Quantity is the measure. Suppose the system has built-in support for the value ALL.
(1) List the tuples in the complete data cube of R in a tabular form with 4 attributes,
i.e., Location; Time; Item; SUM(Quantity)?

Time
Item
Location
Sum(Quantity)
2004
PS2
Sydney
1400
2004
PS2
Melbourne
1100
2004
ALL
Sydney
1400
2004
ALL
Melbourne
1100
2004
ALL
ALL
2500
2004
PS2
ALL
2500
2005
PS2
Sydney
1500
2005
XBOX
Melbourne
1700
2005
ALL
Sydney
1500
2005
ALL
Melbourne
1700
2005
ALL
ALL
3200
2005
PS2
ALL
1500
2005
XBOX
ALL
1700
ALL
PS2
Sydney
2900
ALL