Introduction to OLAP Features

1. Group By
2. Rollup
3. Cube
1. Rollup
  • Used to generate a reports that contains subtotals & totals.
  • SUM is a aggregate function.
  • The total is based on A one dimensional data hierarchy of grouped information.
Syntax

SELECT <Column(s)> FROM <Table_Name> Group By Rollup (Column1, Column2...)
Create Table
Select Query

Rollup Query
2. CUBE
  • The result set is multidimensional cube i.e cross tabulation of all possible combination of the columns.
  • It allows to take a specified set of grouping columns and create sub totals for all possible combinations.
  • In our example CUBE operation generated rows for possible combinations of values from Id, Ename & Salary.
Syntax

SELECT <Column(s)> FROM <Table_Name> Group By Cube(Column1, Column2...)

Cube Query


Null
  • These are the unknown values.
  • All the null values are considered equal & all put into one NULL group.

Summary

This blog provided an introduction to OLAP features in SQL Server.