Skip to content

Generate an Oracle analytic view

Semantic Hub can generate an Oracle semantic layer with reusable measures and requested hierarchies. Select ORACLE_ANALYTIC_VIEW as the model compiler target and supply verified source-column metadata. Generation produces a model.sql file; your database deployment process executes it.

Use an Oracle source connection and a DATABASE deployment. For example, a source connection can read tables in TPCH while the deployment creates the semantic layer in AV_TEST:

deployments:
  DATABASE:
    schema: AV_TEST

The output names attribute dimensions after datasets, attributes after fields, hierarchies and levels after their SHL definitions, and measures after metrics. Each field is declared once in its attribute dimension. Several requested hierarchies on one dataset share that dimension. Repeated dataset roles receive qualified names to distinguish their occurrences. The compiler references fact and dimension sources separately. Models using direct columns and guaranteed relationships can generate no ordinary views. Fact-local measure calculations use native FACT expressions, including arithmetic and filters evaluated before aggregation. They do not require a projection view. Calculated grouping attributes, NULL members, optional relationships and relationship-dependent inputs can require a source-local view. These views use SELECT * plus calculated columns, preserving the original source columns. The compiler does not generate a combined fact-and-dimension view. Review the SQL and execute its statements in order using an account with access to the sources and permission to create these objects. Generated views, attribute dimensions, hierarchies and analytic views use CREATE OR REPLACE, so deployment replaces existing objects with the same names. Oracle DDL can leave a partial deployment if a later statement fails. Each generated statement starts with a separate -- @SQL: comment line that names its intent and object, such as -- @SQL: Creating Analytic View 'TABLEAU_MODEL_TO_ORACLE_20260918'. The JDBC deployment bridge uses these lines to run the statements one by one.

The target supports one fact dataset, to-one dimension relationships, numeric SUM, AVERAGE, MIN, MAX, row/non-null counts, metric filters and calculations over root-owned measures. Each requested hierarchy produces one Oracle hierarchy. Exposing a field does not create another hierarchy. A dataset used through two relationship paths has a separate binding for its hierarchy in each role. Define at least one hierarchy; use a single level when you want independent grouping by one field. Keys, labels and declared secondary attributes are retained. Fields outside those definitions are not automatically published as grouping attributes. Hierarchies can group members through several named levels. List levels from broad parent to detailed child: for example, [customer_status, customer]. The Oracle compiler reverses them for CHILD OF and checks each child's determination of its parent before generating DDL. Each level key must identify one member and determine exactly one parent. Use value_dimension for its member label and secondary_dimensions for additional attributes. The Oracle compiler rejects cycles among these level dependencies before generating DDL. Verify the dependencies against your source data before deploying. Composite keys and NULL grouping members are supported. Alternate level keys, hidden hierarchies and hierarchy levels owned by another dataset remain unsupported. Oracle level names cannot contain literal double quotes. Missing required type information prevents generation. Models requiring multiple facts, distinct counts, opaque SQL sources or time-intelligence metrics receive an unsupported-capability error.

For the corrected SHUB-99 sample, the single Customer Geography hierarchy produces one attribute dimension, one hierarchy and one analytic view. Its direct geography columns need no dimension projection. The calculated Discount and Net Sales Amount fact fields are named base measures using row expressions in their FACT clauses. This sample binds directly to FACT_SALES_TXN and creates no ordinary view. The net-sales metric remains a separate direct native SUM, without a redundant empty-population CASE. A field's default_aggregation selects its measure aggregation; numeric FACT fields without that hint use SUM. For example, query country-level totals using the generated hierarchy:

SELECT "Customer Geography"."Country" AS country,
       "Customer Count", "Sum of Net Sales Amount"
FROM AV_TEST.TABLEAU_MODEL_TO_ORACLE_20260918
     HIERARCHIES ("Customer Geography")
WHERE "Customer Geography".LEVEL_NAME = 'COUNTRY';

Where verified metadata and relationship guarantees prove a key stays non-null, the compiler uses its existing columns directly. This applies to single and composite keys. For nullable values, optional joins and calculations, the compiler generates a two-part key containing a NULL flag and a typed value. The flag keeps NULL separate from a real zero, date or text value used as the internal default. Composite hierarchy attributes retain every original component. When a declared non-null unique source key determines the remaining root-level components, the compiler can use that key and retain the remaining fields as attributes. Multiple root hierarchies require such a declared source key. Overlapping root-level keys without a suitable uniqueness proof are rejected to avoid an Oracle engine error. This requires no rank windows and preserves the original field values and labels. Related dimensions need an authored level keyed by the relationship target key. The compiler reports an error when that level is missing instead of inventing one. The corrected sample explicitly defines CUSTOMER, keyed by Customer Sk (Dim Customer), below CITY. Roots with multiple hierarchies likewise need an authored level for their shared source key. Optional and unmatched relationships retain an explicit unknown member. Row calculations and filtered measures keep their original evaluation before aggregation. Declare matching relationships only when the source guarantees them; nullable fields can remain nullable.

Hierarchy attributes retain their source types. Generated keys keep NULL grouping values distinct from other members. An entirely empty fact source returns no native analytic-view cells, including at the ALL level. If your application needs a scalar SQL result row for an empty source, use a query with that explicit behavior.

This initial scope was tested on Oracle Cloud 23.26.4.1.0. Performance and other Oracle versions require separate qualification. See Oracle's analytic-view documentation for native query syntax.

When a source view is needed for grouping attributes, relationship keys or related-field calculations, its name combines the contributing dataset names with underscores and ends in _V. The analytic view uses the model name, TABLEAU_MODEL_TO_ORACLE_20260918 in this sample.