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.                    

Next Recommended Reading New features in SQL Server 2005