OLAP Functions

Ø  Some features for query processing in oracle include the use of online analytical processing (OLAP) upon the database.
Ø  OLAP features are useful for data warehousing and data mart applications.
Ø  The OLAP operations are performance enhancements.
  • Top N Queries
  • Group By
  • CUBE
  • ROLLUP
ROLLUP Option:
Ø  It is a group by operation and is used to produce subtotals at any level of the aggregation.
Ø  The generated sub totals “Rolled Up” to produce grant total.
Ø  The totaling is based on a one dimensional data hierarchy of grouped information.
Syntax:
GROUP BY ROLLUP (column 1, column 2,…)
Illustrations:
  • Select Deptno, Sum(sal) From Emp group by Rollup(Deptno);
  • Select Job, Sum(sal) From Emp Group by Rollup(Job);
  • Select Job,Avg(Sal) From Emp Group by Rollup(Job);
Passing Multiple Columns To Rollup:
Ø  When multiple columns are passed to Rollup, the Rollup, groups the rows into blocks with the same column values.
Ex:
  • Select Deptno, Job, Sum(sal) Salary From Emp Group by Rollup(Deptno, Job);
  • Select Job, Deptno, Sum(sal) Salary From Emp Group by Rollup(Job, Deptno);
  • Select Job, Deptno, Avg(sal) Average From Emp Group by Rollup(Job, Deptno);
Ø  NULL Values in the output of Rollup operations typically mean that the row contains subtotal or grant total information.
Ø  Use NVL () function for proper meaning.
CUBE Option:
Ø  It is an extension similar to rollup.
Ø  Cube allows specified set of grouping columns and creates sub totals for all possible combinations of them.
Ø  The Result of cube is a summary that shows subtotals for every combination of columns or expressions in the group by clause.
Ø  The implementation of cube is also called as N-dimensional cross tabulation.
Ex:
  • Select Deptno, Job, Sum(sal) Salary From Emp Group by Cube(Deptno, Job);
  • Select Job, Deptno, Sum(Sal) Salary From Emp Group  by Cube(Job, Deptno);
Applying Grouping() Function:
Ø  The Grouping() Function accepts a column and returns 0 or 1.
Ø  Grouping() Function returns 1 when the column value is NULL, and returns 0 when the column value is NOT NULL.
Ø  Grouping() Function is used only upon queries that use ROLLUP or CUBE.
Ex:
  • Select Grouping(Deptno), Deptno, Sum(sal) From Emp Group by Rollup(Deptno);
  • Select Grouping(Job), Job, Sum(sal) From Emp Group by Rollup(Job);
Grouping() with DECODE():
Ø  The Decode() function can be used to convert 1 and 0 returned through Grouping() into a meaningful output.
Ex:
  • Select Decode(Grouping(deptno), 1,’ALL Departments’, Deptno) Departments, Sum(sal) From Emp Group by Rollup(deptno);
  • Select Decode(Grouping(Job), 1,’All Designations’, Job) Designations, Sum(sal) From  Emp Group by Rollup(Job);
Decode() and Grouping() To Converting Multiple Column Values:
Ex:
  • Select Decode(Grouping(Deptno), 1,’All Department’, Deptno) Departments, Decode(Grouping(Job), 1,’All Designations’, Job) Designations, Sum(sal) From Emp Group by Rollup(Deptno, Job);
Grouping() with Decode() and CUBE:
Ex:
  • Select Decode(Grouping(Deptno), 1,’All Departments’, Deptno) Departments, Decode(Grouping(Job), 1,’All Designation’, Job) Designations, Sum(sal) From Emp Group by CUBE(Deptno, Job);
Applying Grouping SETS Clause:
Ø  The Grouping Sets clause is used to get subtotal rows.
Ex:
  • Select Deptno, Job, Sum(sal) From Emp Group by Grouping Sets(Deptno, Job);
Grouping ID() Function:
Ø  The Function is used to filter rows using a having clause to exclude rows that do not contain a subtotal or grand total.
Ø  The Function accepts one or more columns and returns the decimal equivalent of the grouping bit vector.
Ø  The Grouping BIT Vector is computed by combining the results or a call to the grouping() function for each column in order.
Computing the Grouping BIT Vector:
Ø  Grouping() Function returns 1 when the column value is NULL, Else returns 0, based on this concept.
Ø  Grouping_ID() returns 0, when deptno and job are not null’s.
Ø  Grouping_ID() returns 1, If Deptno is not null and job is null.
Ø  Grouping_ID() returns 2, if Deptno is NULL and Job is not null.
Ø  Grouping_ID() returns 3, if Deptno is null and job is null.
Ex:
  • Select Deptno, Job, Grouping(deptno) GDPT, Grouping(Job) GJOB, Grouping_ID(Deptno, Job) GRPID, Sum(sal) From Emp Group by CUBE(Deptno, Job);
Grouping_ID() and Having Clause:
Ex:
  • Select Deptno, Job, Grouping_ID(Deptno, Job) GRPID, Sum(sal) From Emp Group by CUBE(Deptno, Job) Having Grouping_ID(Deptno, Job) > 0;
Representing Column Multiple Times in a Group by clause:
Ø  A column can be represented multiple times in a group by clause.
Ex:
  • Select Deptno, Job, Sum(sal) From Emp Group by Deptno, Rollup(deptno, Job);
  • Select Deptno, Job, Sum(sal) From Emp Group by Deptno, Cube(Deptno, Job);
Applying Group_ID Function:
Ø  The Group_ID() Function is used to remove the duplicate rows returned by group by clause.
Ø  The Group_ID() Does not accept any parameters.
Ø  If ‘N’ Duplicates exist for a particular grouping, group_ID() returns numbers in the range 0 to N-1.
Ex:

  • Select Deptno, Job, Group_ID(), Sum(sal) From Emp Group by Deptno, Rollup(Deptno, Job);
  • Select Deptno, Job, Group_ID(), Sum(sal) From Emp Group by Deptno, CUBE(Deptno, Job);
  • Select Deptno, Job, Group_ID(), Sum(sal) From Emp Group by Deptno, Rollup(Deptno, Job) Having Group_ID() = 0;