SQL Fullcube Code Library

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