Introduction

Analytic view attribute dimensions metadata that maps to physical tables (or view) and defines levels. Attribute dimensions may be mapped to a single star-style dimension table or multiple tables in a snowflake configuration. This tutorial provides an example of a snowflake configuration using multiple tables.

Be sure to run the Perquisite SQL before continuing.

Examine Sample Data

The same data is contained in three tables with time data at the month, quarter, and year levels

SELECT *
FROM time_month_dim
ORDER BY month_end_date;

SELECT *
FROM time_quarter_dim
ORDER BY quarter_end_date;

SELECT *
FROM time_year_dim
ORDER BY year_name;

The next query joins the month, quarter, and year tables.

SELECT
	y.year_id
	, y.year_name
    , q.quarter_id
	, q.quarter_name
	, m.month_id
	, m.month_name
	, m.month_end_date
FROM
	time_year_dim y
	, time_quarter_dim q
	, time_month_dim m
WHERE
	m.quarter_id = q.quarter_id
	AND q.year_id = y.year_id
ORDER BY
	m.month_end_date;

Create Attribute Dimension

To use multiple tables, list each table in the USING clause (comma separated) and add join paths. Joins paths join the tables as they would be joined in a query. Note that JOIN PATHs are not comma separated.

Be sure to alias attributes.

CREATE OR REPLACE ATTRIBUTE DIMENSION time_ad
USING time_month_dim AS m, time_quarter_dim AS q, time_year_dim AS y
  JOIN PATH m_to_q ON m.quarter_id = q.quarter_id
  JOIN PATH q_to_y ON q.year_id = y.year_id
ATTRIBUTES (
  m.month_id AS month_id
  , m.month_name AS month_name
  , m.month_end_date AS month_end_date
  , q.quarter_id AS quarter_id
  , q.quarter_name AS quarter_name
  , y.year_id AS year_id
  , y.year_name AS year_name
  )
LEVEL year
  KEY year_id
  MEMBER NAME year_name
  ORDER BY month_end_date
LEVEL quarter
  KEY quarter_id
  MEMBER NAME quarter_name
  ORDER BY month_end_date
  DETERMINES (year_id)
LEVEL month
  KEY month_id
  MEMBER NAME month_name
  ORDER BY month_end_date
  DETERMINES (quarter_id, month_end_date)
ALL 
  MEMBER NAME 'ALL'
  MEMBER CAPTION 'All'; 

Now you can create the hierarchy.

 CREATE OR REPLACE HIERARCHY time_hier
  USING time_ad
  (month
    CHILD OF quarter
    CHILD OF year);

Query the hierarchy.

SELECT *
FROM time_hier
ORDER BY hier_order;

Questions or Comments?

Get in touch at william.endress@oracle.com.