Oracle Database 12c: Using Analytic Views


Options



Before You Begin

Purpose

In this tutorial, you learn how use Analytic Views with Oracle Database 12.2.

The Analytic Views feature is available with any Oracle Database 12.2 database installation, whether it be an Oracle Database Cloud Service, or an on-premise Oracle Database environment.

You will learn how to:

  • Create analytic views
  • Query analytic views using SQL
  • Enhance analytic views with calculations

Most importantly, you will find that analytic views are easy to create and query.

Time to Complete

Approximately 45 mins

Background

Analytic Views organize data using a dimensional model, allowing you to easily add aggregations and calculations to data sets and present data in views that can be queried with relatively simple SQL. Like standard relational views, analytic views:

  • Are metadata objects (that is, they do not store data).
  • Can be queried using SQL.
  • Can access data from other database objects such as tables, views and external tables.
  • Can join multiple tables into a single view.

In addition, analytic views:

  • Organize data into a rich business model using dimensional and hierarchical concepts.
  • Include system-generated columns with hierarchical data.
  • Automatically aggregate data.
  • Include embedded measure calculations that are easily defined using syntax based on the business model.
  • Include presentation metadata.

The definition of the analytic view includes navigation, join, aggregation and calculation rules, eliminating the need to include these rules in queries. Rather than having simple tables and complex SELECT statements that express joins, aggregations, and measure calculations you can use simple SQL to query smart analytic views.

This approach has several benefits, including:

  • Simplified and faster application development. It is much easier to define calculations within analytic views than it is to write or generate complex SELECT statements.
  • Calculation rules can be defined once in the Database and re-used within any number of applications, providing end-users with greater freedom in the choice of reporting tools without concern for inconsistent results.
  • The ability to increase the value of applications by enhancing data sets with aggregate data and measure calculations.

This tutorial illustrates these concepts in the following hands-on exercises.

What Do You Need?

Have access to Oracle Database 12.2, and the sample Sales History (SH) data set.

As mentioned previously, analytic views may be used with either an on-premise Oracle Database installation, or an Oracle Database Cloud Service. In this tutorial, the Oracle Exadata Express Cloud Service is used as the database platform.

Prepare the Hands-On Environment

In order to perform the exercises in this tutorial using the Exadata Express Cloud Service, complete the following tasks:

  • Create a new schema for the sample data.
  • Download and install SQL Developer as the client development tool.
  • Install sample data on the Exadata Express Cloud Serivce using SQL Developer.
  • Provide required privileges to the database user which enable development of Analytic View database objects.

Perform the following steps:

  1. Log into your Exadata Express Cloud Service environment, and open the Service Console window.

  2. In the Administration section, click the Create Database Schema option, as shown here:

    Description of this image
  3. In the Create Databse Schema window, enter sh as the Schema name, and then create a password. NOTE: The password must contain at least 8 characters, including at least one capital letter, one number, and one special character. Finally click the Create Database Schema button.

    Description of this image
  4. Back in the Service Console window, in the Client Access section click the Download Tools option, as shown below:

    Description of this image
  5. In the Download Tools window, select SQL Developer.

    Result: The Using SQL Developer with Oracle Database Exadata Express Cloud Service page on Oracle Technology Network (OTN) appears.

  6. Follow the instructions in Step 1: Download and Run SQL Developer, as shown below.

    Description of this image

    Note: Once you have downloaded and started SQL Developer, close the OTN page.

  7. Then, toggle back to the Service Console window. In the Client Access section click the Download Client Credentials option, as shown below:

    Description of this image
  8. In the Download Client Credentials window, create a password.

    NOTES: This password will serve as the "Keystore Password" in your SQL Developer connection to the SH schema in your Exadata Express Oracle Database instance. In the same way as your database schema password, this password must contain at least 8 characters, including at least one capital letter, one number, and one special character.

    Description of this image
  9. Click Download.

  10. In the next window, specify a location to save the resulting client_credentials.zip file.

    Now, use SQL Developer to create a connection to the SH schema in your Exadata Express database instance.

  11. In the SQL Developer Connections tab, click the Add icon (+) and select New Connection from the menu, as shown here:

    Description of this image
  12. In the New / Select Database Connection dialog, specify the following:

    • Connection Name: Enter a descriptive name for the Connection (Ex Ex SH is used in this example).
    • Username: sh
    • Password: Use the password that you created for the sh schema in the Exadata Express Administrative > Create Database Schema interface.
    • Connection Type: Cloud PDB
    • Role: default
    • Configuration File: Use the Browse button to locate and select the client_credentials.zip file that you downloaded from the Exadata Express Client Access > Download Client Credentials interface.
    • Keystore Password: Use the password that you created in the Exadata Express Client Access > Download Client Credentials interface.

    The completed Database Connection options should look similar to this:

    Description of this image
    • Click Test to verify the connection ("Status: Success" should appear in the lower left corner of the dialog box).
    • Click Save and then click Connect to connect to the database.

    Next, use SQL scripts to install the sample data to your SH schema on the cloud instance.

  13. Use the Save As option in your right-mouse menu to download the following .sql files to a secure directory on the machine that contains SQL Developer: channels.sql, countries.sql, customers.sql, products.sql, promotions.sql, times.sql, and sales.sql.

  14. In SQL Developer, select View > Files from the main menu. Then, in the Files tab, navigate to the directory contains the .sql files. For example:

    Description of this image
  15. Double-click the channels.sql file.

    Result: The .sql file appears in a SQL Developer worksheet window.

  16. Click the Run Script tool, select the SH schema connection, and click OK as shown below:

    Description of this image
  17. When the script completes, close the worksheet window, open the countries.sql script, and run the script in the same way.

  18. When the countries.sql script completes, close the worksheet window and run the following scripts in the same way: customers.sql, products.sql, promotions.sql, and times.sql. NOTE: Do not run the sales.sql script yet.

  19. Result: After these six scripts complete succesfully, the tables are created, and the data is inserted for each table. Drill on the Tables node for the SH connection to view the new tables:

    Description of this image
  20. Now, open sales.sql from the Files tab, and run the script, as shown below:

    Description of this image
  21. When the script completes, close the sales.sql worksheet window, and then click the Refresh tool in the Connections tab to view all of the tables.

    Description of this image

    Result: The sample data set for the tutorial is ready.

  22. Close the Files tab.

  23. Finally, using the Exadata Express administration account (PDB_ADMIN), grant the SH user appropriate privilages for Analytic View development.

  24. Right-click the Connections node and select New Connection from the menu. Then, in the New / Select Database Connection dialog, specify the following:

    • Connection Name: Enter a descriptive name for the Connection (Admin is used in this example).
    • Username: PDB_ADMIN
    • Password: Enter the password for the PDB_ADMIN account.
    • Connection Type: Cloud PDB
    • Role: default
    • Configuration File: Use the Browse button to locate and select the client_credentials.zip file that you downloaded from the Exadata Express Client Access > Download Client Credentials interface.
    • Keystore Password: Use the password that you created in the Exadata Express Client Access > Download Client Credentials interface.

    The completed Database Connection options should look similar to this:

    Description of this image
    • Click Connect to connect to the database as PBD_ADMIN.
  25. Under the Admin connection node, drill on the Other Users node. Then right-click the SH user and select Edit User from the menu, as shown below:

    Description of this image
  26. In the System Privileges tab of the Edit User window, select the following values under the Granted column:

    • CREATE ANALTYIC VIEW
    • CREATE ATTRIBUTE DIMENSION
    • CREATE HIERARCHY
    Description of this image
  27. Select the SQL tab to view the additional GRANTs that are required for Analytic View object development. Then, click Apply to execute the SQL statements.

    Description of this image

    Finally, click OK in the Successful information dialog.

  28. Close the Admin Worksheet window, and then right-click on the Admin connection node and select Disconnect from the menu.

Define Analytic View Objects

There are three types of database objects that enable analytic view functionality: 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. Data related to dimensions and hierarchies is selected from hierarchies. 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 presents fact data. Analytic views reference both fact tables and hierarchies. Both hierarchy and measure data is selected from analytic views.

In the following exercises, you will execute SQL code to:

  • Create the attribute dimension and hierarchy objects for each of the dimension tables in the sample sales history data model.
  • Create the analytic view for the sales data. The analytic view joins the hierarchies to the fact table to present fact data as measures.

Time Attribute Dimension and Hierarchies

The TIMES table contains data that can support both a calendar year and fiscal year hierarchies.

In this exercise, you examine the contents of the TIMES table. Then, you create the attribute dimension for Time. Finally, you create the two hierarchies for Time.

    Examine the TIMES Table
  1. In SQL Developer, ensure you are connected to the SH user, with an open Worksheet window, like this:

    Description of this image
  2. First, view some data in the TIMES table by executing the following SELECT statement:

    SELECT * FROM sh.times;

    NOTE: To execute SQL statements, simmply select the entire statement and past it into the SQL Developer Worksheet window. Then click the Run Statement tool as shown below.

    The resulting output looks like this:

    Description of this image

    Note: Some of the columns in this table can be used to build the calendar year hierarchy.

    In particular, notice that TIME_ID contains unique values within CALENDAR_MONTH_DESC. In addition, if you scroll through all of the columns, you would notice that:

    • CALENDAR_MONTH_DESC contains unique values within CALENDAR_QUARTER_DESC.
    • CALENDAR_QUARTER_DESC contains unique values within CALENDAR_YEAR.
  3. Next, execute the following SELECT statement to sort certain values:

    SELECT
     time_id,
     calendar_month_desc,
     end_of_cal_month,
     calendar_quarter_desc,
     end_of_cal_quarter,
     calendar_year
    FROM
     sh.times
    ORDER BY
     calendar_year,
     end_of_cal_quarter,
     end_of_cal_month,
     time_id;

    The output looks like this:

    Description of this image

    Notes: The ORDER BY clause used in the SELECT statement above sorts values in chronological order. Therefore, columns selected in the query above can be used to build a calendar year hierarchy.

  4. In a similar fashion, columns selected in the following query can be used to build the fiscal year hierarchy.

    SELECT
     time_id,
     fiscal_month_desc,
     end_of_fis_month,
     fiscal_quarter_desc,
     end_of_fis_quarter,
     fiscal_year
    FROM
     sh.times
    ORDER BY
     fiscal_year,
     end_of_fis_quarter,
     end_of_fis_month,
     time_id;

    The output looks like this:

    Description of this image
  5. Create the Attribute Dimension for Time

    An attribute dimension is a metadata object that contains attributes and levels created from columns in a dimension table.

    Hierarchy objects (that you will create soon) reference attribute dimensions and inherit attributes and levels from them. The attribute dimension allows you to create attributes and levels once and reuse them in any number of hierarchies.

    • An attribute references a column in a table (or view) and includes metadata which may be useful to applications. An attribute may assume the name of a column or be aliased to create a different attribute name.
    • A level has the following properties:
      • A Name. (E.g., LEVEL day or LEVEL calendar_month.)
      • A KEY which continues unique values in the level.
      • MEMBER NAME, MEMBER CAPTION and MEMBER DESCIPTION, all of which are typically used for text descriptors of the KEY value.
      • ORDER BY, which is used for sorting within the level.
      • DETERMINES, which may include one or more attributes that are determined by the KEY attribute of the level. For example, for each value in TIME_ID, there are only one value in the CALENDAR_MONTH_DESC and FISCAL_MONTH_DESC attributes. In most cases, the parent attribute of the level is listed in DETERMINES.

    Note: An attribute dimension must have at least one attribute and one level.

  6. Execute the follow SQL statement to create an attribute dimension for Time that includes attributes and levels for both Time hierarchies:

    CREATE OR REPLACE ATTRIBUTE DIMENSION sh_times_attr_dim
    USING sh.times
    ATTRIBUTES (
     time_id,
     calendar_month_desc,
     end_of_cal_month,
     calendar_quarter_desc,
     end_of_cal_quarter,
     calendar_year,
     end_of_cal_year,
     fiscal_month_desc,
     end_of_fis_month,
     fiscal_quarter_desc,
     end_of_fis_quarter,
     fiscal_year,
     end_of_fis_year
     )
    LEVEL day
     KEY time_id
     MEMBER NAME to_char(time_id)
     MEMBER CAPTION to_char(time_id)
     MEMBER DESCRIPTION to_char(time_id)
     ORDER BY time_id
     DETERMINES(calendar_month_desc,fiscal_month_desc)
    LEVEL calendar_month
     KEY calendar_month_desc
     MEMBER NAME calendar_month_desc
     MEMBER CAPTION calendar_month_desc
     MEMBER DESCRIPTION calendar_month_desc
     ORDER BY end_of_cal_month
     DETERMINES(calendar_quarter_desc)
    LEVEL calendar_quarter
     KEY calendar_quarter_desc
     MEMBER NAME calendar_quarter_desc
     MEMBER CAPTION calendar_quarter_desc
     MEMBER DESCRIPTION calendar_quarter_desc
     ORDER BY end_of_cal_quarter
     DETERMINES(calendar_year)
    LEVEL calendar_year
     KEY calendar_year
     MEMBER NAME TO_CHAR(calendar_year)
     MEMBER CAPTION TO_CHAR(calendar_year)
     MEMBER DESCRIPTION TO_CHAR(calendar_year)
    LEVEL fiscal_month
     KEY fiscal_month_desc
     MEMBER NAME fiscal_month_desc
     MEMBER CAPTION fiscal_month_desc
     MEMBER DESCRIPTION fiscal_month_desc
     ORDER BY end_of_fis_month
     DETERMINES(fiscal_quarter_desc)
    LEVEL fiscal_quarter
     KEY fiscal_quarter_desc
     MEMBER NAME fiscal_quarter_desc
     MEMBER CAPTION fiscal_quarter_desc
     MEMBER DESCRIPTION fiscal_quarter_desc
     ORDER BY end_of_fis_quarter
     DETERMINES(fiscal_year)
    LEVEL fiscal_year
     KEY fiscal_year
     MEMBER NAME TO_CHAR(fiscal_year)
     MEMBER CAPTION TO_CHAR(fiscal_year)
     MEMBER DESCRIPTION TO_CHAR(fiscal_year)
     ORDER BY end_of_fis_year
    ALL MEMBER NAME 'ALL YEARS';

    Result: The atttribute dimension is created.

    Description of this image

    Note: This attribute dimension is fully functional, but it does not contain metadata to describe the elements of the attributes or levels. To create this metadata, you add CLASSIFICATION values to the CREATE ATTRIBUTE DIMENSION statement.

    There are two uses of CLASSIFICATION: CLASSIFICATION caption and CLASSIFICATION description. These are typically used by applications as descriptive labels (rather than displaying the object name).

    For example, the TIME_ID column may contain an attribute with the caption and description of 'Day'.

  7. Execute the following statement to re-create the attribute dimension for Time. This statement contains CLASSIFICATION values that define metadata for each element of the attribute dimension.

    CREATE OR REPLACE ATTRIBUTE DIMENSION sh_times_attr_dim
     CLASSIFICATION caption VALUE 'Time'
     CLASSIFICATION description VALUE 'Time'
    USING sh.times
    ATTRIBUTES (
     time_id
      CLASSIFICATION caption VALUE 'Day'
      CLASSIFICATION description VALUE 'Day',
     calendar_month_desc
      CLASSIFICATION caption VALUE 'Calendar Month'
      CLASSIFICATION description VALUE 'Calendar Month',
     end_of_cal_month
      CLASSIFICATION caption VALUE 'End of Calendar Month'
      CLASSIFICATION description VALUE 'End of Calendar Month',
     calendar_quarter_desc
      CLASSIFICATION caption VALUE 'Calendar Quarter'
      CLASSIFICATION description VALUE 'Calendar Quarter',
     end_of_cal_quarter
      CLASSIFICATION caption VALUE 'End of Calendar Quarter'
      CLASSIFICATION description VALUE 'End of Calendar Quarter',
     calendar_year
      CLASSIFICATION caption VALUE 'Calendar Year'
      CLASSIFICATION description VALUE 'Calendar Year',
     end_of_cal_year
      CLASSIFICATION caption VALUE 'End of Calendar Year'
      CLASSIFICATION description VALUE 'End of Calendar Year',
     fiscal_month_desc
      CLASSIFICATION caption VALUE 'Fiscal Month'
      CLASSIFICATION description VALUE 'Fiscal Month',
     end_of_fis_month
      CLASSIFICATION caption VALUE 'End of Fiscal Month'
      CLASSIFICATION description VALUE 'End of Fiscal Month',
     fiscal_quarter_desc
      CLASSIFICATION caption VALUE 'Fiscal Quarter'
      CLASSIFICATION description VALUE 'Calendar Quarter',
     end_of_fis_quarter
      CLASSIFICATION caption VALUE 'End of Fiscal Quarter'
      CLASSIFICATION description VALUE 'End of Fiscal Quarter',
     fiscal_year
      CLASSIFICATION caption VALUE 'Fiscal Year'
      CLASSIFICATION description VALUE 'Fiscal Year',
     end_of_fis_year
      CLASSIFICATION caption VALUE 'End of Fiscal Year'
      CLASSIFICATION description VALUE 'End of Fiscal Year'
     )
    LEVEL day 
     CLASSIFICATION caption VALUE 'Day'
     CLASSIFICATION description VALUE 'Day'
     KEY time_id
     MEMBER NAME to_char(time_id)
     MEMBER CAPTION to_char(time_id)
     MEMBER DESCRIPTION to_char(time_id)
     ORDER BY time_id
     DETERMINES(calendar_month_desc,fiscal_month_desc)
    LEVEL calendar_month
     CLASSIFICATION caption VALUE 'Calendar Month'
     CLASSIFICATION description VALUE 'Calendar Month'
     KEY calendar_month_desc
     MEMBER NAME calendar_month_desc
     MEMBER CAPTION calendar_month_desc
     MEMBER DESCRIPTION calendar_month_desc
     ORDER BY end_of_cal_month
     DETERMINES(calendar_quarter_desc)
    LEVEL calendar_quarter
     CLASSIFICATION caption VALUE 'Calendar Quarter'
     CLASSIFICATION description VALUE 'Calendar Quarter'
     KEY calendar_quarter_desc
     MEMBER NAME calendar_quarter_desc
     MEMBER CAPTION calendar_quarter_desc
     MEMBER DESCRIPTION calendar_quarter_desc
     ORDER BY end_of_cal_quarter
     DETERMINES(calendar_year)
    LEVEL calendar_year
     CLASSIFICATION caption VALUE 'Calendar Year'
     CLASSIFICATION description VALUE 'Calendar Year'
     KEY calendar_year
     MEMBER NAME TO_CHAR(calendar_year)
     MEMBER CAPTION TO_CHAR(calendar_year)
     MEMBER DESCRIPTION TO_CHAR(calendar_year)
    LEVEL fiscal_month
     CLASSIFICATION caption VALUE 'Fiscal Month'
     CLASSIFICATION description VALUE 'Fiscal Month' 
     KEY fiscal_month_desc
     MEMBER NAME fiscal_month_desc
     MEMBER CAPTION fiscal_month_desc
     MEMBER DESCRIPTION fiscal_month_desc
     ORDER BY end_of_fis_month
     DETERMINES(fiscal_quarter_desc)
    LEVEL fiscal_quarter
     CLASSIFICATION caption VALUE 'Fiscal Quarter'
     CLASSIFICATION description VALUE 'Fiscal Quarter'
     KEY fiscal_quarter_desc
     MEMBER NAME fiscal_quarter_desc
     MEMBER CAPTION fiscal_quarter_desc
     MEMBER DESCRIPTION fiscal_quarter_desc
     ORDER BY end_of_fis_quarter
     DETERMINES(fiscal_year)
    LEVEL fiscal_year
     CLASSIFICATION caption VALUE 'Fiscal Year'
     CLASSIFICATION description VALUE 'Fiscal Year'
     KEY fiscal_year
     MEMBER NAME TO_CHAR(fiscal_year)
     MEMBER CAPTION TO_CHAR(fiscal_year)
     MEMBER DESCRIPTION TO_CHAR(fiscal_year)
     ORDER BY end_of_fis_year
    ALL MEMBER NAME 'ALL YEARS';
  8. Create Hierarchies for Time

    As stated previously, attribute values are queried from hierarchies. Therefore, a hierarchy references an attribute dimension, and it inherits metadata from it.

    As a result, the CREATE HIERARCHY statement is relatively simple.

  9. Execute the follow SQL statement to create the Calendar Year hierarchy:

    CREATE OR REPLACE HIERARCHY sh_times_calendar_hier
     CLASSIFICATION caption VALUE 'Calendar Year'
     CLASSIFICATION description VALUE 'Calendar Year'
    USING sh_times_attr_dim (
     day CHILD OF
     calendar_month CHILD OF
     calendar_quarter CHILD OF
     calendar_year
     );
  10. Now, execute the follow SQL statement to create the Fiscal Year hierarchy:

    CREATE OR REPLACE HIERARCHY sh_times_fiscal_hier
     CLASSIFICATION caption VALUE 'Fiscal Year'
     CLASSIFICATION description VALUE 'Fiscal Year'
    USING sh_times_attr_dim (
     day CHILD OF
     fiscal_month CHILD OF
     fiscal_quarter CHILD OF
     fiscal_year
     );

    Result: The SQL Developer Worksheet and Script Output should look like this:.

    Description of this image

    Note: The Script Output indicates that the Time attribute dimension object was created, and then re-created (with the CLASSIFICATION options added). In addition, two Hierarchy objects for Time have been created: one for the Calendar year, and another for the Fiscal year.

  11. Query the Calendar Year hierarchy:

    SELECT *
    FROM sh_times_calendar_hier
    WHERE calendar_year = '2001';

    The output looks like this:

    Description of this image
  12. Query the Fiscal Year hierarchy:

    SELECT * FROM
    sh_times_fiscal_hier
    WHERE fiscal_year = '2001';

    The output looks like this:

    Description of this image

Product Attribute Dimension and Hierarchy

The PRODUCTS table supports a hierarchy of levels Product > Subcategory > Category.

In this exercise, you examine the contents of the PRODUCTS table, create the attribute dimension for Product, and then create a hierarchy for Product.

    Examine the PRODUCTS Table
  1. First, view some data in the PRODUCTS table by executing the following SELECT statement:

    SELECT * FROM sh.products;

    The output looks like this:

    Description of this image
  2. Next, execute the following SELECT statement to view the columns that can be used to create a product hierarchy:

    SELECT
     prod_id,
     prod_name,
     prod_subcategory,
     prod_category
    FROM
     sh.products
    ORDER BY
     prod_category,
     prod_subcategory,
     prod_name;

    The output looks like this:

    Description of this image

    Notes: The ORDER BY clause used in the SELECT statement above sorts values in hierarchical order. Therefore, columns selected in the query above can be used to build a product hierarchy.

  3. Create the Attribute Dimension for Product

    As you learned in the previous exercise, an attribute dimension is a metadata object that contains attributes and levels that are created from columns in a dimension table.

    In addition, you learned that CLASSIFICATION values may be added to the attribute dimension definition in order to describe the elements of the attributes and levels.

  4. Execute the follow SQL statement to create an attribute dimension for Product. This object defines the attributes and levels for the Product hierarchy, and includes CLASSIFICATION values for all elements:

    CREATE OR REPLACE ATTRIBUTE DIMENSION sh_products_attr_dim
    USING sh.products
    ATTRIBUTES (
     prod_id
      CLASSIFICATION caption VALUE 'Product'
      CLASSIFICATION description VALUE 'Product',
     prod_name
      CLASSIFICATION caption VALUE 'Product'
      CLASSIFICATION description VALUE 'Product',
     prod_subcategory
      CLASSIFICATION caption VALUE 'Subcategory'
      CLASSIFICATION description VALUE 'Subcategory',
     prod_category
      CLASSIFICATION caption VALUE 'Category'
      CLASSIFICATION description VALUE 'Category'
     )
    LEVEL PRODUCT
     CLASSIFICATION caption VALUE 'Product'
     CLASSIFICATION description VALUE 'Product'
     KEY prod_id
     MEMBER NAME prod_name
     MEMBER CAPTION prod_name
     MEMBER DESCRIPTION prod_name
     ORDER BY prod_name
     DETERMINES (prod_subcategory)
    LEVEL SUBCATEGORY
     CLASSIFICATION caption VALUE 'Subcategory'
     CLASSIFICATION description VALUE 'Subcategory'
     KEY prod_subcategory
     MEMBER NAME prod_subcategory
     MEMBER CAPTION prod_subcategory
     MEMBER DESCRIPTION prod_subcategory
     ORDER BY prod_subcategory
     DETERMINES (prod_category)
    LEVEL CATEGORY
     CLASSIFICATION caption VALUE 'Category'
     CLASSIFICATION description VALUE 'Category'
     KEY prod_category
     MEMBER NAME prod_category
     MEMBER CAPTION prod_category
     MEMBER DESCRIPTION prod_category
     ORDER BY prod_category
    ALL MEMBER NAME 'ALL PRODUCTS';

    Result: The Script Output indicates that the Product attribute dimension object was created.

  5. Create the Hierarchy for Product

    As stated previously, attribute values are queried from hierarchies. Therefore, the Product hierarchy references the Product attribute dimension and inherits metadata from it.

  6. Execute the follow SQL statement to create the Product hierarchy:

    CREATE OR REPLACE HIERARCHY sh_products_hier
     CLASSIFICATION caption VALUE 'Products'
     CLASSIFICATION description VALUE 'Products'
    USING sh_products_attr_dim (
     product CHILD OF
     subcategory CHILD OF
     category
     );

    Result: The Script Output indicates that the Hierarchy object for Product has been created.

  7. Query the Product hierarchy:

    SELECT * FROM sh_products_hier;

    The output looks like this:

    Description of this image

Customer Attribute Dimension and Hierarchy

The Customer dimension is a little more complicated than Time and Product, because Customer data is in two tables (CUSTOMERS and COUNTRIES) rather than one.

In this case, cities might not always be unique within states, and states might not always be unique within countries. For example, there could be a Miami in Florida and a Miami in Ohio. (With this small sample data set that condition does not actually exist, but you will create the hierarchy to cover this case.)

Therefore, in this exercise you:

  • Examine the contents of both the CUSTOMERS and COUNTRIES tables. Then, create a view that joins certain columns from the CUSTOMERS and COUNTRIES tables.
  • Create an attribute dimension for Customer from the joined view.
  • Create a hierarchy for Customer.
    Examine and Join the CUSTOMERS and COUNTRIES Tables
  1. View some data in the CUSTOMERS table by executing the following SELECT statement:

    SELECT * FROM sh.customers WHERE rownum <= 30;

    The output looks like this:

    Description of this image
  2. View data in the COUNTRIES table by executing the following SELECT statement:

    SELECT * FROM sh.countries;

    The output looks like this:

    Description of this image
  3. Next, create a view that joins the two tables and creates unique values for customer_name, city_id, and state_province_id:

    CREATE OR REPLACE VIEW sh_customers_dim_view AS
    SELECT
     a.cust_id,
     a.cust_last_name ||
     ', ' ||
     a.cust_first_name AS customer_name,
     a.cust_city ||
     ', ' ||
     a.cust_state_province ||
     ', ' || a.country_id AS city_id,
     a.cust_city AS city_name,
     a.cust_state_province ||
     ', ' || a.country_id AS state_province_id,
     a.cust_state_province AS state_province_name,
     b.country_id,
     b.country_name,
     b.country_subregion AS subregion,
     b.country_region AS region
    FROM sh.customers a, sh.countries b where a.country_id = b.country_id;

    Result: The Script Output pane shows that the view is created.

  4. Query the view by executing the following SELECT statement:

    SELECT *
    FROM sh_customers_dim_view
    WHERE rownum <= 50
    ORDER BY
     region,
     subregion,
     country_name,
     state_province_id,
     city_id;
  5. The output looks like this:

    Description of this image
    Create the Attribute Dimension for Customers

    Now, the attribute dimension object for Customers may be created from the joined view.

  6. Execute the follow SQL statement to create an attribute dimension for Customers:

    CREATE OR REPLACE ATTRIBUTE DIMENSION sh_customers_attr_dim
    USING sh_customers_dim_view
    ATTRIBUTES (
     cust_id
      CLASSIFICATION caption VALUE 'Customer'
      CLASSIFICATION description VALUE 'Customer',
     customer_name
      CLASSIFICATION caption VALUE 'Customer'
      CLASSIFICATION description VALUE 'Customer',
     city_id
      CLASSIFICATION caption VALUE 'City'
      CLASSIFICATION description VALUE 'City',
     city_name
      CLASSIFICATION caption VALUE 'City'
      CLASSIFICATION description VALUE 'City',
     state_province_id
      CLASSIFICATION caption VALUE 'State Province'
      CLASSIFICATION description VALUE 'State Province',
     state_province_name
      CLASSIFICATION caption VALUE 'State Province'
      CLASSIFICATION description VALUE 'State Province',
     country_id
      CLASSIFICATION caption VALUE 'Country'
      CLASSIFICATION description VALUE 'Country',
     country_name
      CLASSIFICATION caption VALUE 'Country'
      CLASSIFICATION description VALUE 'Country',
     subregion
      CLASSIFICATION caption VALUE 'Subregion'
      CLASSIFICATION description VALUE 'Subregion',
     region
      CLASSIFICATION caption VALUE 'Region'
      CLASSIFICATION description VALUE 'Region'
     )
    LEVEL CUSTOMER
     CLASSIFICATION caption VALUE 'Customer'
     CLASSIFICATION description VALUE 'Customer'
     KEY cust_id
     MEMBER NAME customer_name
     MEMBER CAPTION customer_name
     MEMBER DESCRIPTION customer_name
     ORDER BY customer_name
     DETERMINES (city_id)
    LEVEL CITY
     CLASSIFICATION caption VALUE 'City'
     CLASSIFICATION description VALUE 'City'
     KEY city_id
     MEMBER NAME city_name
     MEMBER CAPTION city_name
     MEMBER DESCRIPTION city_name
     ORDER BY city_name
     DETERMINES (state_province_id)
    LEVEL STATE_PROVINCE
     CLASSIFICATION caption VALUE 'State_Province'
     CLASSIFICATION description VALUE 'State Province'
     KEY state_province_id
     MEMBER NAME state_province_name
     MEMBER CAPTION state_province_name
     MEMBER DESCRIPTION state_province_name
     ORDER BY state_province_name
     DETERMINES (country_id)
    LEVEL COUNTRY
     CLASSIFICATION caption VALUE 'Country'
     CLASSIFICATION description VALUE 'Country'
     KEY country_id
     MEMBER NAME country_name
     MEMBER CAPTION country_name
     MEMBER DESCRIPTION country_name
     ORDER BY country_name
     DETERMINES (subregion)
    LEVEL SUBREGION
     CLASSIFICATION caption VALUE 'Subregion'
     CLASSIFICATION description VALUE 'Subregion'
     KEY subregion
     MEMBER NAME subregion
     MEMBER CAPTION subregion
     MEMBER DESCRIPTION subregion
     ORDER BY subregion
     DETERMINES (region)
    LEVEL REGION
     CLASSIFICATION caption VALUE 'Region'
     CLASSIFICATION description VALUE 'Region'
     KEY region
     MEMBER NAME region
     MEMBER CAPTION region
     MEMBER DESCRIPTION region
     ORDER BY region
    ALL MEMBER NAME 'ALL CUSTOMERS';

    Result: The Script Output pane shows that the Customers attribute dimension is created.

  7. Create the Hierarchy for Customers

    The Customers hierarchy references the Customers attribute dimension and inherits metadata from it.

  8. Execute the follow SQL statement to create the Customers hierarchy:

    CREATE OR REPLACE HIERARCHY sh_customers_hier
     CLASSIFICATION caption VALUE 'Customers'
     CLASSIFICATION description VALUE 'Customers'
    USING sh_customers_attr_dim (
     customer CHILD OF
     city CHILD OF
     state_province CHILD OF
     country CHILD OF
     subregion CHILD OF
     region
     );

    Result: The Script Output pane shows that the Customers Hierarchy object is created.

  9. Query the Customers hierarchy:

    SELECT * FROM sh_customers_hier WHERE rownum <= 50;

    The output looks like this:

    Description of this image

    Notes:

    • With 5 levels, notice that this hierarchy returns quite a few columns. Some columns are hierarchical attribute columns and other columns are simply attribute columns.
    • Hierarchical columns are created by the database. Some hierarchical columns combine unpivot attributes into a single column, creating rows for aggregate level attributes. For example, each of the MEMBER NAME attributes are combined into a single MEMBER_NAME column. Other hierarchical columns are calculated, for example the HIER_ORDER column.
  10. Query the hierarchical attributes in the SH_CUSTOMERS_HIER hierarchy.

    SELECT
     member_name,
     member_unique_name,
     member_caption,
     member_description,
     level_name,
     hier_order,
     depth,
     parent_level_name,
     parent_unique_name
    FROM sh_customers_hier
    WHERE rownum <= 50;

    The output looks like this:

    Description of this image

    Notes:

    • These hierarchical attribute columns will exist in every hierarchy.
    • In particular, notice that the hierarchy view returns data at each level. That is, at both detail and aggregate levels.
  11. Now, query the Customers hierarchy with the following sort criteria:

    SELECT
     member_name,
     member_unique_name,
     level_name,
     hier_order
    FROM sh_customers_hier
    WHERE rownum <= 50
    ORDER BY hier_order;

    The output looks like this:

    Description of this image

    Note: The HIER_ORDER column sorts children within parents.

  12. Finally, execute the following query. This query demonstrates that attribute columns contain data from each attribute in the ATTRIBUTES list of the attribute dimension:

    SELECT
     cust_id,
     customer_name,city_id,
     city_name,
     state_province_id,
     state_province_name,
     country_id,
     country_name,
     subregion,
     region
    FROM sh_customers_hier
    WHERE rownum <= 50;

    The output looks like this:

    Description of this image

Channels and Promotions Attribute Dimensions and Hierarchies

There are no new concepts associated with attribute dimensions and hierarchies for the Channels and Promotions dimensions.

Therefore, In this exercise you quickly examine the contents of the CHANNELS and PROMOTIONS tables, create the attribute dimension for each dimension, and create a hierarchy for each dimension.

    Examine the CHANNELS Table
  1. First, view data in the CHANNELS table by executing the following SELECT statement:

    SELECT * FROM sh.channels;

    The output looks like this:

    Description of this image
  2. Create the Attribute Dimension for Channels
  3. Execute the follow SQL statement to create an attribute dimension for Channels. This object defines the attributes and levels for the Channels hierarchy, and includes CLASSIFICATION values for all elements:

    CREATE OR REPLACE ATTRIBUTE DIMENSION sh_channels_attr_dim
      CLASSIFICATION caption VALUE 'Channels'
      CLASSIFICATION description VALUE 'Channels'
    USING sh.channels
    ATTRIBUTES (
     channel_id
      CLASSIFICATION caption VALUE 'Channel'
      CLASSIFICATION description VALUE 'Channel',
     channel_desc
      CLASSIFICATION caption VALUE 'Channel'
      CLASSIFICATION description VALUE 'Channel',
     channel_class
      CLASSIFICATION caption VALUE 'Channel Class'
      CLASSIFICATION description VALUE 'Channel Class'
     )
    LEVEL CHANNEL
     CLASSIFICATION caption VALUE 'Channel'
     CLASSIFICATION description VALUE 'Channel'
     KEY channel_id
     MEMBER NAME channel_desc
     MEMBER CAPTION channel_desc
     ORDER BY channel_desc
     DETERMINES (channel_class)
    LEVEL CHANNEL_CLASS
     CLASSIFICATION caption VALUE 'Channel_Class'
     CLASSIFICATION description VALUE 'Channel Class'
     KEY channel_class
     MEMBER NAME channel_class
     MEMBER CAPTION channel_class
     ORDER BY channel_class
    ALL MEMBER NAME 'ALL CHANNELS';
  4. Create the Hierarchy for Channels

    The Channels hierarchy references the Channels attribute dimension and inherits metadata from it.

  5. Execute the follow SQL statement to create the Channels hierarchy:

    CREATE OR REPLACE HIERARCHY sh_channels_hier
     CLASSIFICATION caption VALUE 'Channels'
     CLASSIFICATION description VALUE 'Channels'
    USING sh_channels_attr_dim (
     channel CHILD OF
     channel_class
     );
  6. Query the Channels hierarchy:

    SELECT * FROM sh_channels_hier;

    The output looks like this:

    Description of this image
  7. Examine the PROMOTIONS Table
  8. First, view data in the PROMOTIONS table by executing the following SELECT statement:

    SELECT * FROM sh.promotions;

    The output looks like this:

    Description of this image
  9. Create the Attribute Dimension for Promotions
  10. Execute the follow SQL statement to create an attribute dimension for Promotions. This object defines the attributes and levels for the Promotions hierarchy, and includes CLASSIFICATION values for all elements:

    CREATE OR REPLACE ATTRIBUTE DIMENSION sh_promotions_attr_dim
      CLASSIFICATION caption VALUE 'Promotions'
      CLASSIFICATION description VALUE 'Promotions'
    USING sh.promotions ATTRIBUTES (
     promo_id
      CLASSIFICATION caption VALUE 'promotion'
      CLASSIFICATION description VALUE 'promotion',
     promo_name
      CLASSIFICATION caption VALUE 'promotion'
      CLASSIFICATION description VALUE 'promotion',
     promo_subcategory
      CLASSIFICATION caption VALUE 'Subcategory'
      CLASSIFICATION description VALUE 'Subcategory',
     promo_category
      CLASSIFICATION caption VALUE 'Category'
      CLASSIFICATION description VALUE 'Category'
     )
    LEVEL PROMOTION
     CLASSIFICATION caption VALUE 'promotion'
     CLASSIFICATION description VALUE 'promotion'
     KEY promo_id
     MEMBER NAME promo_name
     MEMBER CAPTION promo_name
     ORDER BY promo_name
     DETERMINES (promo_subcategory)
    LEVEL SUBCATEGORY
     CLASSIFICATION caption VALUE 'Subcategory'
     CLASSIFICATION description VALUE 'Subcategory'
     KEY promo_subcategory
     MEMBER NAME promo_subcategory
     MEMBER CAPTION promo_subcategory
     ORDER BY promo_subcategory
     DETERMINES (promo_category)
    LEVEL CATEGORY
     CLASSIFICATION caption VALUE 'Category'
     CLASSIFICATION description VALUE 'Category'
     KEY promo_category
     MEMBER NAME promo_category
     MEMBER CAPTION promo_category
     ORDER BY promo_category
    ALL MEMBER NAME 'ALL PROMOTIONS';
  11. Create the Hierarchy for Promotions

    The Promotions hierarchy references the Promotions attribute dimension and inherits metadata from it.

  12. Execute the follow SQL statement to create the Promotions hierarchy:

    CREATE OR REPLACE HIERARCHY sh_promotions_hier
     CLASSIFICATION caption VALUE 'Promotions'
     CLASSIFICATION description VALUE 'Promotions'
    USING sh_promotions_attr_dim (
     promotion CHILD OF
     subcategory CHILD OF
     category
     );
  13. Query the Promotions hierarchy:

    SELECT * FROM sh_promotions_hier;

    The output looks like this:

    Description of this image

Analytic View for Sales Data

The Analytic View itself joins the dimension hierarchy objects to the fact table in order to present fact data as measures. A measure may come directly from the fact table or it may be calculated using an expression.

In this sample data set, the fact table contains keys for product, customer, time, channel and promotions and the measures QUANTITY_SOLD and AMOUNT_SOLD.

In this exercise, you examine data from the SALES table and then create the analytic view for Sales.

  1. First, view some data in the SALES table by executing the following SELECT statement:

    SELECT * FROM sh.sales WHERE rownum <= 50;

    The output looks like this:

    Description of this image
  2. Next, execute the follow SQL statement to create the analytic view for Sales:

    CREATE OR REPLACE ANALYTIC VIEW sh_sales_history_av
      CLASSIFICATION caption VALUE 'Sales History (SH Sample Schema)'
      CLASSIFICATION description VALUE 'Sales History by Time, Product, Customer, Channel and
    Promotion'
    -- This AV references the SALES fact table.
    USING sh.sales
    -- This is where hierarchies are joined into the analytic view.
    DIMENSION BY (
     sh_times_attr_dim
      KEY time_id REFERENCES time_id
      HIERARCHIES (sh_times_calendar_hier DEFAULT, sh_times_fiscal_hier),
     sh_products_attr_dim
      KEY prod_id REFERENCES prod_id
      HIERARCHIES (sh_products_hier DEFAULT),
     sh_customers_attr_dim
      KEY cust_id REFERENCES cust_id
      HIERARCHIES (sh_customers_hier DEFAULT),
     sh_channels_attr_dim
      KEY channel_id REFERENCES channel_id
      HIERARCHIES (sh_channels_hier DEFAULT),
     sh_promotions_attr_dim
      KEY promo_id REFERENCES promo_id
      HIERARCHIES (sh_promotions_hier DEFAULT)
     )
    MEASURES (
     -- Amount sold maps to the fact table.
     amount_sold FACT amount_sold
      CLASSIFICATION caption VALUE 'Amount Sold'
      CLASSIFICATION description VALUE 'Amount Sold'
      CLASSIFICATION format_string VALUE '999,999,999,999.99',
     -- Quantity sold maps to the fact table.
     quantity_sold FACT quantity_sold
      CLASSIFICATION caption VALUE 'Quantity Sold'
      CLASSIFICATION description VALUE 'Quantity Sold'
      CLASSIFICATION format_string VALUE '999,999,999,999'
     )
    DEFAULT MEASURE amount_sold;

    Result: The Script Ouput pane shows that the analytic view is created.

    NOTES:

    As shown in the CREATE statement above:

    • The ANALYTIC VIEW joins the dimension hierarchy objects that you created previously to the SALES fact table.
    • Sales table facts are idenfied as MEASURES (amount_sold and quantity_sold).
    • CLASSFICATION values are used to define captions, descriptions, and format string metadata.

Using Analytic Views

In this section, you learn how to:

  • Run basic queries against the analytic view for sales.
  • Add calculated measures to the analytic view.
  • Run and examine sample analytic vew queries that combine fact and calculated measures.

Execute Basic Analytic View Queries

In this exercise, you run basic queries against the analytic view for sales.

Notes:

  • An analytic view query does not need to select from all hierarchies.
  • An analytic view automatically aggregates the data for the selected hierarchy levels in the query.

The first query returns data at the calendar year, category and region levels (therefore, the SH_CHANNELS_HIER and SH_PROMOTIONS_HIER hierarchies are not used in the query).

  1. Execute the following SELECT statement:

    SELECT
     sh_times_calendar_hier.member_name AS TIMES_CALENDAR_HIER,
     sh_products_hier.member_name AS PRODUCTS_HIER,
     sh_customers_hier.member_name AS CUSTOMERS_HIER,
     amount_sold,
     quantity_sold
    FROM sh_sales_history_av
     HIERARCHIES (
      sh_times_calendar_hier,
      sh_products_hier,
      sh_customers_hier
     )
    WHERE
     sh_times_calendar_hier.level_name = 'CALENDAR_YEAR'
     AND sh_products_hier.level_name = 'CATEGORY'
     AND sh_customers_hier.level_name = 'REGION'
    ORDER BY
     sh_times_calendar_hier.hier_order,
     sh_products_hier.hier_order,
     sh_customers_hier.hier_order;

    The output looks like this:

    Description of this image

    Results: Measure data is automatically agregated for the specified hierarchy levels: Calendar Year, Product Category and Customer Region.

    The next query includes all five hierarchies. Compare the results.

  2. Execute the following SELECT statement:

    SELECT
     sh_times_calendar_hier.member_name AS TIMES_CALENDAR_HIER,
     sh_products_hier.member_name AS PRODUCTS_HIER,
     sh_customers_hier.member_name AS CUSTOMERS_HIER,
     sh_channels_hier.member_name AS CHANNELS_HIER,
     sh_promotions_hier.member_name AS PROMOTIONS_HIER,
     amount_sold,
     quantity_sold
    FROM sh_sales_history_av
     HIERARCHIES (
      sh_times_calendar_hier,
      sh_products_hier,
      sh_customers_hier,
      sh_channels_hier,
      sh_promotions_hier
     )
    WHERE
     sh_times_calendar_hier.level_name = 'CALENDAR_YEAR'
     AND sh_products_hier.level_name = 'CATEGORY'
     AND sh_customers_hier.level_name = 'REGION'
     AND sh_channels_hier.level_name = 'CHANNEL_CLASS'
     AND sh_promotions_hier.level_name = 'CATEGORY'
    ORDER BY
     sh_times_calendar_hier.hier_order,
     sh_products_hier.hier_order,
     sh_customers_hier.hier_order,
     sh_channels_hier.hier_order,
     sh_promotions_hier.hier_order;

    The output looks like this:

    Description of this image

    Results: Measure data is automatically agregated for the specified dimension hierarchy levels: Calendar Year, Product Category, Customer Region, Channel Class and Promotion Category.

Add Calculated Measures to the Analytic View

Calculated measures are added to an analytic view using expressions that reference attributes in hierarchies, hierarchy values, and other measures.

The following analytic view adds a variety of examples.

  1. Execute the following statement to recreate the analytic view to include the calculated measures:

    CREATE OR REPLACE ANALYTIC VIEW sh_sales_history_av
     CLASSIFICATION caption VALUE 'Sales History (SH Sample Schema)'
     CLASSIFICATION description VALUE 'Sales History by Time, Product, Customer, Channel and Promotion'
    USING sh.sales
    DIMENSION BY (
     sh_times_attr_dim
      KEY time_id REFERENCES time_id
      HIERARCHIES (sh_times_calendar_hier DEFAULT, sh_times_fiscal_hier),
     sh_products_attr_dim
      KEY prod_id REFERENCES prod_id
      HIERARCHIES (sh_products_hier DEFAULT),
     sh_customers_attr_dim
      KEY cust_id REFERENCES cust_id
      HIERARCHIES (sh_customers_hier DEFAULT),
     sh_channels_attr_dim
      KEY channel_id REFERENCES channel_id
      HIERARCHIES (sh_channels_hier DEFAULT),
     sh_promotions_attr_dim
      KEY promo_id REFERENCES promo_id
      HIERARCHIES (sh_promotions_hier DEFAULT)
     )
    MEASURES (
     -- Amount sold maps to the fact table.
     amount_sold FACT amount_sold
      CLASSIFICATION caption VALUE 'Amount Sold'
      CLASSIFICATION description VALUE 'Amount Sold'
      CLASSIFICATION format_string VALUE '999,999,999,999.99',
     -- Quantity sold maps to the fact table.
     quantity_sold FACT quantity_sold
      CLASSIFICATION caption VALUE 'Quantity Sold'
      CLASSIFICATION description VALUE 'Quantity Sold'
      CLASSIFICATION format_string VALUE '999,999,999,999',
     -- Ratio of amount sold for the current value to the parent product value.
     amt_sold_shr_parent_prod AS
      (SHARE_OF(amount_sold HIERARCHY sh_products_hier PARENT))
      CLASSIFICATION caption VALUE 'Sales Product Share of Parent'
      CLASSIFICATION description VALUE 'Sales Product Share of Parent'
      CLASSIFICATION format_string VALUE '999.99',
     -- Ratio of amount sold for the current value to the parent customer value.
     sales_shr_parent_cust AS
      (SHARE_OF(amount_sold HIERARCHY sh_customers_hier PARENT))
      CLASSIFICATION caption VALUE 'Sales Customer Share of Parent'
      CLASSIFICATION description VALUE 'Sales Customer Share of Parent'
      CLASSIFICATION format_string VALUE '999,999,999,999.99',
     --
     -- Calendar Year measures
     --
     -- Sales Calendar Year to Date
     sales_cal_ytd AS
      (SUM(amount_sold) OVER (HIERARCHY sh_times_calendar_hier
        BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER
        WITHIN ANCESTOR AT LEVEL calendar_year))
      CLASSIFICATION caption VALUE 'Sales Calendar YTD'
      CLASSIFICATION description VALUE 'Sales Calendar YTD'
      CLASSIFICATION format_string VALUE '999,999,999,999.99',
     -- Sales same period 1 year ago.
     sales_cal_year_ago AS
      (LAG(amount_sold) OVER (HIERARCHY sh_times_calendar_hier
        OFFSET 1 ACROSS ANCESTOR AT LEVEL calendar_year))
      CLASSIFICATION caption VALUE 'Sales Calendar Year Ago'
      CLASSIFICATION description VALUE 'Sales Year Ago'
      CLASSIFICATION format_string VALUE '$999,999,999,999.99',
     -- Change in sales for the current period as compared to the same period 1 year ago.
     sales_chg_cal_year_ago AS
      (LAG_DIFF(amount_sold) OVER (HIERARCHY sh_times_calendar_hier
        OFFSET 1 ACROSS ANCESTOR AT LEVEL calendar_year))
      CLASSIFICATION caption VALUE 'Sales Change Calendar Year Ago'
      CLASSIFICATION description VALUE 'Sales Change Calendar Year Ago'
      CLASSIFICATION format_string VALUE '$999,999,999,999.99',
     -- Percent change in sales for the current period as compared to the same period 1 year ago.
     sales_pctchg_cal_year_ago AS
      (LAG_DIFF_PERCENT(amount_sold) OVER (HIERARCHY sh_times_calendar_hier
        OFFSET 1 ACROSS ANCESTOR AT LEVEL calendar_year))
      CLASSIFICATION caption VALUE 'Sales Percent Change Calendar Year Ago'
      CLASSIFICATION description VALUE 'Sales Percent Change Calendar Year Ago'
      CLASSIFICATION format_string VALUE '999.99',
     --
     -- Fiscal Year measures
     --
     sales_fis_ytd AS
     (SUM(amount_sold) OVER (HIERARCHY sh_times_fiscal_hier
        BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER
        WITHIN ANCESTOR AT LEVEL fiscal_year))
      CLASSIFICATION caption VALUE 'Sales Fiscal YTD'
      CLASSIFICATION description VALUE 'Sales Fiscal YTD'
      CLASSIFICATION format_string VALUE '999,999,999,999.99',
     sales_fis_year_ago AS
      (LAG(amount_sold) OVER (HIERARCHY sh_times_fiscal_hier
        OFFSET 1 ACROSS ANCESTOR AT LEVEL fiscal_year))
      CLASSIFICATION caption VALUE 'Sales Fiscal Year Ago'
      CLASSIFICATION description VALUE 'Sales Fiscal Year Ago'
      CLASSIFICATION format_string VALUE '$999,999,999,999.99',
     -- Change in sales for the current period as compared to the same period 1 year ago.
     sales_chg_fis_year_ago AS
      (LAG_DIFF(amount_sold) OVER (HIERARCHY sh_times_fiscal_hier
        OFFSET 1 ACROSS ANCESTOR AT LEVEL fiscal_year))
      CLASSIFICATION caption VALUE 'Sales Change Fiscal Year Ago'
      CLASSIFICATION description VALUE 'Sales Change Fiscal Year Ago'
      CLASSIFICATION format_string VALUE '$999,999,999,999.99',
     -- Percent change in sales for the current period as compared to the same period 1 year ago.
     sales_pctchg_fis_year_ago AS
      (LAG_DIFF_PERCENT(amount_sold) OVER (HIERARCHY sh_times_fiscal_hier
        OFFSET 1 ACROSS ANCESTOR AT LEVEL fiscal_year))
      CLASSIFICATION caption VALUE 'Sales Percent Change Fiscal Year Ago'
      CLASSIFICATION description VALUE 'Sales Percent Change Fiscal Year Ago'
      CLASSIFICATION format_string VALUE '999.99'
     )
    DEFAULT MEASURE amount_sold;

    NOTES:

    Ten new calculated measures are added to the analytic view, including:

    • Two Share measures (Share measures calculate the ratio of a current row to parent row, ancestor row or all rows in the current level):
      • Sales Product Share of Parent
      • Sales Customer Share of Parent
    • Four Calendar Year measures:
      • Sales Calendar YTD
      • Sales Calendar Year Ago
      • Sales Change Calendar Year Ago
      • Sales Percent Change Calendar Year Ago
    • Four Fiscal Year measures:
      • Sales Fiscal YTD
      • Sales Fiscal Year Ago
      • Sales Change Fiscal Year Ago
      • Sales Percent Change Fiscal Year Ago

Examine Sample Analytic View Queries

In this exercise, you run several queries that illustrate the power and simplicity of analytic view queries to perform a variety of analysis.

  1. First, execute the following SELECT statement to view Amount sold by Calendar Year, Product Category, and Customer Region:

    SELECT
     sh_times_calendar_hier.member_name AS time,
     sh_products_hier.member_name AS product,
     sh_customers_hier.member_name AS customer,
     amount_sold
    FROM sh_sales_history_av
     HIERARCHIES (
      sh_times_calendar_hier,
      sh_products_hier,
      sh_customers_hier
     )
    WHERE
     sh_times_calendar_hier.level_name = 'CALENDAR_YEAR'
     AND sh_products_hier.level_name = 'CATEGORY'
     AND sh_customers_hier.level_name = 'REGION'
    ORDER BY
     sh_times_calendar_hier.hier_order,
     sh_products_hier.hier_order,
     sh_customers_hier.hier_order;

    The output looks like this:

    Description of this image

    Note: The results are identical for the Amount Sold measure, as seen previously in the first analytic view query.

  2. Now, add Sales Calendar Year Ago and Sales Percent Change Calendar Year Ago to the previous SELECT statement. Note that the sort order is changed to make it easier to view year over year changes.

    SELECT
     sh_times_calendar_hier.member_name AS time,
     sh_products_hier.member_name AS product,
     sh_customers_hier.member_name AS customer,
     amount_sold,
     sales_cal_year_ago,
     ROUND(sales_pctchg_cal_year_ago,2) AS sales_pctchg_cal_year_ago
    FROM sh_sales_history_av
     HIERARCHIES (
      sh_times_calendar_hier,
      sh_products_hier,
      sh_customers_hier
     )
    WHERE
     sh_times_calendar_hier.level_name = 'CALENDAR_YEAR'
     AND sh_products_hier.level_name = 'CATEGORY'
     AND sh_customers_hier.level_name = 'REGION'
    ORDER BY
     sh_products_hier.hier_order,
     sh_customers_hier.hier_order,
     sh_times_calendar_hier.hier_order;

    The output looks like this:

    Description of this image
  3. Execute the following SELECT statement to view Sales Calendar Year to Date, at the Calendar Month level, for Women in Europe:

    SELECT
     sh_times_calendar_hier.member_name AS time,
     sh_products_hier.member_name AS product,
     sh_customers_hier.member_name AS customer,
     amount_sold,
     sales_cal_ytd
    FROM sh_sales_history_av
     HIERARCHIES (
      sh_times_calendar_hier,
      sh_products_hier,
      sh_customers_hier
     )
    WHERE
     sh_times_calendar_hier.level_name = 'CALENDAR_MONTH'
     AND sh_products_hier.member_name = 'Women'
     AND sh_customers_hier.member_name = 'Europe'
    ORDER BY
     sh_times_calendar_hier.hier_order,
     sh_products_hier.hier_order,
     sh_customers_hier.hier_order;

    The output looks like this:

    Description of this image
  4. Execute the following SELECT statement to view the Share of Sales for in each geographic region to Sales for Women in Calendar Year 2000:

    SELECT
     sh_times_calendar_hier.member_name AS time,
     sh_products_hier.member_name AS product,
     sh_customers_hier.member_name AS customer,
     ROUND(sales_shr_parent_cust,2) * 100 || '%' AS sales_shr_parent_cust,
     amount_sold
    FROM sh_sales_history_av
     HIERARCHIES (
      sh_times_calendar_hier,
      sh_products_hier,
      sh_customers_hier
     )
    WHERE
     sh_times_calendar_hier.member_name = '2000'
     AND sh_products_hier.member_name = 'Women'
     AND sh_customers_hier.level_name = 'REGION';

    The output looks like this:

    Description of this image
  5. Add all Calendar Years to the previous query and note that the Share of Sales automatically breaks out by Year and Amount Sold:

    SELECT
     sh_times_calendar_hier.member_name AS time,
     sh_products_hier.member_name AS product,
     sh_customers_hier.member_name AS customer,
     ROUND(sales_shr_parent_cust,2) * 100 || '%' AS sales_shr_parent_cust,
     amount_sold
    FROM sh_sales_history_av
     HIERARCHIES (
      sh_times_calendar_hier,
      sh_products_hier,
      sh_customers_hier
     )
    WHERE
     sh_times_calendar_hier.level_name = 'CALENDAR_YEAR'
     AND sh_products_hier.member_name = 'Women'
     AND sh_customers_hier.level_name = 'REGION'
    ORDER BY
     sh_times_calendar_hier.hier_order,
     amount_sold;

    The output looks like this:

    Description of this image
  6. This last example shows that selecting hierarchical columns such as MEMBER_NAME simplifies SQL generation. This is because the hierarchical columns do not need to change depending on the hierarchy or level of aggregation.

    In this case, the Share of Sales query automatically breaks out by Product Category and Amount Sold for the Year 2000.

    Note: It is, however, perfectly fine to select from 'regular' attribute columns. In this case, the Share of Sales automatically breaks out by Year and Amount Sold:

    SELECT
     sh_times_calendar_hier.calendar_year,
     sh_products_hier.prod_category,
     sh_customers_hier.region,
     ROUND(sales_shr_parent_cust,2) * 100 || '%' AS sales_shr_parent_cust,
     amount_sold
    FROM sh_sales_history_av
     HIERARCHIES (
      sh_times_calendar_hier,
      sh_products_hier,
      sh_customers_hier
     )
    WHERE
     sh_times_calendar_hier.member_name = '2000'
     AND sh_products_hier.level_name = 'CATEGORY'
     AND sh_customers_hier.level_name = 'REGION'
    ORDER BY
     sh_products_hier.hier_order,
     amount_sold;

    The output looks like this:

    Description of this image

Want to Learn More?