1. Using Guide for Oracle Cloud
  2. Data Studio
  3. Python ORDS API for Oracle Machine Learning
  4. Working with Python ORDS API for OML
  5. Analytic View Functions

Analytic View Functions

The class Adp.Analytics is used for working with analytic views. If the requested analytic view does not exists (except create), all functions return message Analytic view does not exist

Analytics.get_list(owner=None)

This returns list of Analytic View names, if owner is missing or set to None, use the current schema.

Analytics.create(fact_table, skip_dimensions=False, owner=None)

This creates the Analytic View based on the fact table. Measures are selected automatically based on the columns of the fact table in the schema of the owner (if owner is missing or set to None, use the current schema). Dimensions are selected from suitable dimensions tables, if skip_dimensions is True, columns of the fact table are selected as dimensions.

Analytics.drop(model_name, delete_objects=False)

This deletes the Analytic view.

Analytics.compile(av_name, owner=None)

This compiles the Analytic View in the schema of the owner (if owner is missing or set to None, use the current schema).

Analytics.get_measures_list(av_name, owner=None)

This returns the metadata of all measures of the Analytic View in the schema of owner (if owner is missing or set to None, use the current schema).

Analytics.get_entity_ddl(entity_type, entity_name, owner=None)

This generates DDL for creating or replacing entity in the schema of owner (if owner is missing or set to None, use the current schema). entity_type can be 'TABLE', 'VIEW', or 'ANALYTIC_VIEW'.

Analytics.get_data_preview(entity_name, owner=None)

This returns metadata of the Analytic View (hierarchies (name and caption) and measures), aggregated data, and sql query for selecting data from the Analytic View in the schema of the owner (if owner is missing or set to None, use the current schema).

Analytics.get_metadata(av_name, owner=None)

This returns detailed metadata of the Analytic View in the schema of the owner (if owner is missing or set to None, use the current schema).

Analytics.get_quality_report(av_name, hierarchies, owner=None)

This returns quality report for the Analytic View in the schema of the owner (if owner is missing or set to None, use the current schema).

Analytics.get_dimension_names(av_name)

This returns array of dimension names of the Analytic View.

Analytics.get_fact_table_name(av_name)

This returns the name of the fact table of the Analytic View.

Analytics.get_error_classes_from_dim(av_name, dimension)

This returns the information about errors in specified dimension during analyzing the Analytic View.

Analytics.get_error_classes_from_fact_tab(av_name, fact_tab)

This returns the information about errors in the specified fact table while analyzing the Analytic View.

Analytics.get_fact_tab_error_details(av_name, fact_tab, err_class, err_col, rec_count=-1)

This returns a detailed explanation of the error in the fact table, err_class is taken from the response of get_error_classes_from_fact_tab. 

Analytics.get_dim_error_details(av_name, dimension, err_class, rec_count=-1)

This returns a detailed explanation of the error in the dimension, err_class is taken from the response of get_error_classes_from_dim. 

Analytics.get_data(levels, column_names, entity_name, hierarchies, measures, where_condition, owner=None)

This returns the aggregated data from the Analytic View in the schema of the owner (if owner is missing or set to None, use the current schema).

Here are the parameters and their descriptions:

column_names: This is the array of columns.

entity_name: This is the name of the Analytic View.

hierarchies: This is the array of selected hierarchies.

measures: This is the array of measures

where_condition: This is the array of conditions. Each condition has 4 fields: hierarchy, column, operator, and value. The return value is the list of query results, each item in the array has the same column as in column_names with its value 

Analytics.getSQL(levels, columnN_names, entity_name, hierarchies, measures, where_condition, owner=None)

This returns SQL Query that is used in get_analytic_view_data. All parameters are the same as in get_analytic_view_data.

Examples of Analytics

This section provides the following examples to help you get started using the Analytics API:

  • Create the Analytic View
  • Drop the Analytic View
  • Data Quality
  • Get Dimension Names
  • Get the Analytic View Metadata
  • Get the Analytic View Data

Before using these examples, you must create the ADP instance and connect to the ORDS.

import adp
ords = adp.login('<protocol://host:port>', 'schema_name', 'schema_password')

Create the Analytic View

ords.Analytics.create('SALES')

Output:
'success'

Drop the Analytic View

ords.Analytics.drop('SALES_AV')

Data Quality

av_name = "SALES_AV"
# Get fact table
text = ords.Analytics.get_fact_table_name(av_name)
j = json.loads(text)
fact_table = j["FACT_TABLE_NAME"]
  
#Get dimensions
text = ords.Analytics.get_dimension_names(av_name)
j = json.loads(text)
 
dims = []
for dim in j:
    dims.append(dim['DIMENSION_NAME'])
 
# Check error count in fact table
text = ords.Analytics.get_error_classes_from_fact_tab(av_name, fact_table)
j = json.loads(text)
count = j[0]['ERROR_COUNT']
if count != 0:
    print('There are errors in fact table')
else:
    print('Fact table has no errors')
 
#Check error count in dimensions
dim_error = False
for dim in dims:
    text = ords.Analytics.get_error_classes_from_dim(av_name, dim)
    count = j[0]['ERROR_COUNT']
    if count != 0:
        dim_error = True
        break
 
if dim_error:
    print('There are errors in dimensions')
else:
    print('Dimensions have no errors')
 
Output:
Fact table has no errors
Dimensions have no errors

Get the Analytic View Metadata

ords.get_metadata('SALES_AV')


Output:

{

  "hierarchies": [

    {

      "name": "SALES_AV_CUST_ID_HIER",

      "alias": "CUST_ID",

      "owner": "ADPTEST",

      "captionColumnName": "SALES_AV_CUST_ID_HIER",

      "orderColumnName": "SALES_AV_CUST_ID_HIER_HIER_ORDER",

      "caption": "CUST_ID",

      "levels": [

        {

          "keys": [

            {

              "name": "CUST_ID_ATTR",

              "columnName": "SALES_AV_CUST_ID_HIER_CUST_ID_ATTR"

            }

          ],

          "altKeys": [],

          "attributes": [],

          "ordering": [

            {

              "name": "CUST_ID_ATTR",

              "direction": "ASC",

              "nulls": "FIRST",

              "columnName": "SALES_AV_CUST_ID_HIER_CUST_ID_ATTR"

            }

          ],

          "name": "CUST_ID",

          "caption": "CUST_ID"

        }

      ]

    },

    ...

    ],

    "measures": [

        {

            "name": "QUANTITY_SOLD",

            "columnName": "QUANTITY_SOLD",

            "dataType": "NUMBER",

            "measureType": "BASE",

            "caption": "QUANTITY_SOLD"

        },

        {

            "name": "AMOUNT_SOLD",

            "columnName": "AMOUNT_SOLD",

            "dataType": "NUMBER",

            "measureType": "BASE",

            "caption": "AMOUNT_SOLD"

        }

    ],

    "av": "SALES_AV",

    "owner": "ADPTEST"

}

Get the Analytic View Data

ords.Analytics.get_data(True,

['SALES_AV_CUST_ID_HIER_CUST_ID_ATTR', 'QUANTITY_SOLD', 'SALES_AV_CUST_ID_HIER_DEPTH'],

'SALES_AV', ['SALES_AV_CUST_ID_HIER'], ['QUANTITY_SOLD'],

[ { 'hierarchy': 'SALES_AV_CUST_ID_HIER', 'column': 'LEVEL_NAME', 'operator': '=', 'value: 'ALL:CUST_ID' } ])



Output:



[

  {

    "SALES_AV_CUST_ID_HIER_CUST_ID_ATTR": 100,

    "SALES_AV_CUST_ID_HIER_DEPTH": 1,

    "QUANTITY_SOLD": 5

  },

  {

    "SALES_AV_CUST_ID_HIER_CUST_ID_ATTR": 200,

    "SALES_AV_CUST_ID_HIER_DEPTH": 1,

    "QUANTITY_SOLD": 4

  },

...

]