T-SQL WITH CUBE Example in SQL Server

SQL/OLAP provides methods and tools to analyze data using SQL.
SQL/OLAP enhancements in T-SQL for Business Intelligence solutions with SQL Server 2005 can be listed as follows :
- Ranking functions,
- TOP n,
- Pivot and UnPivot, and
- CUBE and ROLLUP operator.

Let's start this tutorial with sql WITH CUBE T-SQL OLAP command in SQL Server 2005 and later T-SQL.

T-SQL CUBE Operator

The SQL OLAP operator we will give examples here in this sql tutorial is Cube operator in SQL Server 2005
T-SQL Cube operator is very similar to SQL Group By operator with only difference of additional aggregate summary lines.

T-SQL developers know that Group By sql operator takes a list of sql columns that define the groups on which the aggregation functions like SUM() will be calculated.
The columns listed in Group By clause take place in the result set of the SQL Select statement beside the aggregate columns.

SQL Cube operator adds some additional rows to this resultset. And the select resultset turns into an OLAP cube where sql developers can also find the aggregation results on Group By columns. That is like aggregation on aggregated results.

T-SQL CUBE Example

In a software development company, a project manager has reported the following resource usage giving details on which developer has how much worked on which tasks.

To visualize this T-SQL Cube example, let's first create the below sql table to insert sample data.
Then execute the sql Insert statement to populate sql table with sample data for t-sql WITH Cube example.
Careful eyes will catch that the sql insert command is the new syntax that is new with T-SQL enhancements in SQL Server 2008.

Below, t-sql developers can find a basic sql WITH CUBE query which also helps developers to figure out the tsql WITH CUBE syntax.
Please note that the WITH CUBE operator is used right after GROUP BY columns list in sql queries.

The output of the above T-SQL SELECT query using WITH CUBE is as follows :

Using the above sample sql data, t-sql developers and Business Intelligence developers can create more detailed OLAP report data using the below t-sql CUBE command.
This sql SELECT query using WITH CUBE operator will return aggregate data for time dimension as well.

As a summary, Microsoft SQL Server has introduced With Cube, With Rollup, and similar SQL OLAP commands for use of Business Intelligence and SQL Cube structures in T-SQL and in SQL queries by sql developers.