SQL Fullcube

Fullcube

use pubs
go

if exists (select * from sysobjects where name='automobile_sales_detail' and type='U')
	drop table automobile_sales_detail
go

create table automobile_sales_detail
(
rowid		int	not null identity ,
model		char(6)	null ,
year		smallint not null check (year between 1985 and 2000),
color		char(5)	null,
units_sold	int	not null
) 
go

-- insert automobile_sales_detail values ('Chevy',1990,'Red',5)
-- insert automobile_sales_detail values ('Chevy',1990,'White',87)
-- insert automobile_sales_detail values ('Chevy',1990,'Blue',62)
insert automobile_sales_detail values ('Chevy',1991,'Red',54)
insert automobile_sales_detail values ('Chevy',1991,'White',95)
insert automobile_sales_detail values ('Chevy',1991,'Blue',49)
insert automobile_sales_detail values ('Chevy',1992,'Red',31)
insert automobile_sales_detail values ('Chevy',1992,'White',54)
insert automobile_sales_detail values ('Chevy',1992,'Blue',71)
-- insert automobile_sales_detail values ('Ford',1990,'Red',64)
insert automobile_sales_detail values ('Ford',1990,'White',62)
insert automobile_sales_detail values ('Ford',1990,'Blue',63)
-- insert automobile_sales_detail values ('Ford',1991,'Red',52)
insert automobile_sales_detail values ('Ford',1991,'White',9)
insert automobile_sales_detail values ('Ford',1991,'Blue',55)
-- insert automobile_sales_detail values ('Ford',1992,'Red',27)
insert automobile_sales_detail values ('Ford',1992,'White',62)
insert automobile_sales_detail values ('Ford',1992,'Blue',39)
go

-- The standard datacube works fine - but by design, does not generate 
-- missing combinations.  For example, if I want to refer to output as a 
-- chart to see how many red Fords were sold, there is no such 0 row.  
-- While the easiest method would be to add a few dummy rows where there is missing values, 
-- I will attempt to do this instead with UNION and views or derived tables.
select sum(units_sold),model,year,color
from automobile_sales_detail
group by model,year,color with cube
go


-- The select statement works as I'd expect and unions all the 
-- real values with a dummy value of 0 for every row.
select D.units_sold,D.model,D.year,D.color
	from automobile_sales_detail D
	UNION ALL
	select DISTINCT 0,
	A.model,C.year,B.color
	from 
	automobile_sales_detail A
	CROSS JOIN
	automobile_sales_detail B
	CROSS JOIN
	automobile_sales_detail C 


-- I'd like to take the previous query and make it a view.  I could then do the 
-- CUBE on the view to achieve objective of fully populating the cube, and
-- generate the missing values.

if exists (select * from sysobjects where name='FULLCUBE' and type='V')
	DROP VIEW FULLCUBE
go

-- But trying to put same select into a view says there are ambiguous columns 
-- but every column is qualified.
CREATE VIEW FULLCUBE 
	(
	units_sold,
	model,
	year,
	color
	)
	AS
	(
	select D.units_sold,D.model,D.year,D.color
	from automobile_sales_detail D
	UNION ALL
	select DISTINCT 0,
	A.model,C.year,B.color
	from 
	automobile_sales_detail A
	CROSS JOIN
	automobile_sales_detail B
	CROSS JOIN
	automobile_sales_detail C 
	)
go

select sum(units_sold),model,year,color
from FULLCUBE
group by model,year,color with cube
go

-- as a variation, do it with a derived table instead of a view
SELECT sum(T1.units_sold),T1.model,T1.year,T1.color
from 		(
		select D.units_sold,D.model,D.year,D.color
		from automobile_sales_detail D
		UNION
		select DISTINCT 0,
		A.model,C.year,B.color
		from 
		automobile_sales_detail A
		CROSS JOIN
		automobile_sales_detail B
		CROSS JOIN
		automobile_sales_detail C 
		)
		as T1
group by T1.model,T1.year,T1.color with cube
go


if exists (select * from sysobjects where name='auto_cube' and type='V')
	DROP VIEW auto_cube
go

create view auto_cube (units, model, year, color) as
select sum(units_sold),
CASE 	WHEN (grouping(model)=1) THEN 'ALL'
	ELSE isnull(model,'????')
	END,
CASE	WHEN (grouping(year)=1) THEN 'ALL'
	ELSE isnull(convert(char(6),year),'????')
	END,
CASE	WHEN (grouping(color)=1) THEN 'ALL'
	ELSE isnull(color,'????')
	END
from FULLCUBE
group by model,year,color with cube
go

select *
from auto_cube

insert automobile_sales_detail values (NULL,1992,NULL,39)
go

select sum(units_sold),model,year,color
from automobile_sales_detail
group by model,year,color with cube
go

select *
from auto_cube
go