Understanding the Details of the Analytical View

It is useful to be able to quickly understand the details of the analytical view, and its SQL definition.

Consider the SQL form of the analytical views that we built in the earlier steps.

Clicking the Detail link opens the AV Details window modal, that shows all the information about the analytical view, including Score Detail, Base Table Info, AView Info, and Aview SQL.

Please note that the SQL expression of the analytical view cannot be changed. Instead, you must create a new analytical view with an updated definition.

  1. To examine the details of analytical view geo.national_geo_features_av_1, click Details.

    Viewing the SQL definition of the analytical view
    Viewing the SQL Definition of the Analytical View
  2. The first time you examine the analytical view, you may have to run statistics to determine an accurate rating.

    In the AV Details window modal, under the Score Detail tab, click Run Compute Statistics Once.

    Running initial Statistical Computation on Analytical View
    Running initial Statistical Computation on Analytical View
  3. After statistical computations complete, you can examine the components of AV details:

    Running initial Statistical Computation on Analytical View
    Running initial Statistical Computation on Analytical View
  4. Similarly, look at the SQL statement that represents the second analytical view, geo.national_geo_features_av_1, by clicking Show SQL.

    Viewing the SQL definition of the analytical view
    Viewing the SQL Definition of the Analytical View
  5. Consider and compare the two statements:

    geo.geo_features_lv_av_1 is an analytical view created over a logical view

    SELECT TA_0.feature_class Feature_Class, TA_0.state_alpha State_Alpha, sum_no_finalize(1) Record_Count FROM geo.geo_features_lv TA_0 GROUP BY 1, 2

    geo.national_geo_features_av_1 is an analytical view created over a base table

    SELECT TA_0.feature_class Feature_Class, TA_0.county_name County_Name, sum_no_finalize(1) Record_Count, TA_0.state_alpha State_Alpha FROM geo.national_geo_features TA_0 GROUP BY 1, 2, 4
    • TA_0 is the automatically generated alias for the base table or logical view. It is not the same entity in these two statements.
    • sum_no_finalize(1) is the function that returns record count.

Score Detail

The Score Detail explains the rating of the analytical view:

Aview Advantages
  • File format conversion

    Analytical views are always in parquet format. If original data is not in parquet format, then converting to an analytical view applies parquet's default compression techniques.

  • Join materialization

    Join computations are cached, eliminating the need to perform join computations after query routing.

  • Data reduction

    Aggregation functions can result in data reduction. Analytical views cache the results after aggregation, eliminating to perform these computations after query routing.

  • Incrementally refreshable

    If analytical views are partitioned, they can be incrementally refreshed. Only the partitions containing stale data will be refreshed, leaving the valid partitions untouched.

Score breakdown

The following elements indicate how Arcadia Engine determines the score of the analytical view:

  • Max possible score

    This number is 5.

  • Deductions / No Deductions

    This number is subtracted from the maximum score.

  • Total Score

    This number represents the final rating after applying deductions.

Analytical View Details: Score Detail

Base Table Info

This tab details the information about the base table of the analytical view.

Table Name
The name of the table appears in the form database_name.table_name
File Format
Format of the table, such as TEXT, PARQUET, or KUDU
Row Count
Number of rows in the table
Row Size
Typical size of each row, in Bytes
In Memory Size
The size of the table when stored in memory, in Bytes
On Disk Size
The size of the table when stored on disk, in Bytes
Partitions
The names of the columns that partition the table; can be none or several
Analytical View Details: Base Table Info

AView Info

This tab lists the information about the analytical view.

Aview Name
The name of the analytical view appears in the form database_name.aview_name
File Format
Format of the analytical view, such as PARQUET
Row Count
Number of rows in the analytical view; on recommended analytical views, this number is an estimation
Row Size
Typical size of each row, in Bytes
In Memory Size
The size of the analytical view when stored in memory, in Bytes; on recommended analytical views, this number is an estimation
On Disk Size
The size of the analytical view when stored on disk, in Bytes; on recommended analytical views, this number is not available, so we report is as To Be Determined (TBD)
Partitions
The names of the columns that partition the analytical view; can be none or several
Analytical View Details: Aview Info

AView SQL

This tab provides the definition of the analytical view.

Aview Name
The name of the analytical view appears in the form database_name.aview_name
Measures
Measures used in analytical view; these are typically aggregations, such as sum(1)
Dimension
Dimensions used in analytical view; dimensions that match a partitioning column have a green key icon.
SQL
The SQL SELECT statement that defines the analytical view
Analytical View Details: AView SQL