Querying Analytic Views

  • Module 4

    About Analytic View Objects

    There are three types of objects that are used with analytic views: attribute dimensions, hierarchies, and analytic views.

    An attribute dimension is a metadata object that references tables or views and organizes columns into higher-level objects such as attributes and levels. Most metadata related to dimensions and hierarchies is defined in the attribute dimension object.

    Hierarchies are a type of view. Hierarchies return detail and aggregate level keys ("hierarchy values") and attributes of those values. As the name implies, hierarchies organize data using hierarchical relationships between data. Hierarchies reference attribute dimension objects.

    Analytic views are a type of view that return fact data. Analytic views reference both fact tables and hierarchies. Both hierarchy and measure data is selected from analytic views.

    Hierarchies are typically used when an application needs to lookup hierarchy values and attributes. For example, to get a list of values for a pick list. Analytic views are used to query measure (fact) data. Because all columns of each hierarchy are included in the analytic view there is no need to join hierarchies and analytic views.

  • Module 5

    Selecting from Hierarchies

    Hierarchies return data from one of more levels that have been defined in an attribute dimension. Every hierarchy will have data from levels and a single row for an ALL level (a single top level aggregate).

    Hierarchies include two types of columns:

    • Attribute columns contain values for each attribute used by a level in the hierarchy. These values typically come directly from dimension ta ble(s). For example, the _ID and _NAME columns in the sample tables will be presented as attribute columns.
    • Hierarchical attribute columns contain values that have been created by the database using data from attribute columns. Hierarchical attributes help aggregate, navigate, filter and present hierarchies.

    Begin familiarizing yourself with hierarchies by selecting from them using SELECT *.

    At this point, just notice that there are many different types of columns and that rows are returned for values at detail and aggregate levels.

    View data in the TIME_HIER hierarchy.

     SELECT *
    FROM time_hier;

    View data in the PRODUCT_HIER hierarchy.

    SELECT *
    FROM product_hier;

    View data in the GEOGRAPHY_HIER hierarchy.

    SELECT *
    FROM geography_hier;

  • Module 6

    Attribute Columns

    Each column of the referenced dimension table may be presented in a hierarchy. The column name may be used as is or be aliased.

    The following queries present the attribute columns of the TIME_HIER hierarchy. Note that each attribute is presented as a separate column and there are rows for each level (years, quarters and months).

    SELECT month_id,
      month_name,
      month_end_date,
      quarter_id,
      quarter_name,
      quarter_end_date,
      year_id,
      year_name,
      year_end_date
    FROM time_hier;

    Select attribute columns from the PRODUCT_HIER hierarchy.

    SELECT category_id,
      category_name,
      department_id,
      department_name
    FROM product_hier;

    Select attribute columns from the GEOGRAPHY_HIER hierarchy.

    SELECT region_id,
      region_name,
      country_id,
      country_name,
      state_province_id,
      state_province_name
    FROM geography_hier;

  • Module 7

    Hierarchical Attribute Columns

    Hierarchical attributes are created by the database. In some cases attribute columns are unpivoted into a single hierarchical attribute column, creating new rows for aggregate levels. In other cases, the database calculates values for hierarchical attribute columns.

    MEMBER_NAME and LEVEL_NAME Columns

    The MEMBER_NAME column is typically used to return a text description of the key value. For example, “Brazil “ for COUNTRY_ID “BR” or “Camera and Accessories” for CATEGORY_ID “-534”. The LEVEL_NAME column includes the level name of the key attribute value.

    Select MEMBER_NAME and LEVEL_NAME columns from the TIME_HIER hierarchy.

    SELECT member_name,
      level_name
    FROM time_hier;
    

    Select MEMBER_NAME and LEVEL_NAME columns from the PRODUCT_HIER hierarchy.

    SELECT member_name,
      level_name
    FROM product_hier;
    

    Select MEMBER_NAME and LEVEL_NAME columns from the GEOGRAPHY_HIER hierarchy.

    SELECT member_name,
      level_name
    FROM geography_hier;
    

    Hierarchical columns can be used for a variety of purposes, including filtering and sorting. For example, the LEVEL_NAME column can be used to filter based on level of aggregation.

    Select MEMBER_NAME and LEVEL_NAME columns from the TIME_HIER hierarchy filtered to 'YEAR'.

    SELECT member_name,
      level_name
    FROM time_hier
    WHERE level_name = 'YEAR';
    

    Select MEMBER_NAME and LEVEL_NAME columns from the PRODUCT_HIER hierarchy filtered to 'DEPARTMENT'.

    SELECT member_name,
      level_name
    FROM product_hier
    WHERE level_name = 'DEPARTMENT';
    

    Select MEMBER_NAME and LEVEL_NAME columns from the GEOGRAPHY_HIER hierarchy filtered to 'REGION' and 'COUNTRY'.

    SELECT member_name,
      level_name
    FROM geography_hier
    WHERE level_name IN ('REGION','COUNTRY');
    

    HIER_ORDER Column

    The HIER_ORDER column sorts values within parents in the hierarchy. For example, Quarters within Years and Months within Quarters. You can choose how HIER_ORDER is calculated when you define levels in the attribute dimension object.

    Select from the TIME_HIER hierarchy ordering by HIER_ORDER.

    SELECT member_name,
      level_name,
      hier_order
    FROM time_hier
    ORDER BY hier_order;
    

    Select from the PRODUCT_HIER hierarchy ordering by HIER_ORDER.

    SELECT member_name, 
      level_name,
      hier_order
    FROM product_hier
    ORDER BY hier_order;
    

    Select from the GEOGRAPHY_HIER hierarchy ordering by HIER_ORDER.

    SELECT member_name,
      level_name,
      hier_order
    FROM geography_hier
    ORDER BY hier_order;
    

    IS_LEAF and DEPTH Columns

    The IS_LEAF column indicates if a value is at the detail (lowest) level of the hierarchy or at an aggregate level. In the following examples, IS_LEAF is used to filter for the detail rows of the hierarchies. (Only the attribute columns are selected, so these queries return the same rows and columns as the dimension tables that the hierarchies refer to.)

    Use IS_LEAF to select only the detail rows of the TIME_HIER hierarchy.

    SELECT month_id,
      month_name,
      month_end_date,
      quarter_id,
      quarter_name,
      quarter_end_date,
      year_id,
      year_name,
      year_end_date
    FROM time_hier
    WHERE is_leaf = 1
    ORDER BY hier_order;
    

    Use IS_LEAF to select only the detail rows of the PRODUCT_HIER hierarchy.

    SELECT category_id,
      category_name,
      department_id,
      department_name
    FROM product_hier
    WHERE is_leaf = 1
    ORDER BY hier_order;
    

    Use IS_LEAF to select only the detail rows of the GEOGRAPHY_HIER hierarchy.

    SELECT state_province_id,
      state_province_name,
      country_id,
      country_name,
      region_id,
      region_name
    FROM geography_hier
    WHERE is_leaf = 1
    ORDER BY hier_order;
    

    IS_LEAF might also be used to indicate whether value is drillable to a lower level of detail. The DEPTH column indicates how distant a value is from the top most value in the hierarchy.

    Use IS_LEAF and DEPTH to query the TIME_HIER hierarchy in outline form.

    SELECT
     CASE is_leaf
       WHEN 0 then lpad(' ',depth * 2,'.') || '+ ' || member_name 
       ELSE lpad(' ',depth * 3,'.') || member_name END AS DRILL,
       depth
    FROM time_hier
    ORDER BY hier_order;
    

    Use IS_LEAF and DEPTH to query the PRODUCT_HIER hierarchy in outline form.

    SELECT
     CASE is_leaf
       WHEN 0 then lpad(' ',depth * 2,'.') || '+ ' || member_name 
       ELSE lpad(' ',depth * 3,'.') || member_name END AS DRILL,
       depth
    FROM product_hier
    ORDER BY hier_order;
    

    Use IS_LEAF and DEPTH to query the GEOGRAPHY_HIER hierarchy in outline form.

    SELECT
     CASE is_leaf
       WHEN 0 then lpad(' ',depth * 2,'.') || '+ ' || member_name 
       ELSE lpad(' ',depth * 3,'.') || member_name END AS DRILL,
       depth
    FROM geography_hier
    ORDER BY hier_order;
    

    MEMBER_UNIQUE_NAME and PARENT_UNIQUE_NAME Columns

    The MEMBER_UNIQUE_NAME is the primary key of the hierarchy. As the primary key, it creates values that are unique across all level of the hierarchy. Depending on your data, the values in MEMBER_UNIQUE_NAME might be a simple concatenation of level name and key attribute value or a m ore complex value that also includes ancestors.

    For now, simply note that MEMBER_UNIQUE_NAME is a unique value. The PARENT_UNIQUE_NAME column contains the unique name of the parent value in the hierarchy.

    The following examples are the simplest form of MEMBER_UNIQUE_NAME.

    Select MEMBER_UNIQUE_NAME from the TIME_HIER hierarchy.

    SELECT year_id,
      quarter_id,
      month_id,
      level_name,
      member_unique_name,
      parent_unique_name
    FROM time_hier
    ORDER BY hier_order;
    

    Select MEMBER_UNIQUE_NAME from the PRODUCT_HIER hierarchy.

    SELECT department_id,
      category_id,
      level_name,
      member_unique_name,
      parent_unique_name
    FROM product_hier
    ORDER BY hier_order;
    

    Select MEMBER_UNIQUE_NAME from the GEOGRAPHY_HIER hierarchy.

    SELECT state_province_id,
      country_id,
      region_id,
      level_name,
      member_unique_name,
      parent_unique_name
    FROM geography_hier
    ORDER BY hier_order;
    

    The MEMBER_UNIQUE_NAME and PARENT_UNIQUE_NAME are particularly useful for drill down type queries.

    Start with Year level data.

    SELECT member_name,
      member_unique_name
    FROM time_hier
    WHERE level_name = 'YEAR'
    ORDER BY hier_order;
    

    Drill to children of CY2014 using PARENT_UNIQUE_NAME.

    SELECT member_name,
      member_unique_name
    FROM time_hier
    WHERE parent_unique_name = '[YEAR].&[14]'
    ORDER BY hier_order;
    

    Drill to children of Q3CY2014.

    SELECT member_name,
      member_unique_name
    FROM time_hier
    WHERE parent_unique_name = '[QUARTER].&[314]'
    ORDER BY hier_order;
    

    MEMBER_CAPTION and MEMBER_DESCRIPTION Columns

    The MEMBER_CAPTION and MEMBER_DESCRPTION columns are also typically used for text descriptors.

    Select MEMBER_CAPTION and MEMBER_DESCRIPTION from the TIME_HIER hierarchy.

    SELECT member_name,
      member_caption,
      member_description
    FROM time_hier
    ORDER BY hier_order;
    

  • Module 8

    Selecting from Analytic Views

    Analytic views join hierarchies to a fact table and include all columns from each hierarchy, measures from the fact table and calculated measures. Like hierarchies, analytic views return detail and aggregate level data.

    A SELECT statement querying an analytic view contains a SELECT list, a FROM clause and in most cases should include a WHERE clause. The SELECT list includes attributes and measures. The FROM clause includes only the analytic view. The HIERARCHIES parameter lists the hierarchies to be used in the query.

    Columns from hierarchies are always qualified by hierarchy name and may also be qualified by owner and attribute dimension name. In most cases each hierarchy referenced in the SELECT list is included in the HIERARCHIES parameter.

    Select the MEMBER_NAME column from the TIME_HIER hierarchy and SALES for data at the YEAR level.

    SELECT time_hier.member_name AS TIME,
      sales
    FROM sales_av HIERARCHIES (time_hier)
    WHERE time_hier.level_name = 'YEAR'
    ORDER BY time_hier.hier_order;

    Note that this query:

    • Does not include an aggregation function (e.g., SUM) or GROUP BY clause. This is because the aggregation rules are included in the definition analytic view.
    • Does not include joins. Joins are also included in the definition of the analytic view.
    • Does not select the YEAR_ID or YEAR_NAME columns. Instead, it selects the MEMBER_NAME column. Either will return the same data, but the MEMBER_NAME column returns data at level of aggregation.< /li>

    Select SALES at the YEAR, QUARTER and MONTH levels.

    SELECT time_hier.member_name AS TIME,
      sales
    FROM sales_av HIERARCHIES (time_hier)
    WHERE time_hier.level_name IN ('YEAR','QUARTER','MONTH')
    ORDER BY time_hier.hier_order;

    HIERARCHIES Parameter

    The HIERACHIES parameter allows a query to return detail and aggregate level values of the listed hierarchies and to filter using attributes of a hierarchy. If a column is selected from a hierarchy which is not listed in the hierarchies parameter only data from the database defined top-most value is returned (that is, where DEPTH = 0).

    The following query selects time values at the year level and includes the TIME_HIER hierarchy in the HIERARCHIES parameter.

    SELECT time_hier.member_name AS TIME,
      sales
    FROM sales_av HIERARCHIES (time_hier)
    WHERE time_hier.level_name = 'YEAR'
    ORDER BY time_hier.hier_order;

    PRODUCT_HIER.MEMBER_NAME as been added to the SELECT list, but PRODUCT_HIER has not been added to the HIERARCHIES parameter. Only data for the ALL PRODUCTS member (that top most aggregate value) are returned by this query.

    SELECT time_hier.member_name AS TIME,
      product_hier.member_name AS PRODUCT,
      sales
    FROM sales_av HIERARCHIES (time_hier)
    WHERE time_hier.level_name = 'YEAR'
    ORDER BY time_hier.hier_order;

    In the next query a filter using PRODUCT_HIER.LEVEL_NAME has been added, but PRODUCT_HIER is still not in the HIERACHIES parameter. This query will return no data.

    SELECT time_hier.member_name AS TIME,
      product_hier.member_name AS PRODUCT,
      sales
    FROM sales_av HIERARCHIES (time_hier)
    WHERE time_hier.level_name = 'YEAR'
      AND product_hier.level_name = 'DEPARTMENT'
    ORDER BY time_hier.hier_order;

    PRODUCT_HIER has been added to the HIERARACHIES parameter. This query returns data at the DEPARTMENT level of the PRODUCT_HIER hierarchy.

    SELECT time_hier.member_name AS TIME,
      product_hier.member_name AS PRODUCT,
      sales
    FROM sales_av HIERARCHIES (time_hier, product_hier)
    WHERE time_hier.level_name = 'YEAR'
      AND product_hier.level_name = 'DEPARTMENT'
    ORDER BY time_hier.hier_order;

    The Importance of Using Filters

    Even analytic views defined over small tables have the potential to return large numbers of rows because they return both detail level and aggregate level data. Therefore, it is usually very important to include filters (that is, a WHERE clause) in queries selecting from analytic views.

    Select SALES for all rows of the TIME_HIERARCHY hierarchy.

    SELECT time_hier.member_name AS TIME,
      time_hier.depth            AS TIME_DEPTH,
      sales
    FROM sales_av HIERARCHIES (time_hier)
    ORDER BY time_hier.hier_order;

    With only a single, small hierarchy such as the TIME_HIER hierarchy (86 rows) the lack of a WHERE clause is not a problem. As more hierarchies are used (that is, listed in the HIERARCHIES parameter), the analytic view has the capacity to return many more rows. Even though the sample dimension tables and fact table used in this tutorial are very small, a query using all three hierarchies has the potential to return up to 260,064 rows (86 time periods * 12 products * 252 geographies).

    Select Sales data from the SALES_AV analytic view using filters on each hierarchy.

    SELECT time_hier.member_name AS TIME,
      product_hier.member_name   AS product,
      geography_hier.member_name AS geography,
      sales
    FROM sales_av HIERARCHIES (
      time_hier,
      product_hier,
      geography_hier
      )
    WHERE time_hier.member_name  = 'CY2014'
    AND product_hier.level_name = 'DEPARTMENT' 
    AND geography_hier.parent_unique_name = '[REGION].&[EUROPE]'
    ORDER BY time_hier.hier_order,
       geography_hier.hier_order,
       product_hier.hier_order;

  • Module 9

    Selecting Calculated Measures

    Calculated measures are defined in the analytic view and presented as columns. To select a calculated measure, just include it in the SELECT list.

    Select the SALES, SALES_SHARE_GEOG_PARENT and SALES_PRIOR_PERIOD measures from the SALES_AV analytic view.

    Note the following:

    • The query filters to the time period ‘CY2014’ and reports both the current year sales and the prior period (CY2103) in this case. The database has automatically expands filters to access CY2103 data (so you don’t have to).
    • The SALES_SHARE_GEOG_PARENT measure calculates the ratio of Sales for the current time, product and geography to the Sales of the parent geography (North America, in this case). This is another example of the database automatically expanding filters to access data needed by calculations.
    SELECT time_hier.member_name AS TIME,
      product_hier.member_name   AS product,
      geography_hier.member_name AS geography,
      sales,
      ROUND(sales_share_geog_parent,2) as sales_share_geog_parent,
      sales_prior_period
    FROM sales_av HIERARCHIES (
      time_hier,
      product_hier,
      geography_hier
      )
    WHERE time_hier.member_name  = 'CY2014'
    AND product_hier.level_name = 'DEPARTMENT' 
    AND geography_hier.parent_unique_name = '[REGION].&[NORTH_AMERICA]'
    ORDER BY time_hier.hier_order,
      geography_hier.hier_order,
      product_hier.hier_order;

    The query is easily changed to drill down on Mexico. Note that the only part of the query that needs to change is the value in the PARENT_UNIQUE_NAME filter. Otherwise, the query was unchanged.

    Drill down to Mexico using the PARENT_UNIQUE_NAME value.

    SELECT time_hier.member_name AS TIME,
      product_hier.member_name   AS product,
      geography_hier.member_name AS geography,
      sales,
      round(sales_share_geog_parent,2) AS sales_share_geog_parent,
      sales_prior_period
    FROM sales_av HIERARCHIES (
      time_hier,
      product_hier,
      geography_hier
      )
    WHERE time_hier.member_name  = 'CY2014'
    AND product_hier.level_name = 'DEPARTMENT' 
    AND geography_hier.parent_unique_name = '[COUNTRY].&[MX]'
    ORDER BY time_hier.hier_order,
      geography_hier.hier_order,
      product_hier.hier_order;

  • Module 10

    Using SQL Functions

    SQL functions may be used with analytic views, just like any other table or view. For example, SQL functions might be used to format output or perform calculations over the top of an analytic view. Because the analytic view provides easy access to aggregate data, using SQL to perform calculations over the analytic view is typically easier than the equivalent query against tables.

    Use SQL functions and the DEPTH column to create a formatted report.

    SELECT lpad(' ',time_hier.depth * 2) || time_hier.member_name AS TIME,
      lpad(' ',product_hier.depth * 2) || product_hier.member_name   AS product,
      TO_CHAR(sales,'$999,999,999,999') AS sales,
      lpad(round(sales_share_prod_parent,2) * 100 || '%',24,' ') as sales_share_prod_parent,
      TO_CHAR(sales_prior_period,'$999,999,999,999') AS sales_prior_period
    FROM sales_av HIERARCHIES (
      time_hier,
      product_hier
      )
    WHERE time_hier.level_name IN ('YEAR')
    AND product_hier.level_name IN ('DEPARTMENT','CATEGORY')
    ORDER BY time_hier.hier_order,
      product_hier.hier_order;

    The next example queries the analytic view in an inner query and uses the RANK window function in the outer query. The analytic view conveniently provides input to the RANK (TIME and PRODUCT_LEVEL to PARTITION BY and ORDER BY at an aggregate level of SALES).

    Use the RANK function over the SALES_AV analytic view to rank values at the DEPARTMENT level within YEAR.

    WITH sales as (
      SELECT time_hier.member_name      AS time,
        time_hier.hier_order            AS time_order,
        product_hier.member_name        AS product,
        product_hier.hier_order         AS product_order,
        product_hier.level_name         AS product_level,
        product_hier.parent_unique_name AS product_parent,
        sales AS sales
      FROM sales_av HIERARCHIES (
        time_hier,
        product_hier
        )
      WHERE time_hier.level_name IN ('YEAR')
      AND product_hier.level_name IN ('DEPARTMENT')
      )
    SELECT time,
      product,
      sales,
      rank() OVER (PARTITION BY time, product_parent ORDER BY sales DESC) AS prod_rank_within_parent
    FROM sales
    ORDER BY
      time_order,
      sales DESC;

    As another example of the ease of using the hierarchical attribute columns, the previous query is updated to rank Categories within Departments. Only the value in the product LEVEL_NAME filter needs to change, the remainder of the query remains unchanged.

    Change the PRODUCT_HIER.LEVEL_NAME filter to CATEGORY to rank within DEPARTMENT and YEAR.

    WITH sales as (
      SELECT time_hier.member_name AS TIME,
        time_hier.hier_order       AS time_order,
        product_hier.member_name   AS product,
        product_hier.hier_order    AS product_order,
        product_hier.level_name   AS product_level,
        product_hier.parent_unique_name AS product_parent,
        sales AS sales
      FROM sales_av HIERARCHIES (
        time_hier,
        product_hier
        )
      WHERE time_hier.level_name IN ('YEAR')
      AND product_hier.level_name IN ('CATEGORY')
      )
    SELECT time,
      product,
      sales,
      rank() OVER (PARTITION BY time, product_parent ORDER BY sales DESC) AS prod_rank_within_parent
    FROM sales
    ORDER BY
      time_order,
      sales DESC; 

  • Additional Information