OlapTrain P2 CubeQueries 201408051208

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

Wyszukiwarka

Podobne podstrony:
ClothesEveryday P2 Prepare OracleBIEE ROLAP Metadata 20140803 1723
OlapTrain P1 PrepareOracleAWMDatabase Student 20140820
201408011709
FURTHER NOV 02 P2
fce hb samp p2
PhysHL P2 M00
Oczyszczanie ludzkiego białka P2 na drodze chromatografii powinowactwa
p2 GOTOWY
p2
Mathematics HL Nov 2006 TZ1 P2$
Mathematics HL May 2004 TZ1 P2 $
IMG 20140830 0018 id 211648 Nieznany
Mathematics HL Nov 2002 P2
Mathematics HL May 2004 TZ2 P2
MATHEMATICS HL May 1999 P2$

więcej podobnych podstron