Analytic View Functions

The class Adp.Analytics provides the syntax and descriptions of the classes, methods, attributes, and parameters of the application programming interface to Analytic Views tool of Data Studio Suite of tools. If the requested analytic view does not exists (except create), all functions return message Analytic view does not exist.

List of Analytic View Names Procedure

Returns list of Analytic View names.

Analytics.get_list(owner)

Parameters:

owner: This field displays the owner of the Database Link. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can the list of Analytic Views:

Analytics.get_list()
 
{
  "nodes": [
    {
      "label": "ANALYTIC_VIEW1",
      "type": "ANALYTIC_VIEW",
      "id": "\"ADMIN\".\"DB\".\"ANALYTIC_VIEW1\"",
      "data": {
        "name": "ANALYTIC_VIEW1",
        "namespace": "DB",
        "path": "\"DB\".\"ANALYTIC_VIEW1\"",
        "schema": "ADMIN",
        "classifications": {
          "CAPTION": "Analytic View1",
          "DESCRIPTION": "Analytic View1"
        },
        "application": "DATABASE",
        "created": "2021-06-09T14:19:06Z",
        "updated": "2021-06-15T10:23:07Z"
      }
    },...
    ]
}

Create the Analytic View Procedure

Create the Analytic View based on list of dimension tables and list of measures.

Syntax

Analytics.create(fact_table, dimensions, measures, av_name,
                        owner)

Parameters:

fact_table: The name of fact table.
dimensions is the list of dimensions table names. If dimension table has no joins with any keys in fact table, throws the error.
measures: This field specifies the list of the fact table columns that will be used for aggregation.
av_name: This field is an Analytic View name, if this parameter is missing, use default analytic view name based on name of the fact table: "<fact_table>_AV".
owner: This field displays the owner of the Analytic View. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can create the Analytic View based on four dimensions and two measures:

tables = ["CHANNELS","PRODUCTS","PROMOTIONS","TIMES"]
measures = [ 'UNIT_COST', 'UNIT_PRICE']
adp.Analytics.create('COSTS', tables, measures)
 
// Output
 
success

Create the Analytic View Automatically Procedure

Create the Analytic View based on fact table. Measures are selected automatically based on the columns of the fact table. Dimensions are selected from suitable dimensions tables.

The function uses default analytic view name based on name of the fact table: "<fact_table>_AV".

Syntax

Analytics.create_auto(fact_table, skip_dimensions,
                      owner)

Parameters:

fact_table: The name of fact table.
skip_dimensions: If the field value is True, columns of the fact table are selected as dimensions. The default value is False.
owner: This field displays the owner of the Analytic View. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can create the Analytic View:

Analytics.create_auto('COSTS', False) // Output success

Delete the Analytic view Procedure

Delete the Analytic view with specified name

Syntax

Analytics.drop(model_name, delete_objects)

Parameters:

model_name: The name of the Analytic View.
delete_objects: If the field value is True, all related objects (dimensions and measures) will be dropped. The default value is False.

Example

In this example, you can drop the Analytic View:

adp.Analytics.drop('COSTS_AV', True)

Compile the Analytic View Procedure

Compile the Analytic View. It optimizes the query to efficiently retrieve data from the fact tables.

Syntax

Analytics.compile(av_name, owner)

Parameters:

av_name: The name of the Analytic View.
owner: This field displays the owner of the Analytic View. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can compile the Analytic View:

adp.Analytics.compile('COSTS_AV')

Get Measures List Procedure

Get the list of measures from the specified Analytic View.

Syntax

Analytics.get_measures_list(av_name, owner)

Parameters:

av_name: The name of the Analytic View.
owner: This field displays the owner of the Analytic View. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can get measure list of the Analytic View:

adp.Analytics.get_measures_list('COSTS_AV')
 
// Output
 
{
  "nodes": [
    {
      "label": "UNIT_COST",
      "type": "MEASURE",
      "id": "\"ADMIN\".\"DB\".\"COSTS_AV\".\"MEASURE\".\"UNIT_COST\"",
      "data": {
        "name": "UNIT_COST",
        "namespace": "MEASURE",
        "path": "\"DB\".\"COSTS_AV\".\"MEASURE\".\"UNIT_COST\"",
        "schema": "ADMIN",
        "application": "DATABASE",
        "created": "2022-08-16T17:04:08Z",
        "updated": "2022-08-16T17:04:08Z"
      }
    },...
    ]
}

Get Preview Procedure

Return the metadata of the Analytic View (hierarchies (name and caption) and measures), aggregated data, and sql query for selecting data from the Analytic View.

Syntax

Analytics.get_data_preview(entity_name, owner)

Parameters:

entity_name: The name of the Analytic View.
owner: This field displays the owner of the Analytic View. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can get preview data of the Analytic View:

adp.Analytics.get_data_preview('COSTS_AV')
 
// Output
 
{
  "av": "COSTS_AV",
  "caption": null,
  "description": null,
  "metadata": {
    "hierarchies": [
      {
        "name": "CHANNEL_ID",
        "caption": "CHANNEL_ID"
      },
      {
        "name": "PROD_ID",
        "caption": "PROD_ID"
      }
    ],
    "measures": [
      {
        "name": "UNIT_COST",
        "caption": "UNIT_COST"
      },
      {
        "name": "UNIT_PRICE",
        "caption": "UNIT_PRICE"
      }
    ]
  },
  "data": [...],
  "SQL": "..."
}

Get Metadata Procedure

Return detailed metadata of the Analytic View.

Syntax

Analytics.get_metadata(av_name, owner)

Parameters:

av_name: The name of the Analytic View.
owner: This field displays the owner of the Analytic View. If this field is missing, the tool uses the current schema owner.

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

Example

In this example, you can get metadata of the Analytic View:

adp.Analytics.get_metadata('COSTS_AV')

Dimension Names Procedure

Returns array of dimension names of the Analytic View

Syntax

Analytics.get_dimension_names(av_name)

Parameters:

av_name: The name of the Analytic View.

Example

In this example, you can get dimension names of the Analytic View:

adp.Analytics.get_dimension_names('COSTS_AV')

Fact Table Name Procedure

Returns the name of the fact table of the Analytic View.

Syntax

Analytics.get_fact_table_name(av_name)

Parameters:

av_name: the name of the Analytic View.

Example

In this example, you can get name of the fact table of the Analytic View:

adp.Analytics.get_fact_table_name('COSTS_AV')

Get Error Classes From Dimensions Procedure

Return the information about errors in specified dimension during analyzing the Analytic View. This method is used for checking that the dimension of the Analytic View has no errors. In the case of error ERROR_COUNT is not 0, and errorData is not empty, and contains ERROR_MESSAGE (Text representation of the error), ERROR_NUMBER(Oracle error code).

Syntax

Analytics.get_error_classes_from_dim(av_name,
                      dimension)

Parameters:

av_name: the name of the Analytic View.
dimension: dimension name.

Example

In this example, you can get error classes name of the dimensions of the Analytic View:

adp.Analytics.get_error_classes_from_dim('COSTS_AV', 'CHANNELS')

Get Error Classes From Fact Table Procedure

Return the information about errors in specified fact table during analyzing the Analytic View. This method is used for checking that the fact table of the Analytic View has no errors. In the case of error ERROR_COUNT is not 0, and errorData is not empty, and contains ERROR_MESSAGE (Text representation of the error) and ERROR_NUMBER (Oracle error code).

Syntax

Analytics.get_error_classes_from_fact_tab(av_name,
                      fact_tab)

Parameters:

av_name: The name of the Analytic View.
fact_table: The name of fact table.

Example

In this example, you can get error classes of the name of the fact table of the Analytic View:

adp.Analytics.get_error_classes_from_fact_tab('COSTS_AV', 'COSTS')

Return the information about errors in specified fact table during analyzing the Analytic View. This method is used for checking that the fact table of the Analytic View has no errors. In the case of error ERROR_COUNT is not 0, and errorData is not empty, and contains ERROR_MESSAGE (Text representation of the error) and ERROR_NUMBER (Oracle error code).

Quality Report Procedure

Return the information about errors in analytic view specified by av_name. This method checks fact table and dimensions of the Analytic View. In the case of error text representation of the errors is included into report.

Syntax

Analytics.quality_report(av_name)

Parameters:

av_name: The name of the Analytic View.

Example

In this example, you can get quality report of the Analytic View:

adp.Analytics.quality_report('COSTS_AV')
 
// Output
 
[
    "Fact table COSTS has no errors",
    "Dimension COSTS_AV_CHANNELS_AD has no errors",
    "Dimension COSTS_AV_PRODUCTS_AD has no errors",
    "Dimension COSTS_AV_PROMOTIONS_AD has no errors",
    "Dimension COSTS_AV_TIMES_AD has no errors"
]

Get Data Procedure

Returns the aggregated data from the Analytic View. The return value is the list of query results.

Syntax

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

Parameters:

levels: The level of the Analytic View.
column_names: The array of columns.
entity_name: The name of the Analytic View.
hierarchies: The array of selected hierarchies.
measures: The array of measures.
where_condition: is the array of conditions, each condition has 4 fields: hierarchy, column, operator, and value.
owner: This field displays the owner of the Analytic View. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can get quality report of the Analytic View:

adp.Analytics.get_data(True,
    ["COSTS_AV_PROD_HIER_PROD_ID_ATTR",
     "UNIT_COST",
     "COSTS_AV_PROD_HIER_DEPTH"], 'COSTS_AV',
     ["COSTS_AV_PROD_ID_HIER"],
     ["UNIT_COST", "UNIT_PRICE"],
    [{"hierarchy": "COSTS_AV_PROD_ID_HIER", "column": "LEVEL_NAME", "operator": "=", "value": "ALL:PROD_ID"}])
 
//Output
 
[{"UNIT_COST":808685.68},{"UNIT_COST":213170.06},{"UNIT_COST":16778.02},...},

SQL Query Procedure

Return SQL Query that is used in get_data.

Syntax

Analytics.get_sql(levels, column_names, entity_name, hierarchies,
                          measures, where_condition, owner)

Parameters:

levels: The level of the Analytic View.
column_names: The array of columns.
entity_name: The name of the Analytic View.
hierarchies: The array of selected hierarchies.
measures: The array of measures.
where_condition: is the array of conditions, each condition has 4 fields: hierarchy, column, operator, and value.
owner: This field displays the owner of the Analytic View. If this field is missing, the tool uses the current schema owner.

Example

In this example, you can get SQL for aggregated data of the Analytic View:

adp.Analytics.get_sql(True,
    ["COSTS_AV_PROD_HIER_PROD_ID_ATTR",
     "UNIT_COST",
     "COSTS_AV_PROD_HIER_DEPTH"], 'COSTS_AV',
     ["COSTS_AV_PROD_ID_HIER"],
     ["UNIT_COST", "UNIT_PRICE"],
    [{"hierarchy": "COSTS_AV_PROD_ID_HIER", "column": "LEVEL_NAME", "operator": "=", "value": "ALL:PROD_ID"})
 
//Output
 
SELECT
  "MEASURES"."UNIT_COST" AS "UNIT_COST"
FROM "ADMIN"."COSTS_AV" HIERARCHIES(
    "PROD_ID"."PROD_ID")
WHERE
  (
    (
      "PROD_ID"."PROD_ID"."LEVEL_NAME" IN ('ALL', 'PROD_ID')
    )
  )