OlapTrain_P2_CubeQueries

A) "Level" conditions are used to replace "group by".
B) Aggregation occurs in cube and respects cube aggregation rules.
C) No "sum/group by" is required for summary queries
D) OLAP calculations are simply selected as 'data columns‘

SELECT s.sales,

s.channel,
s.time,
s.geography,
s.product

FROM SALES_CUBE_VIEW s
WHERE s.channel

= 'ALL_CHANNELS’

AND

s.time

= 'ALL_YEARS‘

AND

s.geography = 'ALL_REGIONS'

AND

s.product

= 'ALL_PRODUCTS';

SALES CHANNEL TIME GEOGRAPHY PRODUCT

417515017 ALL_CHANNELS ALL_YEARS

ALL_REGIONS ALL_PRODUCTS

Elapsed: 00:00:00.031

SELECT s.sales,

s.channel,

c.long_description,

s.time,
s.geography,
s.product

FROM sales_cube_view s

JOIN channel_sales_channel_view c

ON c.dim_key = s.channel

WHERE s.channel = 'ALL_CHANNELS'

AND s.time = 'ALL_YEARS'
AND s.geography = 'ALL_REGIONS'
AND s.product

= 'ALL_PRODUCTS';

SALES CHANNEL LONG_DESCRIPTION TIME GEOGRAPHY PRO

417515017 ALL_CHANNELS All Channels

ALL_YEARS ALL_REGIONS A

Elapsed: 00:00:00.206

SELECT s.sales,

s.channel,

c.long_description,

s.time,
s.geography,
s.product

FROM sales_cube_view s

JOIN channel_sales_channel_view c

ON c.dim_key = s.channel

WHERE s.channel = 'ALL_CHANNELS'

AND s.time = 'ALL_YEARS'
AND s.geography = 'ALL_REGIONS'
AND s.product

= 'ALL_PRODUCTS';

SALES CHANNEL LONG_DESCRIPTION TIME GEOGRAPHY PRO

417515017 ALL_CHANNELS All Channels

ALL_YEARS ALL_REGIONS A

Elapsed: 00:00:00.206

SELECT s.sales,

s.channel, c.long_description,

c.level_name

s.time,
s.geography,
s.product

FROM sales_cube_view s
JOIN channel_sales_channel_view c

ON c.dim_key = s.channel

WHERE

s.channel = 'ALL_CHANNELS'

AND s.time = 'ALL_YEARS'
AND s.geography = 'ALL_REGIONS'
AND s.product

= 'ALL_PRODUCTS';

SALES LONG_DESCRIPTION LEVEL_NAME TIME

GEOGRAPHY PRODUCT

---------- ------------------------------------------------------------ ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ -------------------------------
80443485.6 Catalog CHANNEL ALL_YEARS

ALL_REGIONS ALL_PRODUCTS

15659829 New York Retail CHANNEL ALL_YEARS

ALL_REGIONS ALL_PRODUCTS

9150317.77 Lisbon Retail CHANNEL ALL_YEARS

ALL_REGIONS ALL_PRODUCTS

23141158.5 San Francisco Retail CHANNEL ALL_YEARS

ALL_REGIONS ALL_PRODUCTS

164498831 Internet CHANNEL ALL_YEARS

ALL_REGIONS ALL_PRODUCTS

28676863.3 London Retail CHANNEL ALL_YEARS

ALL_REGIONS ALL_PRODUCTS

SELECT s.sales,

c.long_description,
t.long_description,
g.long_description,
p.long_description

FROM sales_cube_view s
JOIN channel_sales_channel_view c ON s.channel

= c.dim_key

JOIN time_calendar_view

t ON s.time

= t.dim_key

JOIN geography_regional_view

g ON s.geography = g.dim_key

JOIN product_standard_view

p ON s.product

= p.dim_key

WHERE c.level_name = 'ALL_CHANNELS'

AND  t.level_name = 'ALL_YEARS'
AND  g.level_name = 'ALL_REGIONS'
AND  p.level_name = 'ALL_PRODUCTS';

SALES LONG_DESCRIPTION LONG_DESCRIPTION

LONG_DESCRIPTION

417515017 All Channels All Years

All Regions All Products

Elapsed: 00:00:00.318

SELECT s.sales,

c.long_description,
t.long_description,
g.long_description,
p.long_description

FROM sales_cube_view s
JOIN channel_sales_channel_view c ON s.channel

= c.dim_key

JOIN time_calendar_view

t ON s.time

= t.dim_key

JOIN geography_regional_view

g ON s.geography = g.dim_key

JOIN product_standard_view

p ON s.product

= p.dim_key

WHERE c.level_name = '

CHANNEL

AND  t.level_name = 'ALL_YEARS'
AND  g.level_name = 'ALL_REGIONS'
AND  p.level_name = 'ALL_PRODUCTS';

SALES LONG_DESCRIPTION LONG_DESCRIPTION

LONG_DESCRIPTION

---------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------
80443485.6 Catalog All Years

All Regions All Products

15659829 New York Retail All Years

All Regions All Products

9150317.77 Lisbon Retail All Years

All Regions All Products

23141158.5 San Francisco Retail All Years

All Regions All Products

164498831 Internet All Years

All Regions All Products

28676863.3 London Retail All Years

All Regions All Products

SELECT s.sales,

c.long_description,
t.long_description,
g.long_description,
p.long_description

FROM sales_cube_view s
JOIN channel_sales_channel_view c ON s.channel

= c.dim_key

JOIN time_calendar_view

t ON s.time

= t.dim_key

JOIN geography_regional_view

g ON s.geography = g.dim_key

JOIN product_standard_view

p ON s.product

= p.dim_key

WHERE c.level_name = 'ALL_CHANNELS'

AND t.level_name = '

CALENDAR_YEAR

AND g.level_name = 'ALL_REGIONS'
AND p.level_name = 'ALL_PRODUCTS';

SALES LONG_DESCRIPTION LONG_DESCRIPTION

LONG_DESCRIPTION

120335758 All Channels CY2007

All Regions All Products

138960159 All Channels CY2008

All Regions All Products

158219099 All Channels CY2009

All Regions All Products

Elapsed: 00:00:00.338

1. Using Level and Member Conditions in a Query

Sales by Class (Channel), Department (Product), and Quarters in 2007(Time).
A geography column is not in query, so the "ALL_REGIONS" condition must be added in order to
leverage aggregation over geography.

SELECT c.class_long_description

as class,

p.department_long_descript as dept,
t.calendar_quarter_long_de as qtr,
round(s.sales) as sales

FROM sales_cube_view s
JOIN channel_sales_channel_view c ON s.channel

= c.dim_key

JOIN time_calendar_view

t ON s.time

= t.dim_key

JOIN geography_regional_view

g ON s.geography = g.dim_key

JOIN product_standard_view

p ON s.product

= p.dim_key

WHERE c.level_name = 'CLASS'

AND t.level_name = 'CALENDAR_QUARTER'
AND t.calendar_year_long_descr = 'CY2009'

--> Time filtered for 2009

only

AND g.level_name = 'ALL_REGIONS'

--> LEVEL_NAME can be used for "All" condition

AND p.level_name = 'DEPARTMENT'

ORDER BY c.class_long_description,

p.department_long_descript,
t.end_date;

CLASS DEPT QTR

-------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------- ----------
Direct               Cameras and Camcorders                                                                               Q1-CY2009                                   1242385
Direct               Cameras and Camcorders                                                                               Q2-CY2009                                   1125521
Direct               Cameras and Camcorders                                                                               Q3-CY2009                                   1354490
Direct               Cameras and Camcorders                                                                               Q4-CY2009                                   1443028
Direct               Computers                                                                                            Q1-CY2009                                  13917490
Direct               Computers                                                                                            Q2-CY2009                                  11756607
Direct               Computers                                                                                            Q3-CY2009                                  12865030
Direct               Computers                                                                                            Q4-CY2009                                  14308176
Direct               Portable Music and Video                                                                             Q1-CY2009                                   1945639

1. Add calculations to the previous query.

Calculations are exposed as columns and computed by the OLAP engine.

Query includes time series, year-to-date, and custom calculations

SELECT c.class_long_description

as class,

p.department_long_descript as dept,
t.calendar_quarter_long_de as qtr,
round(s.sales)

as sales,

round(s.SALES_YTD)

as ytd,

-- sales year to date (YTD)

round(s.SALES_YTD_PY_PCT_CHG, 2) as ytd_PY_pct_chg,

-- sales YTD

compared to last year

how_is_sales_ytd

-- How is sales performing YTD (compared to last year)

FROM sales_cube_view s
JOIN channel_sales_channel_view c ON s.channel

= c.dim_key

JOIN time_calendar_view

t ON s.time

= t.dim_key

JOIN geography_regional_view

g ON s.geography = g.dim_key

JOIN product_standard_view

p ON s.product

= p.dim_key

WHERE c.level_name = 'CLASS'

AND t.level_name = 'CALENDAR_QUARTER'
AND t.calendar_year_long_descr = 'CY2009'

--> Time filtered for 2009 only

AND g.level_name = 'ALL_REGIONS'

--> LEVEL_NAME can be used for "All" condition

AND p.level_name = 'DEPARTMENT'

ORDER BY c.class_long_description,

p.department_long_descript,
t.end_date;

CLASS DEPT QTR

1. show multiple levels of aggregation at the same time using a simple query.

Show different levels for time (month, quarter and year).

The cube/dimensions are represented as embedded totals. In this case, select from the long

description or short description column instead of the dimension level description column. This is the
only other change required for the query - all the calculations work out perfectly.

SELECT c.long_description

as channel,

p.long_description

as product,

t.long_description

as time,

round(s.sales)

as sales,

round(s.SALES_YTD)

as ytd,

-- sales year to date (YTD)

round(s.SALES_YTD_PY_PCT_CHG, 2) as ytd_PY_pct_chg,

-- sales YTD

compared to last year

how_is_sales_ytd

-- How is sales performing YTD (compared to last year)

FROM sales_cube_view s
JOIN channel_sales_channel_view c ON s.channel

= c.dim_key

JOIN time_calendar_view

t ON s.time

= t.dim_key

JOIN geography_regional_view

g ON s.geography = g.dim_key

JOIN product_standard_view

p ON s.product

= p.dim_key

WHERE c.level_name = 'CLASS'

AND t.long_description in ('CY2009', 'Q3-CY2009', 'Nov-2009')
AND g.level_name = 'ALL_REGIONS'

--> LEVEL_NAME can be used for "All" condition

AND p.level_name = 'DEPARTMENT'

ORDER BY c.class_long_description,

p.department_long_descript,
t.end_date;

CHANNEL PRODUCT

TIME SALES YTD YTD_PY_PCT_

Q3-CY2009 1354490 3722396 16

Direct Cameras and Camcorders

Nov-

Direct Cameras and Camcorders

CY2009 5165424 5165424 18

1. Parameterized "drill" using 'ALL_YEARS'.

variable time_parent varchar2(50)
exec :time_parent := 'CY2007';

SELECT c.long_description

as channel,

p.long_description

as product,

t.long_description

as time,

round(s.sales)

as sales,

round(s.SALES_YTD)

as ytd,

-- sales year to date (YTD)

round(s.SALES_YTD_PY_PCT_CHG, 2) as ytd_PY_pct_chg,

-- sales YTD

compared to last year

how_is_sales_ytd

-- How is sales performing YTD (compared to last year)

FROM sales_cube_view s
JOIN channel_sales_channel_view c ON s.channel

= c.dim_key

JOIN time_calendar_view

t ON s.time

= t.dim_key

JOIN geography_regional_view

g ON s.geography = g.dim_key

JOIN product_standard_view

p ON s.product

= p.dim_key

WHERE c.level_name = 'CLASS'

AND t.parent = nvl(:time_parent, 'ALL_YEARS')
AND g.level_name = 'ALL_REGIONS'

--> LEVEL_NAME can be used for "All" condition

AND p.level_name = 'DEPARTMENT'

ORDER BY c.class_long_description,

p.department_long_descript,
t.end_date;

CHANNEL PRODUCT

TIME SALES YTD YTD_PY_PCT_

Q3-CY2009 1354490 3722396 16

Direct Cameras and Camcorders

Nov-

Direct Cameras and Camcorders

CY2009 5165424 5165424 18

Direct Computers

Q3-CY2009 12865030 38539127 15