OLAP Features in SQL Server

Introduction to OLAP Features

• OLAP means Online Analytical Processing.
• OLAP feature are usefull for data warehousing and data mart application
• OLAP operations are performed on

               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.