Do OLAP with SQL Use the SQL ROLLUP & CUBE to generate & store a 3D cube with hi

Question

Do OLAP with SQL

Use the SQL ROLLUP & CUBE to generate & store a 3D cube with hierarchies based on the tables in the database given by the description:

Supplier(Supp_ID, Name, City, State)

Consumer(Con_ID, Name, City, State)

Product(Prod_ID, Name, Product_Category, Product_Line, Product_Packaging)

Offers(Supp_ID, Prod_ID, Quantity, Price)

Requests(Con_ID, Prod_ID, Quantity, Price)

Transactions(Tran_ID, Supp_ID, Con_ID, Prod_ID, Quantity, Price)

Given the Transactions_Cube stored abov solve the queries:

1) Aggregate by product category?

2) Aggregate by supplier states?

3) # of trasactions btwn supplier-city-consumer-city pairs?

4) Volum of each product sold in WI?

5) Volum of sales by product & supplier state?

6) Volum of computer sales by suppliers in WI?

7) Volum of auto sales by suppliers in Wisconsin to consumers in Illinois?

8) Volum of truck sales by suppliers in Madison to consumers in Illinois?

9) Volum of each product sold by supplier Redfield to consumers in

Chicago?

10) Quantity of milk sold by supplier Redfield to consumers in Chicago?

11)4 each product list quantity sold by suppliers in

Madison to consumers in Chicago versus quantity sold by suppliers in

Chicago to consumers in Madison (result columns will be: product name,

quantity Madison_Chicago, quantity Chicago_Madison?

Explanation / Answer

define cube sales [time, item, branch, location]:

dollars_sold = sum(sales_in_dollars), avg_sales = avg(sales_in_dollars), units_sold = count(*)

define dimension time as (time_key, day, day_of_week, month, quarter, year)

define dimension item as (item_key, item_name, brand, type, supplier_type)

define dimension branch as (branch_key, branch_name, branch_type)

define dimension location as (location_key, street, city, province_or_state, country)

define cube shipping [time, item, shipper, from_location, to_location]:

dollar_cost = sum(cost_in_dollars), unit_shipped = count(*)

define dimension time as time in cube sales

define dimension item as item in cube sales

define dimension shipper as (shipper_key, shipper_name, location as location in cube sales, shipper_type)

define dimension from_location as location in cube sales

define dimension to_location as location in cube sales

Roll-up: The roll-up operation performs aggregation on a data cube, either by climbing-up a concept hierarchy for a dimension or by dimension reduction. Figure shows the result of a roll-up operation performed on the central cube by climbing up the concept hierarchy for location. This hierarchy was defined as the total order street < city < province or state <country.

SELECT P.pid, P.pname, S.sales FROM Sales S, Products P WHERE S.pid=P.pid AND S.locid=1 AND S.timeid=3      AND S.sales > c ORDER BY S.sales DESC

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.