CCDS$-221$ Data Warehouse

$1|$ P a g e

Lab # $10$

This Lab has $2$ parts. First, you develop SQL SLECT statements for OLAP queries in SQL and then, in

the second part you Visualize OLAP Query results as PivotTable using MS$-$Excel.

Statement purpose

To understand how to develop$/$simulate OLAP queries using SQL statements and generate

PivotTables to visualize results.

Activity Outcomes

Students will be able to develop SQL statements to simulate OLAP operators $($Drill$-$Down, Roll$-$Up$...)$

using aggregate functions.

Instructor Note

Implement each question below and submit its code and result $($screenshot$)$ displayed by Oracle.

Tables to use

You will use the Star schema tables $($F$\_$ORDERS, D$\_$ORDERS, D$\_$SALESMAN, D$\_$TIME, and

D$\_$CUSTOMERS$)$ created and populated in the previous Labs $($Labs $7,8$ & $9).$

The star schema you will reuse in this lab is remembered in Figure $9($extracted from Lab $8).$

Figure $9.$ Star Schema $($Figure $8\mathrm{.}2$ in Lab $8)$ implemented in previous labs.

D$\_$CUSTOMERS

ID$\_$C

Gender

C$\_$FName

C$\_$LName

D$\_$TIME

ID$\_$T

Month$-$No

Quarter Month$-$Name

D$\_$SALESMAN

ID$\_$S

SName

F$\_$ORDERS

ORDER$\_$AMOUNT

Year

Type

D$\_$ORDERS

ID$\_$O

Status

OMode

Date

University of Jeddah

College of Computer Science &

Engineering

IST Dept.

Academic Year $2020/2021$

CCDS$-221$ Data Warehouse

$2|$ P a g e

Lab $10$ Part I

Questions to Answer

Develop a SELECT statement for each of the following requirements:

$$ Querying the Fact only

$1.$ Display the Number of Orders in the D$\_$ORDERS table.

$2.$ Display the Number of Orders in the F$\_$ORDERS table $($Check that you get the same number from

the dimension table D$\_$ORDERS$).$

$3.$ Display the Lowest, Highest and Average Amount of all Orders.

$4.$ For each customer, display the Lowest, Highest and Average Amount of orders.

$$ Query the Fact and one dimension

$5.$ Display the customers$($id and Name$)$ having an average Amount of orders greater than the double

of the average amount of all orders.

$--->$ You should get $4$ rows.

$6.$ Number of Orders by Salesman $($ID$\_$S and SName are required$)$

$--->$ You should get $4$ rows.

$7.$ Number of Orders by customer $($Id$,$ FName, LName$)$

$8.$ Modify your previous answer to display the Number of Orders and their Total by Customer $($Id$,$

FName, LName$)$ sorted by the number of order descending.

$9.$ Display each Customer $($Id$,$ C$\_$FName, C$\_$LName$)$ having the total amount of its Orders greater

than $80\%$ of the average of all Orders.

$$ Queries using two dimensions and the fact

$10.$ Display the Number of direct Orders $($OMode $=$DIRECT$)$ by Customer $($Id$,$ C$\_$FName, C$\_$LName$)$

and Salesman $($identifier and name$).$

$--->$ You should get $69$ rows.

$11.$ Total Amount of Orders by Year and Customer ID$,$ sorted by Year and Customer.

$--->$ You should get $68$ rows

$$ Simulate the Drill$-$Down operator

$12.$ Transform query $11$ to display the result of the following OLAP operation:

DrillDown$($Q$11,$ D$\_$Time, Quarter$)--$ Where Q$11$ is the result of question $11.$

University of Jeddah

College of Computer Science &

Engineering

IST Dept.

Academic Year $2020/2021$

CCDS$-221$ Data Warehouse

$3|$ P a g e

$13.$ Transform query $12$ to display the result of the following OLAP operation

DrillDown$($Q$12,$ D$\_$Time, Month$\_$No$)--$ Where Q$12$ is the result of question $12.$

$$ Simulate the Roll$-$Up operator

Given the Following query QA$.$

Query QA$.$

SELECT OMODE, YYEAR, QUARTER, MONTH$\_$NO$,$ SUM $($ORDER$\_$AMOUNT$)$ AMOUNT

FROM F$\_$ORDERS, D$\_$TIME, D$\_$ORDERS

WHERE F$\_$ID$\_$T $=$ ID$\_$T

AND F$\_$ID$\_$O $=$ ID$\_$O

GROUP BY OMODE $,$ YYEAR, QUARTER, MONTH$\_$NO;

$14.$ Transform query QA to display the result of the following OLAP Operation: