SQL Server T-SQL OLAP WITH CUBE Operator Example
T-SQL developers and Business Intelligence professionals probably know Microsoft SQL Server 2005 has new SQL OLAP commands that empowers the OLAP reporting tools that developers have in T-SQL.
If you want to learn more about SQL/OLAP enhancements in SQL Server 2005, please refer to sql tutorial T-SQL WITH CUBE Example in SQL Server.
I want to give an other SQL Server T-SQL OLAP With Cube operator example in this sql tutorial.
SQL/OLAP WITH CUBE Operator Example
Let's create a real life scenario of course with dummy numbers for T-SQL With Cube example in this sql tutorial.
Assume that you have a database table where you store number of tourists visiting your hotels as a travel agency.
You keep the date and transportation medium as well as the destination point in your SQL Server database table.
And your final target is to build sql queries which will help you analyze sql data.
First create sample sql database table for this WITH CUBE SQL example.
Create Table TouristVisits
(
[Arrival Date] date,
[Transportation] varchar(20),
[Destination] varchar(20),
[Count] int
)
Insert Into TouristVisits Values
('20100615', 'Plane', 'Antalya', 1000),
('20100625', 'Plane', 'Cesme', 100),
('20100628', 'Ship', 'Bodrum', 2000),
('20100712', 'Car', 'Cesme', 2000),
('20100712', 'Plane', 'Cesme', 5000),
('20100716', 'Ship', 'Antalya', 500),
('20100722', 'Plane', 'Cesme', 10000),
('20100727', 'Car', 'Bodrum', 3000)
And the t-sql With Cube sql query which can be used for a sample sql data analyze is as follows:
SELECT
Transportation, Destination, SUM([Count]) Total
FROM TouristVisits
GROUP BY Transportation, Destination
WITH CUBE
When sql programmers execure the above SQL Server T-SQL OLAP WITH CUBE query the resultset will be as seen in the below screenshot.
T-SQL developers can alter the sql WITH CUBE query as follows to get more information from the SQL OLAP query using SQL Server WITH CUBE operator.
The changed t-sql SELECT statement will give results in time dimension as well.
SELECT
DATENAME(MONTH, [Arrival Date]),
Transportation, Destination, SUM([Count]) Total
FROM TouristVisits
GROUP BY DATENAME(MONTH, [Arrival Date]), Transportation, Destination
WITH CUBE
