FIT3003 - W8 - Basic OLAP (CUBE & partial CUBE )

type

Post

Created date

Nov 7, 2022 11:15 AM

Orders of attributes inside `CUBE` matter

💡

When using CUBE, think about which ones are ‘NULL’. Those which are (NULL) need to be placed at LAST.


-- wrong
SELECT c.time_id As Period, 
 p.emp_num AS Pilot,
 c.mod_code As Model,
 SUM(c.tot_fuel) 
FROM dw.charter_fact c, dw.pilot p, dw.model m
WHERE
c.emp_num = p.emp_num AND
c.mod_code = m.mod_code AND
c.time_id LIKE '19951%' AND 
p.pil_license = 'COM' AND
c.mod_code = 'C-90A'
GROUP BY CUBE(c.mod_code, p.emp_num, c.time_id)
ORDER BY c.time_id;

-- correct
SELECT c.time_id As Period, 
 p.emp_num AS Pilot,
 c.mod_code As Model,
 SUM(c.tot_fuel) 
FROM dw.charter_fact c, dw.pilot p, dw.model m
WHERE
c.emp_num = p.emp_num AND
c.mod_code = m.mod_code AND
c.time_id LIKE '19951%' AND 
p.pil_license = 'COM' AND
c.mod_code = 'C-90A'
GROUP BY CUBE( c.time_id, p.emp_num, c.mod_code)
ORDER BY c.time_id;

What is `GROUPING` ?

What is `DECODE` ?

What is `PARTIAL CUBE` ?

Problem

Code


SELECT 
 DECODE(GROUPING(time_id), 1, 'All Periods', 
time_id) As Period, 
 DECODE(GROUPING(c.emp_num), 1, 'All Pilots', 
c.emp_num) AS Pilot,
 DECODE(GROUPING(mod_code), 1, 'All Models', 
mod_code) As Model,
 SUM(tot_fuel) 
FROM dw.charter_fact c, dw.pilot p
WHERE c.emp_num = p.emp_num
AND time_id LIKE '19951%'
AND mod_code = 'C-90A'
AND p.pil_license = 'COM'
GROUP BY CUBE (time_id, c.emp_num, mod_code)
ORDER BY time_id;

Solution

Code


-- Partial Cube
SELECT 
 DECODE(GROUPING(time_id), 1, 'All Periods', 
time_id) As Period, 
 DECODE(GROUPING(c.emp_num), 1, 'All Pilots', 
c.emp_num) AS Pilot,
 DECODE(GROUPING(mod_code), 1, 'All Models', 
mod_code) As Model,
 SUM(tot_fuel) 
FROM dw.charter_fact c, dw.pilot p
WHERE c.emp_num = p.emp_num
AND time_id LIKE '19951%'
AND mod_code = 'C-90A'
AND p.pil_license = 'COM'
GROUP BY CUBE (time_id, c.emp_num), mod_code
ORDER BY time_id;

Orders of attributes inside CUBE matter

What is GROUPING ?

What is DECODE ?

What is PARTIAL CUBE ?

Problem

Solution

Orders of attributes inside `CUBE` matter

What is `GROUPING` ?

What is `DECODE` ?

What is `PARTIAL CUBE` ?