Attribute Dimensions and Hierarchies in Oracle 12.2

Oracle 12c Release 2 introduced Analytic Views, a new set of metadata objects that are very useful for Data Warehouses and Business Intelligence applications. In the first blog post about this new feature I will have a detailed look at two of the new object types: Attribute Dimensions and Hierarchies.

Analytic Views are a special kind of views built on top of a dimensional data model (usually a star schema). They allow specific calculations of measures and aggregations on different hierarchy levels of the referenced dimensions. Before we can define an Analytic View, we have to declare additional metadata about the structure of the dimensions.

Hierarchy Information in Dimensions

A dimension typically consists of one or more hierarchies with multiple hierarchy levels. The lowest level defines the granularity of the fact table, the upper levels can be used for aggregations and drill-up capabilities in an OLAP tool. For example, sales amounts can be displayed for each individual product of a shop, but they also can be aggregated for product subcategories or categories. This hierarchy information is not visible in the table definition of a dimension table or the entity relationship diagram of a star schema. Therefore, additional notations like ADAPT (Application Design for Analytical Processing Technologies) are available. The following ADAPT diagram shows a data mart with two fact tables and four dimensions, including their hierarchies and hierarchy levels.

ADAPT is a trademark of Symmetry Corporation

This hierarchy definitions are usually not stored in the database and have to be defined in the metadata layer of a BI tool, for example in the Business Model and Mapping layer of OBIEE or the Universe in SAP BusinessObjects. But not all BI tools support such a metadata layer. In Oracle 12c Release 2, it is now possible to define the dimension hierarchies in the data dictionary of the database. Let’s show this with the PRODUCTS dimension of the Sales History (SH) sample schema. Oracle provides a good Live SQL Tutorial Creating an Analytic View for the Sales History (SH) Sample Schema. Because I’m lazy and not yet very experienced yet with the syntax of Attribute Dimensions and Hierarchies, I reuse the examples of this tutorial.

The dimension table SH.PRODUCTS contains many attributes, but we cannot see from the table definition which attribute belongs to which hierarchy level:

SQL> desc products

Name                  Null?    Type           

——————— ——– ————– 

PROD_ID               NOT NULL NUMBER(6)      

PROD_NAME             NOT NULL VARCHAR2(50)   

PROD_DESC             NOT NULL VARCHAR2(4000) 

PROD_SUBCATEGORY      NOT NULL VARCHAR2(50)   

PROD_SUBCATEGORY_ID   NOT NULL NUMBER         

PROD_SUBCATEGORY_DESC NOT NULL VARCHAR2(2000) 

PROD_CATEGORY         NOT NULL VARCHAR2(50)   

PROD_CATEGORY_ID      NOT NULL NUMBER         

PROD_CATEGORY_DESC    NOT NULL VARCHAR2(2000) 

PROD_WEIGHT_CLASS     NOT NULL NUMBER(3)      

PROD_UNIT_OF_MEASURE           VARCHAR2(20)   

PROD_PACK_SIZE        NOT NULL VARCHAR2(30)   

SUPPLIER_ID           NOT NULL NUMBER(6)      

PROD_STATUS           NOT NULL VARCHAR2(20)   

PROD_LIST_PRICE       NOT NULL NUMBER(8,2)    

PROD_MIN_PRICE        NOT NULL NUMBER(8,2)    

PROD_TOTAL            NOT NULL VARCHAR2(13)   

PROD_TOTAL_ID         NOT NULL NUMBER         

PROD_SRC_ID                    NUMBER         

PROD_EFF_FROM                  DATE           

PROD_EFF_TO                    DATE           

PROD_VALID                     VARCHAR2(1)    

Creating an Attribute Dimension

A new object called Attribute Dimension is created on top of the table. This object does not contain any physical data, but additional metadata about the attributes of the dimension table. Information that is typically defined in the BI tools can be added here:

• Short and long description of the attributes in the dimension
• Hierarchy levels with their short and long descriptions and assignments of attributes to the hierarchy levels
• Sort order of the members within one hierarchy level

In the example, only four attributes of the table PRODUCTS are used, but of course it is possible to add all required columns.

 

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’;

 

 

Creating a Hierarchy

Because more than one hierarchy can be defined for each dimension (see time dimension in the ADAPT example above), the hierarchy declaration is created with a separate Hierarchy object. For the products dimension we create only one hierarchy with levels ‘Product’, ‘Subcategory’ and ‘Category’:

 

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);

 

 

The created hierarchy consists of the four attributes PROD_ID, PROD_NAME, PROD_SUBCATEGORY and PROD_CATEGORY, but also some additional columns with metadata for all members:

 

SQL> desc sh_products_hier

Name               Role Type         

—————— —- ———— 

PROD_ID            KEY  NUMBER(6)    

PROD_NAME          PROP VARCHAR2(50) 

PROD_SUBCATEGORY   KEY  VARCHAR2(50) 

PROD_CATEGORY      KEY  VARCHAR2(50) 

MEMBER_NAME        HIER VARCHAR2(50) 

MEMBER_UNIQUE_NAME HIER VARCHAR2(67) 

MEMBER_CAPTION     HIER VARCHAR2(50) 

MEMBER_DESCRIPTION HIER VARCHAR2(50) 

LEVEL_NAME         HIER VARCHAR2(11) 

HIER_ORDER         HIER NUMBER       

DEPTH              HIER NUMBER(10)   

IS_LEAF            HIER NUMBER       

PARENT_LEVEL_NAME  HIER VARCHAR2(11) 

PARENT_UNIQUE_NAME HIER VARCHAR2(67) 

 

Unlike the dimension table, the hierarchy does not only contain a row for each product, but additional rows for the upper hierarchy level. We can use the hierarchy object for queries, for example to show the hierarchy for all products in the category ‘Electronics’:

 

SELECT rpad(‘ ‘, depth * 2)||member_description

  FROM sh_products_hier

 WHERE prod_category = ‘Electronics’

ORDER BY hier_order;

 

 

The result shows the 13 products of the selected category and some additional lines for the category and the four subcategories. The predefined column DEPTH is used to indent the description for each hierarchy level. Within each hierarchy level, the descriptions are displayed in alphabetical order:

 

 Electronics

    Game Consoles

      Y Box

    Home Audio

      8.3 Minitower Speaker

      Home Theatre Package with DVD-Audio/Video Play

    Y Box Accessories

      Extension Cable

      Xtend Memory

    Y Box Games

      Adventures with Numbers

      Bounce

      Comic Book Heroes

      Endurance Racing

      Finding Fido

      Fly Fishing

      Martial Arts Champions

      Smash up Boxing

 

18 rows selected.

 

Further Steps

For a star schema, an Attribute Dimension and one or more Hierarchy objects are created on top of each dimension table. For the time dimension, the Attribute Dimension is created with DIMENSION TYPE TIME. This allows additional time series calculations. If a dimension consists of multiple dimension tables (e.g. in a snowflake schema), a view has to be created to join all corresponding tables of a dimension. More details and several examples are described in the Oracle Database Data Warehousing Guide 12c Release 2, chapter 25.

For the fact table of a star schema, an Analytic View can be created. It refers the Attribute Dimensions and Hierarchies created before and allows different aggregations and calculations of measures based on the columns in the fact table. For more information, see blog post Analytic Views: Powerful Oracle 12.2 Feature for Business Intelligence.

Back to the Old Days

The definition of hierarchy information within dimensions is not new in Oracle. A similar feature was already available since Oracle 8i: the CREATE DIMENSION command. For the PRODUCTS dimension table, a hierarchy can be defined like this:

 

 

CREATE DIMENSION products_dim 

    LEVEL product         IS (products.prod_id)

    LEVEL subcategory     IS (products.prod_subcategory_id) 

    LEVEL category        IS (products.prod_category_id) 

    LEVEL prod_total    IS (products.prod_total_id) 

    HIERARCHY prod_rollup (

        product        CHILD OF 

        subcategory CHILD OF 

        category    CHILD OF

        prod_total

    ) 

    ATTRIBUTE product DETERMINES 

        (products.prod_name, products.prod_desc,

         prod_weight_class, prod_unit_of_measure,

         prod_pack_size, prod_status, prod_list_price, prod_min_price)

    ATTRIBUTE subcategory DETERMINES 

        (prod_subcategory, prod_subcategory_desc)

    ATTRIBUTE category DETERMINES 

        (prod_category, prod_category_desc)

    ATTRIBUTE prod_total DETERMINES 

        (prod_total);

 

 

What is the difference between the old Dimension object of Oracle 8i and the new Attribute Dimension and Hierarchy objects of Oracle 12c? Do we still need the CREATE DIMENSION syntax?

Actually, all the information of the Dimension object exists in the new objects as well: The hierarchy levels and assignments of attributes to the levels are defined in the Attribute Dimension, and the relationships between the levels are defined in the Hierarchy object. So, the old Dimension objects seem to be unnecessary. But they are  used for a different purpose.

While Attribute Dimensions and Hierarchies are used in combination with Analytic Views, the Dimension objects are required for Materialized Views with Query Rewrite. For example, if you have a Materialized Views with aggregated data on subcategory level, Oracle can use is for an aggregation on category level. But only, if the optimizer knows about the relationships between subcategories and categories. For this type of Advanced Query Rewrite, it is still necessary to define the hierarchy metadata with the old syntax above.

It would be nice if Oracle improves Query Rewrite in a way that the optimizer can use the Attribute Dimensions and Hierarchies to find a suitable Materialized View for a specific aggregation level. I don’t think this will be the case in the next release. But who knows – dreams are always allowed.