Datawearhousing Composition

COMP9318 (12S1) ASSIGNMENT you

DUE IN 23: fifty nine 21 INTEREST, 2012 (SAT)

Q1. (30 marks)

Consider the restaurant section of http://www.eatability.com.au. One of the essential functionality is that a listed user may rate restaurants. E. g., look at the \Add a Review intended for 168_" component at http://www.eatability.com.au/au/sydney/168/.

You were asked to help the company to create a star schema to investigate various rankings on restaurants. You also need to list or draw hierarchies associated with every single dimension. You simply need to show up to three hierarchies for any aspect. 1

Note down an MDX query that lists the top-5 restaurants in NSW in terms of meals scores this year; for each with the restaurant outlined, display all their scores (food, service,... ).

SELECT Measures.[Food_rate], Measures.[Ambience_rate],

Measures.[Service_rate],Measures.[Value_rate]

ON CONTENT

HEAD( ORDER( [ Restaurant ].[ Australia ].[ Sydney ].[NSW].MEMBERS, Procedures.[Food_rate],

BDESC

), 5

)ON ROWS

COMING FROM [ Review]

WHERE [Calendar].[2011]

Q2. (20 marks)

Consider the following bottom cuboid Sales with three tuples and the aggregate function SUM:

Site

Time

Item

Quantity

Sydney

2005

PS2

1400

Sydney

2006

PS2

truck

Melbourne

2004

PS2

1100

Melbourne

2005

XBox

1700

Location, Time, and Item are sizes and Variety is the assess. Suppose the device has integrated support pertaining to the value ALMOST ALL.

(1) List the tuples in the complete data cube of 3rd there�s r in a tabular form with 4 features, 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

2005

ALL

MOST

2500

2004

PS2

EVERY

2500

june 2006

PS2

Sydney

1500

june 2006

XBOX

Melbourne

1700

2006

ALL

Sydney

1500

2006

ALL

Melbourne

1700

june 2006

ALL

ALL

3200

2005

PS2

MOST

1500

2005

XBOX

ALL

1700

ALMOST ALL

PS2

Sydney

2900

EVERY

PS2

Melbourne

1100

ALL

XBOX

Melbourne

1700

ALL

ALL

Sydney

2900

ALMOST ALL

ALL

Melbourne

2800

ALL

XBOX

ALL

1700

MOST

PS2

EVERY

4000

MOST

ALL

ALMOST ALL

5700

(2) Write down an equal SQL statement that computes the same consequence (i. electronic., the cube). You can only use common SQL constructs, i. elizabeth., no DICE BY offer. (

CHOOSE

L. Country, Capital t. Year, I. Name, S. Quantity

FROM

Area L, Time T, Item I, Revenue S

EXACTLY WHERE

L. Location_Id=S. Location_Id AND

Capital t. Time_Id =S. Time_Id AND

We. Item_Id =S. Item_Id

)UNION(

SELECT

ALL, To. Year, I actually. Name, TOTAL (S. Quantity)

FROM

Location L, Time Capital t, Item I actually, Sales S

WHERE

L. Location_Id =S. Location_Id AND

T. Time_Id =S. Time_Id AND

We. Item_Id =S. Item_Id

GROUP BY (T. Year, My spouse and i. Name)

)UNION(

SELECT

L. Nation, ALL, I actually. ItemName, SUM(S. Quantity)

FROM

Area L, Period T, Item I, Deal S

WHERE

D. Location_Id =S. Location_Id AND T. Time_Id =S. Time_Id AND I. Item_Id =S. Item_Id GROUP SIMPLY BY (L. Country, I. Name)

)UNION(

CHOOSE

M. Country, To. Year, MOST, SUM(S. Quantity)

FROM

Location L, Period T, Item I, Sale S

WHEREVER

D. Location_Id =S. Location_Id AND

Big t. Time_Id =S. Time_Id AND

I. Item_Id =S. Item_Id

GROUP BY (L. Country, To. Year)

)UNION(

SELECT

ALL, MOST, I. Term, SUM(S. Quantity)

FROM

Location L, Period T, Item I, Sales S

EXACTLY WHERE

L. Location_Id =S. Location_Id AND

Big t. Time_Id =S. Time_Id AND

I. Item_Id =S. Item_Id

GROUP BY (I. Name)

)UNION(

SELECT

ALL, T. Year, ALMOST ALL, SUM(S. Quantity)

FROM

Location T, Time Big t, Item I, Sale S

WHERE

L. Location_Id =S. Location_Id AND

T. Time_Id =S. Time_Id AND

I. Item_Id =S. Item_Id

GROUP BY SIMPLY (T. Year)

)UNION(

CHOOSE L. Nation, ALL, MOST, SUM(S. Quantity)

FROM

Location L, Time Capital t, Item We, Sale S i9000

WHERE

L. Location_Id =S. Location_Id AND

T. Time_Id =S. Time_Id AND

I. Item_Id =S. Item_Id

GROUP BY SIMPLY (L. Country)

)UNION(

CHOOSE

EVERY, ALL, ALMOST ALL, SUM(S. Quantity)

FROM

Position L, Time T, Item I, Sale S

WHERE

M. Location_Id =S. Location_Id AND

T. Time_Id =S. Time_Id AND

We. Item_Id =S. Item_Id

)...

Share

Pick Your Plastic Dissertation

PHIL – 447 Week 1 – Pick The Plastic Inside the article " Credit or debit: decide on your plastic-type material, ” simply by Lisa Gerstner…...

corporate governance Essay

Introduction I discuss the paper inside the following style: section My spouse and i summarizes the main points of the paper. Section II analyzes the conventional…...