Create a tab using the data provided. Then create a multi$-$dimentional CUBE in snowflake to

produce the following report $-$

$1.$ Total Sale value by $-$

a$.$ Each Year

b$.$ Each Quarter

c$.$ Each Month

d$.$ Each Product

e$.$ Each Year,Quarter Combination

f$.$ Each Year,Month Combination

g$.$ Each Year,Product Combination

h$.$ Each Quarter,Month Combination

i$.$ Each Quarter,Product Combination

j$.$ Each Month,Product Combination

k$.$ Each Year,Quarter,Month Combination

l$.$ Each Year,Quarter,Product Combination

m$.$ Each Year,Month,Product Combination

n$.$ Each Quarter,Month,Product Combination

o$.$ Total Sales value.

Below is the dataset:$-$

year quarter month product sales$\_$amount

$2021$ Q$1$ Jan A $1000$

$2021$ Q$1$ Jan B $2000$

$2021$ Q$1$ Jan C $3000$

$2021$ Q$1$ Feb A $1500$

$2021$ Q$1$ Feb B $2500$

$2021$ Q$1$ Feb C $3500$

$2021$ Q$1$ Mar A $2000$

$2021$ Q$1$ Mar B $3000$

$2021$ Q$1$ Mar C $4000$

$2021$ Q$2$ Apr A $1200$

$2021$ Q$2$ Apr B $2200$

$2021$ Q$2$ Apr C $3200$

$2021$ Q$2$ May A $1800$

$2021$ Q$2$ May B $2800$

$2021$ Q$2$ May C $3800$

$2021$ Q$2$ Jun A $2200$

$2021$ Q$2$ Jun B $3200$

$2021$ Q$2$ Jun C $4200$

SNOWFLAKE CONNECTION INSTRUCTIONS

pip$3$ install $--$upgrade snowflake$-$connector$-$python

Login to snowflake, create a database $(\text{'}$test$\text{'}$ in this script and a schema 'sales' in this script$)$

CODE TO CONNECT TO SNOWFLAKE from python

#$!/$usr$/$bin$/$env python

import snowflake.connector

# Gets the version

ctx $=$ snowflake.connector.connect$($

user$=$'YOUR USER ID$\text{'},$

password$=$'YOUR PASSWORD',

account$=$'ACCOUNT NAME'

$)$

cs $=$ ctx$.$cursor$()$

try:

cs$.$execute$("$USE DATABASE TEST"$)$

cs$.$execute$("$USE SCHEMA SALES"$)$

cs$.$execute$($

"CREATE OR REPLACE TABLE $"$

"test$\_$table$($col$1$ integer, col$2$ string$)")$

cs$.$execute$("$CREATE TABLE sales$\_$data $($ year INT, quarter VARCHAR$(2),$ month VARCHAR$(3),$ product VARCHAR$(10),$

sales$\_$amount DECIMAL$(10,2))")$

cs$.$execute$("$SELECT current$\_$version$()")$

one$\_$row $=$ cs$.$fetchone$()$

print$($one$\_$row$[0])$

finally:

cs$.$close$()$

ctx$.$close$()$

A$.$ Get the following result using ROLLUP operation on the data cube created as in above questions.

$1.$ Total Sale value by $-$

a$.$ Each Year

b$.$ Each Quarter

c$.$ Each Month

d$.$ Each Product

e$.$ Each Year,Quarter Combination

f$.$ Each Year,Month Combination

g$.$ Each Year,Product Combination

h$.$ Each Quarter,Month Combination

i$.$ Each Quarter,Product Combination

j$.$ Each Month,Product Combination

k$.$ Each Year,Quarter,Month Combination

l$.$ Each Year,Quarter,Product Combination

m$.$ Each Year,Month,Product Combination

n$.$ Each Quarter,Month,Product Combination

o$.$ Total Sales value

B$.$ Use DRILL DOWN on Quarter to create the CUBE on dimention $($year$,$ month, product, salse

amount$)$ and perform the following operation

a$.$ Each Year

b$.$ Each Month

c$.$ Each Product

d$.$ Each Year,Month Combination

e$.$ Each Year,Product Combination

f$.$ Each Month,Product Combination

g$.$ Each Year,Month,Product Combination

C$.$ Create a dice from cube created in last assignment on product A & C$,$ month having ODD

number and Quarter having EVEN number and execute the following opertions $-$

$1.$ Total Sale value by $-$

a$.$ Each Year

b$.$ Each Quarter

c$.$ Each Month

d$.$ Each Product

e$.$ Each Year,Quarter Combination

f$.$ Each Year,Month Combination

g$.$ Each Year,Product Combination

h$.$ Each Quarter,Month Combination

i$.$ Each Quarter,Product Combination

j$.$ Each Month,Product Combination

k$.$ Each Year,Quarter,Month Combination

l$.$ Each Year,Quarter,Product Combination

m$.$ Each Year,Month,Product Combination

n$.$ Each Quarter,Month,Product Combination

o$.$ Total Sales value