This tutorial is the first of a two-part series.
In part one, you will design an analytic view using the simplest DDL statements possible for the sample data set. This analytic view will provide all the necessary structure needed to aggregate data, navigate hierarchies, and support calculations. It will not include descriptive labels. It will rely on default values wherever possible.
In part two, you will walk through a series of queries of increasing complexity. Queries will be presented for both tables and the analytic view. You will see that queries selecting from the analytic view use relatively simple, template-like SQL, even when more advanced calculations are required.
You will see that analytic views can be a great foundation for custom-built applications where the developer is responsible for generating queries.
Analytic views can be queried with simple, template-like queries that eliminate the need for joins, GROUP BY, and aggregation operators. Column names are consistent from one query and one AV to the next, often simplifying or eliminating the need for metadata used for query generation. And if metadata is needed, it is available in the Oracle data dictionary.
Queries selecting from analytic views can use analytic view calculation expressions to enhance applications with a variety of time series calculations, shares and hierarchical ratios, and rankings.
If you have not already done so, consider running the Creating Analytic Views - Getting Started tutorial. It will expose more features of the analytic view. You can also use it to compare a full featured analytic view with the simple analytic view presented in this tutorial.
This tutorial uses data in the AV schema. The AV schema contains a simple star schema. It is the same schema used by the Creating Analytic Views - Getting Started tutorial.
Start with a sample query using data the AV schema. The analytic view that will be designed will support queries such as this.
SELECT
t.year_name
, p.department_name
, g.region_name
, SUM(f.units)
, SUM(f.sales)
FROM
av.time_dim t
, av.product_dim p
, av.geography_dim g
, av.sales_fact f
WHERE
t.month_name = f.month_id
AND p.category_id = f.category_id
AND g.state_province_id = f.state_province_id
GROUP BY
t.year_name
, p.department_name
, g.region_name
ORDER BY
t.year_name
, p.department_name
, g.region_name;
The analytic view will use a subset of columns from tables in the AV schema.
Start by reviewing the coluns in the SALES_FACT table. We see that the table includes key columns for time, product and geography, and two fact columns. This table will need to be joined to dimension tables.
SELECT * FROM av.sales_fact;
Review the TIME_DIM table.
SELECT * FROM av.time_dim;
The following subset of columns will be used to create a time hierarchy.
SELECT
year_name
, quarter_name
, month_name
FROM
av.time_dim
ORDER BY
month_end_date;
Review the PRODUCT_DIM table.
SELECT * FROM av.product_dim;
The following subset of columns will be used to create a product hierarchy.
SELECT
department_name
, category_name
, category_id
FROM
av.product_dim
ORDER BY
department_name
, category_name;
Review the GEOGRPAHY_DIM table.
SELECT * FROM av.geography_dim;
The following subset of columns will be used to create a geography hierarchy.
SELECT
region_name
, country_name
, state_province_name
, state_province_id
FROM
av.geography_dim
ORDER BY
region_name
, country_name
, state_province_id;
Data from the fact table
All of the columns that will be used are combined in the following query. Using a query to flatten a collection of dimension tables and the fact table can be a good way to begin thinking about the analytic view design. The analytic view that will be created will be an improved version of this query.
SELECT
t.year_name
, t.quarter_name
, t.month_name
, p.department_name
, p.category_name
, g.region_name
, g.country_name
, g.state_province_name
, f.units
, f.sales
FROM
av.time_dim t
, av.product_dim p
, av.geography_dim g
, av.sales_fact f
WHERE
t.month_name = f.month_id
AND p.category_id = f.category_id
AND g.state_province_id = f.state_province_id
ORDER BY
t.month_end_date
, p.department_name
, p.category_name
, g.region_name
, g.country_name;
Here is a very brief introduction to the elements of analytic view DDL. A complete discussion is available in the tutorial Creating Analytic Views - Getting Started Deep Dive.
Just skim the following to become aquatinted with the terminology. Do not worry about the details.
An attribute dimension is a database object that contains most of the metadata of the dimension and hierarchies. Attributes dimensions are reusable. More than one hierarchy can reference an attribute dimension.
Every attribute dimension contains the following elements. There are additional elements that are not discussed in this tutorial.
The USING clause references a table or view.
The ATTRIBUTES list contains a list of columns of the form table used by the attribute dimension. For this tutorial, you may think of attributes and columns as synonymous.
A LEVEL represents a level of aggregation in a hierarchy.
A level KEY contains the attributes (column) that is the primary key of the level. As the primary key, it should have unique values of the level.
The ORDER BY property sorts values in the level, just like ORDER BY in a query. ORDER BY uses an attribute from the ATTRIBUTES list.
MEMBER NAME, MEMBER CAPTION, MEMBER DESCRIPTION properties list attributes that are used as descriptive properties of the level key. The properties are optional, but at least one is usually used when the level key is not an end user-friendly value.
The DETERMINES clause acts as a constraint. It contains a list of one or more attributes. The DETERMINES clause tells the hierarchy and analytic view that there is one and only one value of the determined attribute for each value of the key attribute.
A hierarchy is a view. You can select from it with a SELECT statements.
A hierarchy is simply a list of levels arranged as child levels and parent levels, where the parent level is a higher level of aggregation.
Hierarchies are reusable. They may be referenced by more than one analytic view.
The USING clause references an attribute dimension.
The analytic view is the main event! It brings together attribute dimensions, hierarchies, and the FACT table.
The USING clause references the fact table.
The DIMENSIONS clauses list the dimensions and hierarchies the analytic views use.
The REFERENCES clause defines joins between the analytic view (and thus the fact table) to the attribute dimension (and therefore the dimension table).
The HIERARCHIES list contains the hierarchies used by the analytic view.
The MEASURES list contains a list of measures. FACT measures come from the fact table.
The analytic view objects will reference only the selected subset of columns that are used by the required elements of attribute dimensions, hierarchies, and the analytic view. Optional elements will be set to default values.
The time attribute dimension and hierarchy will include levels Month, Quarter, and Year.
Create the time attribute dimension.
CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
USING av.time_dim
ATTRIBUTES
(year_name,
quarter_name,
month_name,
month_end_date)
LEVEL year
KEY year_name
LEVEL quarter
KEY quarter_name
DETERMINES (
year_name)
LEVEL month
KEY month_name
ORDER BY month_end_date
DETERMINES (
month_end_date,
quarter_name);
Create the time hierarchy view.
CREATE OR REPLACE HIERARCHY time_hier
USING time_attr_dim
(month CHILD OF
quarter CHILD OF
year);
Notes:
Select from the TIME_HIER hierarchy.
SELECT * FROM time_hier;
Create the product attribute dimension.
CREATE OR REPLACE ATTRIBUTE DIMENSION product_attr_dim
USING av.product_dim
ATTRIBUTES
(department_name,
category_name,
category_id)
LEVEL department
KEY department_name
LEVEL category
KEY category_id
MEMBER NAME category_name
DETERMINES (
department_name);
Create the product hierarchy view.
CREATE OR REPLACE HIERARCHY product_hier
USING product_attr_dim
(category CHILD OF
department);
Select from the PRODUCT_HIER hierarchy.
SELECT * FROM product_hier;
Notes:
Create the geography attribute dimension.
CREATE OR REPLACE ATTRIBUTE DIMENSION geography_attr_dim
USING av.geography_dim
ATTRIBUTES
(region_name
, country_name
, state_province_name
, state_province_id)
LEVEL region
KEY region_name
LEVEL country
KEY country_name
DETERMINES (
region_name)
LEVEL state_province
KEY state_province_id
MEMBER NAME state_province_name
DETERMINES (
country_name);
Create the geography hierarchy view.
CREATE OR REPLACE HIERARCHY geography_hier
USING geography_attr_dim
(state_province CHILD OF
country CHILD OF
region);
Select from the GEOGRAPHY_HIER hierarchy.
SELECT * FROM geography_hier;
Notes:
The analytic view can now be created.
CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY
(time_attr_dim
KEY month_id REFERENCES month_name
HIERARCHIES (
time_hier DEFAULT),
product_attr_dim
KEY category_id REFERENCES category_id
HIERARCHIES (
product_hier DEFAULT),
geography_attr_dim
KEY state_province_id
REFERENCES state_province_id
HIERARCHIES (
geography_hier DEFAULT)
)
MEASURES
(sales FACT sales,
units FACT units,
sales_prior_period AS (LAG(SALES) OVER (HIERARCHY time_hier OFFSET 1))
);
Notes:
Analytic views contain attribute columns and hierarchical columns. Attribute columns replicate the data from the source tables (some transformations are possible, so the data might not always be an exact replication). Hierarchical columns represent the different roles that the data plays and are automatically calculated by the hierarchy.
This example uses built-in hierarchical columns.
SELECT
time_hier.member_name AS time,
product_hier.member_name AS product,
geography_hier.member_name as geography,
sales,
sales_prior_period,
units
FROM sales_av
HIERARCHIES (
time_hier,
product_hier,
geography_hier)
WHERE
time_hier.level_name = 'YEAR'
AND product_hier.level_name = 'DEPARTMENT'
AND geography_hier.level_name = 'REGION'
ORDER BY
time_hier.hier_order,
product_hier.hier_order,
geography_hier.hier_order;
Every hierarchy view and analytic view has the same set of hierarchical columns. Data from all levels of aggregation is presented in the same hierarchical columns. This can dramatically simplify SQL generation., The following query selects from data at the Quarter, Country, and Department levels.
Note the the query does not include joins or GROUP BY. Joins and aggregation rules are embedded in the analytic view and hierarchies. Listing the hierarchies replaces joins and GROUP BY in the query. Only the hierarchies used in the select list or filters needs to be included in the hierarchies list.
SELECT
time_hier.member_name AS time,
product_hier.member_name AS product,
geography_hier.member_name as geography,
sales,
sales_prior_period,
units
FROM sales_av
HIERARCHIES (
time_hier,
product_hier,
geography_hier)
WHERE
time_hier.level_name = 'QUARTER'
AND product_hier.level_name = 'DEPARTMENT'
AND geography_hier.level_name = 'COUNTRY'
ORDER BY
time_hier.hier_order,
product_hier.hier_order,
geography_hier.hier_order;
The following query uses attribute columns, which reflect the original columns from the tables.
SELECT
year_name,
department_name,
region_name,
sales,
sales_prior_period,
units
FROM sales_av
HIERARCHIES (
time_hier,
product_hier,
geography_hier)
WHERE
time_hier.level_name = 'YEAR'
AND product_hier.level_name = 'DEPARTMENT'
AND geography_hier.level_name = 'REGION'
ORDER BY
year_name,
department_name,
region_name;
Queries can use any combination of attribute and hierarchical columns.
The hierarchy views and analytic view created in this tutorial contain all the structure needed to support aggregations, hierarchical navigation, and calculations. The elements in the DDL statements those that are required or recommended for best practices with this particular data set. This DDL is excludes optional elements such as classifications that are used to provide additional descriptive metadata.
This style of analytic view would be appropriate for custom applications such as those developed using Oracle Application Expression (APEX). Application development is simplified because analytic view can be queried using simple, re-usable query templates and calculations expressions.
Part 2 of this tutorial focuses on SQL generation. It will compare queries written against tables with queries written against this analytic view.