Query Oracle Analytic Views Using Power BI, Tableau and other BI Tools

January 9, 2024 | 10 minute read
William Endress
Autonomous Database Product Management
Text Size 100%:

Analytic Views: An In-Database Semantic Layer

Oracle analytic views are a feature of every edition of the Oracle Database.  Using analytic views, a subject matter expert can fully define how a data set (a collection of tables) should calculate and present data to applications and business users.  Analytic views define how data is aggregated, calculated, navigated, and presented to applications and business users.  They are layered over one or more tables (local tables, views, external tables, etc.), hiding the underlying complexity.  Analytic views manage and use aggregate caches to improve query performance.  In short, analytic views are an in-database semantic layer supporting business intelligence and analytic applications.

Dimensional-Relational Duality

Analytic views and fast columnar data stores are modern implementations of online analytical processing (OLAP). You might find my blog post The Evolution of Online Analytical Processing in the Oracle Database, interesting if you are familiar with OLAP.

One of the motivations for separating the semantic layer (the dimensional model) from the physical layer is to create a relational-dimensional duality. The same data set can be queried with relational and dimensional (cube-like) semantics.

If you are familiar with analytic views, you know that you can query them using hierarchical (cube-like) semantics and query the tables used by the analytic view using relational semantics. You might not know that you can also query the analytic view using relational semantics using the familiar SELECT .. FROM .. GROUP BY query pattern. This query pattern allows applications like Power BI and Tableau to query analytic views.

Dimensional-Relational Duality using Analytic Views
Dimensional-relational duality using Oracle analytic views

 

Why would anyone want to query an analytic view using GROUP BY queries when they can query the underlying tables directly? Many of the advantages to using an analytic view that are available to an analytic view-aware application are also available to applications using GROUP BY queries. These advantages include:

  • Hiding the underlying complexity of the physical tables and joins.
  • Simplifying calculation definition and query generation.
  • Query optimization and aggregate caches.
  • A built-in metadata repository (in the Oracle Data Dictionary).
  • Define once in the Database and share across applications = less work and more consistency.
  • Multiple language support.

In other words, it's a lot easier.

Hierarchical Queries

Let's start with a quick look at using hierarchical queries that select from an analytic view.

Every hierarchy and analytic view has a hierarchical representation that includes attributes, hierarchical attributes, and measures.  The data sources (tables and views), joins, aggregation rules, and measure calculations are embedded in the analytic view and, thus, do not need to be expressed within the query.  The HIERARCHIES clause replaces joins and GROUP BY.

The following query SELECTs from the analytic view using hierarchical columns (member_name, level_name, hier_order).  SELECT .. FROM ... HIERARCHIES ... WHERE is the basic template used to query the hierarchical representation of the analytic view.  Aggregation operators are not required, and the query selects a calculated measure.  By selecting MEMBER_NAME columns and using LEVEL_NAME filters, it is easy to specify the level of aggregation without changing the SELECT list.

SELECT
    time_hier.member_name      AS time
  , product_hier.member_name   AS product
  , geography_hier.member_name AS geography
  , sales
  , sales_year_ago
  , sales_chg_year_ago
  , ROUND(sales_pct_chg_year_ago,2) as sales_pct_chg_year_ago
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'
    AND time_hier.year_name = 'CY2015'
ORDER BY
    time_hier.hier_order
  , product_hier.hier_order
  , geography_hier.hier_order;

Once you learn this basic template, querying analytic views is very easy.  And it's straightforward to generate these queries in custom applications, partly because column names do not change when different levels of aggregation are required.  The following query selects a different calculated measure (SALES_SHARE_OF_PRODUCT) at the Month and Department levels.  Note that the query changes very little.

SELECT
    time_hier.member_name      AS time
  , product_hier.member_name   AS product
  , sales
  , ROUND(sales_shr_of_product,2) AS sales_share_of_product
FROM
    sales_av HIERARCHIES (
        time_hier
    , product_hier
    )
WHERE
        time_hier.level_name = 'QUARTER'
    AND product_hier.level_name = 'DEPARTMENT'
    AND time_hier.year_name = 'CY2015'
ORDER BY
    time_hier.hier_order
  , product_hier.hier_order;

Think back to some of the advantages of using an analytic view:

  • The underlying complexity of data sources is hidden from the application.
  • Aggregation rules are defined in the model.
  • Calculations are defined in the model.
  • Access to aggregate caches is transparent to the application.

Add to that simple, reuseable query templates.

Relational Queries

Most advantages of using hierarchical queries with the analytic view also apply to relational queries selecting from the analytic view.  If your tool does not query analytic views directly, you can still take advantage of analytic views.

Creating a Relational Representation of an Analytic View

For each hierarchy and analytic view, there is always a hierarchical representation in the database. These are what are found in the USER_HIERARCHIES and USER_ANALYTIC_VIEW dictionary views. These representations are entirely standardized, providing consistency from one analytic view to the next.

Because each tool might work best with different variations of the relational representation, you create the view using a CREATE VIEW statement. The query of the CREATE VIEW statement selects columns from the analytic view. It includes the FACT ROWS keywords to indicate that queries will be processed by the analytic view (rather than the analytic view simply supplying rows that will be processed afterward in SQL).

The following CREATE VIEW statement creates a relational representation of the analytic view that includes columns representing attributes (rather than keys) and the measure of the analytic view.

CREATE OR REPLACE VIEW sales_av_view AS
    SELECT
        time_hier.year_name
      , time_hier.quarter_name
      , time_hier.month_name
      , time_hier.month_end_date
      , product_hier.department_name
      , product_hier.category_name
      , geography_hier.region_name
      , geography_hier.country_name
      , geography_hier.state_province_name
      , measures.sales
      , measures.sales_prior_period
      , measures.sales_ chg_prior_period
      , measures.sales_pct_chg_prior_period
      , measures.sales_year_ago
      , measures.sales_chg_year_ago
      , measures.sales_pct_chg_year_ago
      , measures.sales_shr_of_product
    FROM
        sales_av FACT ROWS;

Each application might work best with a particular variation.  Many applications would work well with this example because the column aliases will present themselves nicely to business users.

CREATE OR REPLACE VIEW sales_av_view AS
    SELECT
        time_hier.year_name                 AS "Year"
      , time_hier.quarter_name              AS "Quarter"
      , time_hier.month_name                AS "Month"
      , time_hier.month_end_date            AS "Month End Date"
      , product_hier.department_name        AS "Department"
      , product_hier.category_name          AS "Category"
      , geography_hier.region_name          AS "Region"
      , geography_hier.country_name         AS "Country"
      , geography_hier.state_province_name  AS "State Province"
      , measures.sales                      AS "Sales"
      , measures.sales_prior_period         AS "Sales Prior Period"
      , measures.sales_chg_prior_period     AS "Sales Change Prior Period"
      , measures.sales_pct_chg_prior_period AS "Sales Percent Change Prior Period"
      , measures.sales_year_ago             AS "Sales Year Ago"
      , measures.sales_chg_year_ago         AS "Sales Change Year Ago
      , measures.sales_pct_chg_year_ago     AS "Sales Percent Change Year Ago"
      , measures.sales_shr_of_product       AS "Sales Share of Product"
    FROM
        sales_av FACT ROWS;

Each of these views are simply selecting attribute and measure columns form the analytic view. You can use the USER_ANALYTIC_VIEW_COLUMN dictionary view to get a list of available columns. Be sure to include the FACT ROWS keywords.

That's it. Nothing complicated.

Querying the Relational Representation

A relational representation of the analytic view is queried using the SELECT .. SUM .. FROM .. GROUP BY pattern.  Like the hierarchical representation of the analytic view, the relational representation is a flattened (joined) view of the dimension and fact table tables.  No joins are required in queries.  Unlike the hierarchical representation, the view returns only detailed rows.  Use GROUP BY to return aggregate rows. (Hierarchies can be joined to analytic views if that that the desired approach.)

There's one twist.  GROUP BY does not understand how to aggregate data that has been calculated in the analytic view.  For example, GROUP BY does not know how to aggregate a measure such as the Share of Product Sales to the Parent Product.  It doesn't make sense to SUM or AVG this measure.  GROUP BY needs to pass along the value calculated by the analytic view.  The solution to this problem is the AV_AGGREGATE aggregation operator.  AV_AGGREGATE allows a value to be passed through GROUP BY unchanged from the analytic view.

Here is an example of a query that selects a base measure and several calculated measures from an analytic view.  All measures are calculated according to their definition in the analytic view.

SELECT
    year
  , department
  , region
  , AV_AGGREGATE(sales)                  AS sales
  , AV_AGGREGATE(sales_year_ago)         AS sales_year_ago
  , AV_AGGREGATE(sales_chg_year_ago)     AS sales_chg_year_ago
  , AV_AGGREGATE(sales_pct_chg_year_ago) AS sales_pct_chg_year_ago
FROM
    sales_av_view
GROUP BY
    year
  , department
  , region;

That's it. Again, nothing complicated.

Most business intelligence tools have support for aggregate pass-through functions. For example, RAWSQL in Tableau.

Suppose your application doesn't support an aggregate pass-through function or doesn't need to access calculated measures in the analytic view. In that case, you can still query the analytic view using GROUP BY pattern SQL. Base measures (measures based on fact table columns) can be selected using relational aggregation operators.

If you look closely at the previous CREATE VIEW statement, note that the measures are specified as members of the MEASURES hierarchy. That indicates that the column is a measure of the analytic view and should be calculated usng the rules of that measure (including the aggregation operator).  For example:

, measures.sales AS "Sales" -- This is a measure of the analytic view

All analytic view measures must be selected using AV_AGGREGATE.

Columns from the fact table can be included the view using fact$.  For example:

, measures.sales AS "Sales"  -- This is a measure of the analytic view
, fact$sales AS "Sales Fact" -- This is a column in the fact table

FACT$ columns are aggregated using SQL aggregation operators (e.g., SUM, AVG).  The following query selects both types of columns.

SELECT
    year
  , department
  , region
  , AV_AGGREGATE(sales)   AS sales
  , SUM(sales_fact)       AS sum_sales
  , AVG(sales_fact)       AS avg_sales
FROM
    sales_av_view
GROUP BY
    year
  , department
  , region;

Note that the fact column can be selected more than once with different relational aggregation operators.

Always First on Autonomous Database

New analytic view features are always released first on the Oracle Autonomous Database. The relational representation of analytic views is available as of this date on Automonmous Database and Oracle 23c.

Summing It Up

Oracle analytic views offer versatile support for relational and dimensional style queries, making them a powerful tool for data analysis. These views provide a range of features and advantages that are accessible to both forms of queries, ensuring a comprehensive and flexible approach to supporting a wide variety of applications.

One key benefit is the ability to define analytic views once and share them across various applications, fostering consistency and efficiency in data management. The concept of a single version of the truth is upheld, ensuring that users across the organization access a unified and accurate representation of data. Moreover, analytic views facilitate query optimizations, enhancing the performance of analytic applications. 

Additionally, the support for multiple languages adds to the accessibility and usability of Oracle analytic views, making them a strong foundation for geographically distributed user communities.

The relational representation is easily created using CREATE VIEW. Remember to include the FACT ROWS keywords.

Learn More

An In-depth tutorial on this topic is available on Oracle LiveSQL: Query Analytic Views with Relational GROUP BY Pattern SELECT Statements.

For a wide variety of analytic view-related resources, please visit my collateral library. Here, you will find presentations, links to posts, sample applications, and links to other Oracle Live SQL tutorials.


 

William Endress

Autonomous Database Product Management

William (Bud) Endress a member of the Oracle Autonomous Database and SQL product management team, responsible for analytic views, data loading in Oracle Autonomous Database, in-memory aggregation (a feature of Oracle Database In-Memory), and the OLAP option to Oracle Database.


Previous Post

Essbase 21.5.4 Marketplace and Independent Deployment is live

Tanya Heise | 1 min read

Next Post


Sharing Data from On-Premise Oracle Databases

Alexey Filanovskiy | 4 min read
Everything you need to know about data warehousing with the world's leading cloud solution provider