SET pages    999
SET linesize 180
SET trimout   ON
SET trimspool ON
SET timing    ON
SET echo      ON
-- ****************************************************************************
-- Querying views defined over an OLAP cube
-- ****************************************************************************
-- Queries that mirror data selections shown in Building OLAP11g Cubes
--   These queries illustrate the following:
--     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'
-- ****************************************************************************
--
-- 1: Product and Sales are the only columns in query. "DEPARTMENT" is the
--      level name for Product. All other dimensions require an 'ALL' filter
--      in order to leverage aggregations in the Cube.

COLUMN dept  FORMAT a30
COLUMN sales FORMAT 999,999,990.000
SELECT p.department_long_descript AS dept
,      s.sales                    AS sales
FROM product_standard_view p
,    sales_cube_view       s
WHERE(p.dim_key    = s.product
AND   p.level_name = 'DEPARTMENT'
AND   s.channel    = 'ALL_CHANNELS'
AND   s.geography  = 'ALL_REGIONS'
AND   s.time       = 'ALL_YEARS');

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

COLUMN class FORMAT a10
COLUMN qtr   FORMAT a10
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 channel_sales_channel_view c
,    product_standard_view      p
,    geography_regional_view    g
,    time_calendar_view         t
,    sales_cube_view            s
WHERE(c.dim_key                  = s.channel
AND   g.dim_key                  = s.geography
AND   p.dim_key                  = s.product
AND   t.dim_key                  = s.time
AND   g.level_name               = 'ALL_REGIONS'
AND   c.level_name               = 'CLASS'
AND   p.level_name               = 'DEPARTMENT'
AND   t.level_name               = 'CALENDAR_QUARTER'
AND   t.calendar_year_long_descr = 'CY2007')
ORDER BY c.class_long_description
,        p.department_long_descript
,        t.end_date;

-- ****************************************************************************
-- 3:  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

COLUMN ytd              FORMAT 999,999,990.000
COLUMN ytd_py_pct_chg   FORMAT 90.00
COLUMN how_is_sales_ytd FORMAT a20
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
,      round(s.sales_ytd_py_pct_chg, 2) AS ytd_py_pct_chg
,      how_is_sales_ytd
FROM channel_sales_channel_view c
,    product_standard_view      p
,    geography_regional_view    g
,    time_calendar_view         t
,    sales_cube_view            s
WHERE(c.dim_key                  = s.channel
AND   g.dim_key                  = s.geography
AND   p.dim_key                  = s.product
AND   t.dim_key                  = s.time
AND   g.level_name               = 'ALL_REGIONS'
AND   c.level_name               = 'CLASS'
AND   p.level_name               = 'DEPARTMENT'
AND   t.level_name               = 'CALENDAR_QUARTER'
AND   t.calendar_year_long_descr = 'CY2007')
ORDER BY c.class_long_description
,        p.department_long_descript
,        t.end_date;

-- ****************************************************************************
-- 4: The cube/dimensions are represented as embedded totals. This makes it easy to
--    show multiple levels of aggregation at the same time using a simple query.
--
--    Here, we are showing different levels for time (month, quarter and year).
--      In this case, you 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.

COLUMN channel FORMAT a10
COLUMN product FORMAT a30
COLUMN time    FORMAT a10
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
,      round(s.sales_ytd_py_pct_chg, 2) AS ytd_py_pct_chg
,      how_is_sales_ytd
FROM channel_sales_channel_view c
,    product_standard_view      p
,    geography_regional_view    g
,    time_calendar_view         t
,    sales_cube_view            s
WHERE(c.dim_key          = s.channel
AND   g.dim_key          = s.geography
AND   p.dim_key          = s.product
AND   t.dim_key          = s.TIME
AND   g.level_name       = 'ALL_REGIONS'
AND   c.level_name       = 'CLASS'
AND   p.level_name       = 'DEPARTMENT'
AND   t.long_description in ('CY2007', 'Q3-CY2007', 'Nov-2007'))
ORDER BY channel
,        product
,        t.end_date;

-- ****************************************************************************
-- 5: The dimension views also include special hierarchy columns that make it easy to
--      navigate the dimensions (e.g. drill down from 'All Years' to the 'Years' level.)
--      Use the "parent" column to drill down any type of of hierarchy:
--        level-based, ragged, skip-level or value-based.
--
--      The following query selects the "children" of "ALL_PRODUCTS" and "ALL_REGIONS"
--      by applying a condition on the parent columns.
--      Note that the Channel dimension is now removed from the results.

COLUMN geography FORMAT a30
SELECT g.long_description              AS geography
,      p.long_description              AS product
,      t.long_description              AS time
,      round(s.sales)                  AS sales
,      round(s.sales_ytd)              AS ytd
,      round(s.sales_ytd_py_pct_chg,2) AS ytd_py_pct_chg
,      how_is_sales_ytd
FROM channel_sales_channel_view c
,    product_standard_view      p
,    geography_regional_view    g
,    time_calendar_view         t
,    sales_cube_view            s
WHERE(c.dim_key          = s.channel
AND   g.dim_key          = s.geography
AND   p.dim_key          = s.product
AND   t.dim_key          = s.TIME
AND   g.parent           = 'ALL_REGIONS'
AND   c.level_name       = 'ALL_CHANNELS'
AND   p.parent           = 'ALL_PRODUCTS'
AND   t.long_description in ('CY2007', 'Q3-CY2007', 'Nov-2007'))
ORDER BY geography
,        product
,        t.end_date;

-- ****************************************************************************
-- 6: Change the Time selection to a Parameterized "drill" using 'ALL_YEARS'
--      Channel is added back into the results, and Geography is removed.
--      Drill on Channel to return the children of  'ALL_CHANNELS'.

VARIABLE time_parent VARCHAR2(10);
BEGIN
 :time_parent:=NULL;
END;
/
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
,      round(s.sales_ytd_py_pct_chg, 2) AS ytd_PY_pct_chg
,      how_is_sales_ytd
FROM channel_sales_channel_view c
,    product_standard_view      p
,    geography_regional_view    g
,    time_calendar_view         t
,    sales_cube_view            s
WHERE(c.dim_key    = s.channel
AND   g.dim_key    = s.geography
AND   p.dim_key    = s.product
AND   t.dim_key    = s.time
AND   c.parent     = 'ALL_CHANNELS'
AND   g.level_name = 'ALL_REGIONS'
AND   p.parent     = 'ALL_PRODUCTS'
AND   t.parent     = nvl(:time_parent, 'ALL_YEARS'))
ORDER BY channel
,        product
,        t.end_date;

BEGIN
 :time_parent:='CY2007';
END;
/
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
,      round(s.sales_ytd_py_pct_chg, 2) AS ytd_py_pct_chg
,      how_is_sales_ytd
FROM channel_sales_channel_view c
,    product_standard_view      p
,    geography_regional_view    g
,    time_calendar_view         t
,    sales_cube_view            s
WHERE(c.dim_key    = s.channel
AND   g.dim_key    = s.geography
AND   p.dim_key    = s.product
AND   t.dim_key    = s.time
AND   c.parent     = 'ALL_CHANNELS'
AND   g.level_name = 'ALL_REGIONS'
AND   p.parent     = 'ALL_PRODUCTS'
AND   t.parent     = nvl(:time_parent, 'ALL_YEARS'))
ORDER BY channel
,        product
,        t.end_date;
