CCDS$-221$ Data Warehouse

$4|$ P a g e

Lab $10$ Part II

In this section of the Lab, you will learn how to practice the theoretical knowledge you have acquired

in the OLAP ALGEBRA chapter. To do so$,$ you will use the MS$-$Excel GUI $($easy$-$to$-$use by yourself and

decision$-$makers$)$ for generating$/$handling $2-$Dimensional tables$($known as PivotTable in MS$-$Excel$)$ for

results visualization. This GUI has the merit to hide the syntax of the OLAP operators.

First, have a look to query$$Q$1$ expressed on our ORDERS star schema implemented in previous labs.

SELECT F$\_$ID$\_$C $,$ C$\_$FNAME, C$\_$LNAME, YYEAR, QUARTER, SUM$($ORDER$\_$AMOUNT$)$

Amount$\_$Orders

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

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

AND F$\_$ID$\_$C $=$ ID$\_$C

GROUP BY YYEAR, QUARTER, F$\_$ID$\_$C $,$ C$\_$FNAME, C$\_$LNAME, YYear

ORDER BY $1,4,5,6$ ;

$/*98$ rows selected.$*/$

SQL Query Q$1.$

Lab$10-$Appendix depicts the result of query Q$1(98$ rows$).$

$1.$ Run MS$-$Excel on your computer.

$2.$ Open the file Q$1.$csv provided with this lab; it contains the full result of query Q$1.$

$3.$ In the following you will use an Excel feature called PivotTable to change the data visualization

into a $2-$Dimensional table $($called PivotTable in MS$-$Excel Terminology$).$ To do so$,$ follow the steps

below:

$-$ Put the cursor anywhere inside the sheet $($do not select any cell$)$ then

$-$ Click on the Insert Tab,