CREATE ANALYTIC VIEW
VIEW statement to create an analytic view. An analytic view specifies the source of its fact data and defines measures that describe calculations or other analytic operations to perform on the data. An analytic view also specifies the attribute dimensions and hierarchies that define the rows of the analytic view.
To create an analytic view in your own schema, you must have the
VIEW system privilege. To create an analytic view in another user's schema, you must have the
VIEW system privilege.
REPLACE to replace an existing definition of an analytic view with a different definition.
FORCE and NOFORCE
FORCE to force the creation of the analytic view even if it does not successfully compile. If you specify
NOFORCE, then the analytic view must compile successfully, otherwise an error occurs. The default is
Specify the schema in which to create the analytic view. If you do not specify a schema, then Oracle Database creates the analytic view in your own schema.
Specify a name for the analytic view.
Use the sharing clause if you want to create the object in an application root in the context of an application maintenance. This type of object is called an application common object and it can be shared with the application PDBs that belong to the application root.
You can specify how the object is shared using one of the following sharing attributes:
METADATA- A metadata link shares the metadata, but its data is unique to each container. This type of object is referred to as a metadata-linked application common object.
NONE- The object is not shared and can only be accessed in the application root.
Use the classification clause to specify values for the
DESCRIPTION classifications and to specify user-defined classifications. Classifications provide descriptive metadata that applications may use to provide information about analytic views and their components.
You may specify any number of classifications for the same object. A classification can have a maximum length of 4000 bytes.
DESCRIPTION classifications, you may use the DDL shortcuts
' or the full classification syntax.
You may vary the classification values by language. To specify a language for the
DESCRIPTION classification, you must use the full syntax. If you do not specify a language, then the language value for the classification is
NULL. The language value must either be
NULL or a valid
Use this clause to declare the sources that you want to use to build the analytic view.
You can specify any of the following sources to build an analytic view:
A fact table or a view.
External tables and remote tables.
A table or a view in another schema. You can specify an alias for the table or the view.
REMOTE on a given source to indicate to the analytic view that the given source is backed by remote data and should be optimized as remote data.
Specify the attribute dimensions of the analytic view.
Specify an attribute dimension, columns of the fact table, columns of the attribute dimension, and hierarchies that are related in the analytic view.
KEY keyword, specify one or more columns in the fact table.
REFERENCES keyword, specify attributes of the attribute dimensions that the analytic view is dimensioned by. Each attribute must be a level key. The
DISTINCT keyword supports the use of denormalized fact tables, in which the attribute dimension and fact data are in the same table. Use
DISTINCT when an attribute dimension is defined using the fact table.
HIERARCHIES keyword, specify the hierarchies in the analytic view that use the attribute dimension.
Specify an attribute dimension. You can specify an alias for an attribute dimension, which is required if you use the same dimension more than once or if you use multiple dimensions with the same name from different schemas.
Specify a hierarchy. You can specify an alias for a hierarchy. You can specify one of the hierarchies in the list as the default. If you do not specify a default, the first hierarchy in the list is the default.
Specify the measures for the analytic view.
Define a measure using either a single fact column or an expression over measures in this analytic view. A measure can be either a base measure or a calculated measure.
Define a base measure by optionally specifying a fact column or a
meas_aggregate_clause, or both. If you do not specify a fact column, then the analytic view uses the column of the fact table that has the same name as the measure. If a column by the same name does not exist, an error is raised.
Define a calculated measure by specifying an analytic view expression. The expression may reference other measures in the analytic view, but may not reference fact columns. Calculated measures do not have an aggregate clause because they're computed over the aggregated base measures.
For the syntax and descriptions of analytic view expressions, see Analytic View Expressions.
Specify a measure to use as the default measure for the analytic view. If you do not specify a measure, the first measure defined is the default.
Specify a default aggregation function for a base measure. If you do not specify an aggregation function, then the function specified by the
default_aggregate_clause is used.
The functions that you can aggregate by in the
default_aggregate_clause are the following:
Specify a default aggregation function for all of the base measures in the analytic view. If you do not specify a default aggregation function, then the default value is
Specify a cache clause to improve query response time when an appropriate materialized view is available. You can specify one or more cache specifications.
Specify one or more measure groups for a cache clause. To include all measure groups, specify
ALL. Each measure group can contain one or more measures and one or more level groupings. A level grouping can contain one or more level specifications.
Specify one or more levels for a level grouping of a measure group for a cache specification. Specify only one level per hierarchy. A materialized view must exist that contains the aggregated values for the hierarchy level.
If you specify the
USING clause, then the given table will be directly used at query time, if the analytic view determines that this is the best cache to use for the query. The typical shape of the cache is a column for each measure in the
MEASURE GROUP plus a column per level key of each level in the cache. There is one row per member combination, across all given levels, that has at least one contributing row from the fact table. The column names of the given table must match a specific format so that the analytic view can identify which columns line up with which measures and level keys. The names of the columns can be retrieved from the
DBMS_HIERARCHY package using the method
This method takes in the cache to generate SQL for and returns the SQL text for the cache. This SQL text can be used to create a materialized view for the cache. It can also be used to create an aggregate table using
CREATE TABLE AS.
At compile time of the analytic view, the following checks will be made in regard to the materialized table:
- The table must exist and be accessible by the owner of the analytic view
- The columns of the table must include the expected cache columns
Specify this clause to explictly state the fact columns that can be accessed by the dervided analytic view. You can aggregate any columns of the fact table that appear in
fact_columns_clause at query time with the aggregation operator specified in the derived analytic view
If an alias is provided for the fact column, then the alias name must be used in the dervided analytic view. The alias defaults to the fact column name if not specified.
It is a semantic analysis error, if two or more fact columns are specified with the same name.
If you do not specify this clause, then no fact columns can be accessed for aggregation by the derived analytic view. This is the default.
Specify this clause on an analytic view, if you want the view to participate in detecting queries that match its semantic model and transform it into an equivalent analytic view query if appropriate.
You cannot use
qry_transform_clause on an analytic view in the following cases:
When the analytic view contains an attribute dimension with more than one dimension table (either a snowflake or starflake schema)
When a dimension table joins to the fact table at a level that is above the leaf level of the dimension (i.e. a
NORELYis specified and one or more base tables are remote tables
The new clause allows for an optional
NORELY keyword. The default is
The analytic view metadata can be viewed as a set of constraints on the underlying data. These constraints are not enforced by the database, but can be checked using the
RELY keyword indicates that the constraints implied on the data by the analytic view metadata can be relied upon without validation when being considered for base table transform. If
NORELY is specified, then the data must be in a valid state in relation to the metadata in order for the base table transform to take place.
The following is a description of the
desc SALES_FACT Name Null? Type ----------------- ----- ------------- MONTH_ID VARCHAR2(10) CATEGORY_ID NUMBER(6) STATE_PROVINCE_ID VARCHAR2(120) UNITS NUMBER(6) SALES NUMBER(12,2)
The following example creates the
SALES_AV analytic view using the
CREATE OR REPLACE ANALYTIC VIEW sales_av USING sales_fact DIMENSION BY (time_attr_dim -- An attribute dimension of time data KEY month_id REFERENCES month_id HIERARCHIES ( time_hier DEFAULT), product_attr_dim -- An attribute dimension of product data KEY category_id REFERENCES category_id HIERARCHIES ( product_hier DEFAULT), geography_attr_dim -- An attribute dimension of store data KEY state_province_id REFERENCES state_province_id HIERARCHIES ( geography_hier DEFAULT) ) MEASURES (sales FACT sales, -- A base measure units FACT units, -- A base measure sales_prior_period AS -- Calculated measures (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1)), sales_year_ago AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year)), chg_sales_year_ago AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year)), pct_chg_sales_year_ago AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year)), sales_qtr_ago AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL quarter)), chg_sales_qtr_ago AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL quarter)), pct_chg_sales_qtr_ago AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL quarter)) ) DEFAULT MEASURE SALES;