# Assignment2

1. How many nonempty aggregated (i.e., non-base) cells a complete cube will contain? Answer: 1918.

Explaination: Initially, for those cuboids with at least one of the first three dimensions not aggregated(i.e. not *), for example cuboid (dim1, ∗, ∗, ∗, ..., ∗), there are two cells. The number of such cuboids is (23 − 1) ∗ 27 = 896. Second, for cuboids with the first three dimensions aggregated, for example cuboid (∗, ∗, ∗, dim4, ..., dim10), there is only one cell. The number of such cuboids is 27 = 128. So the number of non-based cells is 896 ∗ 2 + 128 − 2 = 1918. 2. How many nonempty aggregated cells an iceberg cube will contain, if the condition of the iceberg cube is count ≥ 2? Answer: 128.

Explaination:

Only those cells with the first three dimensions aggregated (i.e.*), for example the cell (∗, ∗, ∗, b4, ..., b10), have count 2. And the number of such cells is 27 = 128. 3. How many (non-star) dimensions does the closed cell with count 2 have? Answer: 7.

Explaination: There is only one closed cell with count 2, i.e. (∗, ∗, ∗, b4, ..., b10). So the number of non-star dimensions is 10 − 3 = 7.

4. How many closed cells in the full cube?

Answer: 3.

Explaination: A closed cube is a data cube consisting of only closed cells. The three closed cells are (∗, ∗, ∗, b4, ..., b10),(a1, a2, a3, b4, ..., b9, b10), and (b1, b2, b3, b4, ..., b9, b10).

Question 2

1.How many cuboids are there in this cube

Answer: 24

Explaination:

Where is the number of levels associated with dimension i.

So for the Total number of cuboid = (2+1) *(1+1) *(1+1)*(1+1) =24

2.Counting cells in the cuboid(Location(city),Category,Rating,Price) Answer = 48

SELECT distinct city,category,rating,price ,count(*) as tot FROM [assign2_Q2].[dbo].[CS412_assignment 2_Q2] A

group by A.category,A.rating,A.price,A.city

3.Now let's drill up by climbing up in the Location dimension from City to State. How many cells are there in the cuboid. Answer = 34

SELECT distinct state,category,rating,price ,count(*) as tot FROM [assign2_Q2].[dbo].[CS412_assignment 2_Q2] A

group by A.category,A.rating,A.price,A.state

4.How many cell in the cuboid(*,category,Rating,Price)

Answer = 23

SELECT distinct A.category,A.rating,A.price,count(*) as tot FROM [assign2_Q2].[dbo].[CS412_assignment 2_Q2] A

group by A.category,A.rating,A.price

5. What is the count for the cell(Location(state)=illinois,*,rating=3,price='moderate' Answer = 2 (record # 6 ,45)

SELECT count(*)

FROM [assign2_Q2].[dbo].[CS412_assignment 2_Q2] A

where A.state = 'Illinois' and A.rating = 3 and A.price='moderate'

6. What is the count for the cell(Location(city) = 'Chicago',Category = 'food',*,*) Answer = 2

Select count(*) from [assign2_Q2].[dbo].[CS412_assignment 2_Q2] A where A.city = 'Chicago' and A.category = 'food'

Mini-MP#3

1) OLAP operation = Drill down

Most Sales = Mountain bike

Least Sales = Trial Bike

2)

Popular way Web Searching by Chrome

Best visualization of granularity = we can drill down data at multiple granularity level for time ranging from year/quarter,months.

Different granularity levels

3)Total transaction / region, to give the better idea of the concentration of customers in different regions.

Also the following cube can be used see the sales trend for the last year among different categories.

The following cube can be used as analyzing page views contributed by each browser for different quarters.

Please join StudyMode to read the full document