Social Icons

CUBE Function

Teradata Cube Function
I hope you all familiar with ROLLUP Function now. If not please read before get into CUBE Function.
  • The CUBE function can be called as Superset of RollUP function.
  • Because It returns the same result set as ROLLUP along with provides some additional information.
  • Instead producing a 'hierarchy' of results, CUBE looks at all permutations of the specified dimensions.
Let take an example , Produce a 'CUBE' report showing employee salaries across the dimensions of department and manager.

SELECT DEPT_NO AS DEPTNO
 ,MGR_EMP_NO AS MANAGER
 ,SUM(SALARY_AMOUNT) AS SUMSAL
 FROM EMPLOYEE
 WHERE DEPT_NO < 402
GROUP BY CUBE (DEPT_NO,MGR_EMP_NO)
 ORDER BY 1,2;


DEPTNOMANAGERSUMSAL
??591925(all depts, all managers)
?801286750(all depts, one manager)
?1003207725
?101958700
?102538750
100?100000(one dept, all managers)
100801100000(one dept, one manager)
201?73450(one dept, all managers)
20180134700
201102538750
301?116400
30180157700
301101958700
302?56500
30280156500
401?245575
40180137850
4011003207725

If you observe there are four additional rows (green) are included in the output.
These four rows represent the totals by manager.This report shows all possible permutations of the two specified dimensions.

 Reversing CUBE Parameters:
 What happens when we flip the sequence of the CUBE parameters? Consider the following:

 SELECT DEPT_NO AS DEPTNO
 ,MGR_EMP_NO AS MANAGER
 ,SUM(SALARY_AMOUNT) AS SUMSAL
 FROM EMPLOYEE
 WHERE DEPT_NO < 402
GROUP BY CUBE (MGR_EMP_NO,DEPT_NO)
 ORDER BY 1,2;

Yes, The output is exactly  returns same.There is no difference in output becacuse CUBE function looks at all permutations of the specified parameters, order of the arguments doesn't matter - unlike ROLLUP where it does matter.

 we can use the GROUPING and COALESCE functions to make the report more readable.

 SELECT CASE GROUPING (department_number)
 WHEN 1 THEN 'All Depts'
 ELSE (COALESCE (department_number,'Null Dept') ) END AS Deptno
 ,CASE GROUPING (manager_employee_number)
 WHEN 1 THEN 'All Managers'
 ELSE manager_employee_number END AS Mngr
 ,SUM(salary_amount) AS SumSal
 FROM employee
 WHERE department_number < 402
 GROUP BY CUBE (manager_employee_number,department_number)
 ORDER BY 1,2;
 

DeptnoMngrSumSal
100801100000
100All Managers100000
20180134700
201102538750
201All Managers73450
30180157700
301101958700
301All Managers116400
30280156500
302All Managers56500
40180137850
4011003207725
401All Managers245575
All Depts801286750
All Depts1003207725
All Depts101958700
All Depts102538750
All DeptsAll Managers591925

As more dimensions are added to the cube, more permutations and thus more rows will result.

 If n is the number of dimensions in a cube, then 2**n represents the number of permutations generated:
 2 dimensions - 4 permutations
 3 dimensions - 8 permutations
 4 dimensions - 16 permutations
 etc.
Example

Produce a 'cube' report showing employee salaries across the three dimensions of department, manager and job code for department numbers less than 302.


SELECT CASE GROUPING (department_number)
 WHEN 1 THEN 'All Depts'
 ELSE (COALESCE (department_number,'Null Dept') ) END AS Deptno

,CASE GROUPING (manager_employee_number)
 WHEN 1 THEN 'All Managers'
 ELSE manager_employee_number END AS Mngr

 ,CASE GROUPING (job_code)
 WHEN 1 THEN 'All Job Codes'
 ELSE (COALESCE (job_code,'Null Job Code') ) END AS Job

,SUM(salary_amount) AS SumSal
 FROM employee
 WHERE department_number < 302
GROUP BY CUBE (manager_employee_number,department_number, job_code)
 ORDER BY 1,2;


DeptnoMngrJobSumSal
100801111100100000
100801All Job Codes100000
100All ManagersAll Job Codes100000
100All Managers111100100000
20180121110034700
201801All Job Codes34700
2011025All Job Codes38750
201102522210138750
201All Managers21110034700
201All ManagersAll Job Codes73450
201All Managers22210138750
301801All Job Codes57700
30180131110057700
3011019All Job Codes58700
301101931210129450
301101931210229250
301All Managers31210229250
301All Managers31110057700
301All ManagersAll Job Codes116400
301All Managers31210129450
All Depts801All Job Codes192400
All Depts801111100100000
All Depts80121110034700
All Depts80131110057700
All Depts101931210129450
All Depts1019All Job Codes58700
All Depts101931210229250
All Depts1025All Job Codes38750
All Depts102522210138750
All DeptsAll ManagersAll Job Codes289850
All DeptsAll Managers31110057700
All DeptsAll Managers21110034700
All DeptsAll Managers111100100000
All DeptsAll Managers31210129450
All DeptsAll Managers31210229250
All DeptsAll Managers22210138750
All DeptsAll Managers31210229250
All DeptsAll Managers22210138750

To help interpret the output, use the following color-coding scheme:

(department,manager, job_code)       Each unique combo of dept/mgr/job
(department, manager)                        Each unique combo of dept/mgr
(department, job_code)                        Each unique combo of dept/job
(manager, job_code)                             Each unique combo of mgr/job
(department)                                         All depts
(manager)                                             All mgrs
(job_code)                                             All jobs
( )                                                           Grand Total

4 comments:

  1. can you post all td 14 features

    ReplyDelete
    Replies
    1. Thanks Malleswara Reddy.. Shortly will post all TD 14 Feature.. Keep Browsing :)

      Delete