Skip to main content Skip to complementary content

Analytics repository schema

In addition to the data provided by the Enterprise Manager Analytics dashboards, organizations can also use their own BI tools to generate reports based on the available metrics. To assist with this, the following topic provides a description of the Analytics repository schema together with a couple of sample queries.

The Analytics repository contains the following tables:

aem_endpoint_type

The aem_endpoint_type table provides lookup information about the Replicate endpoints.

Available table columns
Column Data Type Description

endpoint_type_id

Integer

The endpoint type ID.

endpoint_type_name

Character varying(1024)

The endpoint type.

aem_meta_source_database

The aem_meta_source_database table provides lookup information about the source endpoint.

Available table columns
Column Data Type Description

meta_source_database_id

Integer

The source endpoint ID.

meta_source_database_name

Character varying(1024)

The source endpoint name.

endpoint_type_id

Integer

The endpoint type ID.

aem_meta_target_database

The aem_meta_target_database table provides lookup information about the target endpoint.

Available table columns
Column Data Type Description

meta_target_database_id

Integer

Target Endpoint ID

meta_target_database_name

Character varying(1024)

Target Endpoint Name

endpoint_type_id

Integer

Endpoint Type ID

aem_server

The aem_server table provides lookup information about the Replicate server.

Available table columns
Column Data Type Description

server_id

Integer

The Replicate server ID.

server_name

Character varying(1024)

The Replicate server name.

Host

Character varying(1024)

The host name or IP address.

port

Integer

The Replicate port.

platform

Character varying(1024)

The Replicate server platform.

version

Character varying(1024)

The Replicate version.

aem_source_database

The aem_source_database table provides information about the source endpoint used in a specific task.

Available table columns
Column Data Type Description

task_id

Integer

The task run ID.

source_database_id

Integer

The source endpoint ID.

source_database_name

Character varying(1024)

The source endpoint name.

endpoint_type_id

Integer

The endpoint Type ID.

aem_target_database

The aem_target_database table provides information about the target endpoint used in a specific task.

Available table columns
Column Data Type Description

task_id

Integer

The task run ID.

target_database_id

Integer

The target endpoint ID.

target_database_name

Character varying(1024)

The target endpoint name.

endpoint_type_id

Integer

The target endpoint type ID.

aem_target_processes

The aem_target_processes table is used internally to monitor the Enterprise Manager Analytics processes such as the Collector and the Purger.

aem_task

This is a fact table for each run or instance of a Replicate task.

Available table columns
Column Data Type Description

task_id

Integer

The task run ID.

target_database_id

Integer

The target endpoint ID.

target_database_name

Character varying(1024)

Target endpoint name.

endpoint_type_id

Integer

The endpoint type ID.

retrieval_time

Timestamp without time zone

The time that the information about the task was retrieved.

server_id

Integer

The Replicate server ID.

task_name_id

Integer

The task name ID.

task_state_id

Integer

The task state ID.

task_stop_reason_id

Integer

The task stop Reason ID.

task_profile_id

Integer

The task profile ID.

cdc_evt_applied_insert_count

Integer

The number of INSERTs since the last retrieval time or since the task was started.

cdc_evt_applied_update_count

Integer

The number of UPDATEs since the last retrieval time or since the task was started.

cdc_evt_applied_delete_count

Integer

The number of DELETEs since the last retrieval time or since the task was started.

cdc_evt_applied_ddl_count

Integer

The number of DDLs since the last retrieval time or since the task was started.

full_load_tables_completed_count

Integer

The number of completed tables loaded to the Target at retrieval time.

full_load_tables_loading_count

Integer

The number of tables being loaded to the target at retrieval time.

full_load_tables_queued_count

Integer

The number of tables waiting to be loaded to the target at retrieval time.

full_load_tables_with_error_count

Integer

The number of tables that could not be loaded to the target at retrieval time due to an error.

full_load_total_records_transferred

Integer

The total number of records that have completed loading to the target at retrieval time.

full_load_est_records_count_for_all_tables

Integer

The estimated number of records to be loaded to the target.

full_load_completed

Integer

Indicates if the Full Load has completed.

full_load_start

Timestamp without time zone

The start time of the Full Load.

full_load_finish

Timestamp without time zone

The finish time of the Full Load.

full_load_thrput_src_thrput_records_count

Integer

The Full Load source throughput (in rec/sec) at retrieval time.

full_load_thrput_src_thrput_volume

Integer

The Full Load source throughput (in kb/sec) at retrieval time.

full_load_thrput_trg_thrput_records_count

Integer

The Full Load target throughput (in rec/sec) at retrieval time.

full_load_thrput_trg_thrput_volume

Integer

The Full Load target throughput (in kb/sec) at retrieval time.

cdc_thrput_src_thrput_records_count

Integer

The Change Processing source throughput (in rec/sec) at retrieval time.

cdc_thrput_src_thrput_volume

Integer

The Change Processing source throughput (in kb/sec) at retrieval time.

cdc_thrput_trg_thrput_records_count

Integer

The Change Processing target throughput (in rec/sec) at retrieval time.

cdc_thrput_trg_thrput_volume

Integer

The Change Processing target throughput (in kb/sec) at retrieval time

cdc_trans_read_rollback_count

Integer

The number of ROLLBACK transactions since the last retrieval time or since the task was started.

cdc_trans_read_records_rollback_count

Integer

The number of ROLLBACK change records since the last retrieval time or since the task was started.

cdc_trans_rollback_change_volume

Integer

The volume of ROLLBACK changes (in bytes).

cdc_trans_applied_transactions_in_progress_count

Integer

The number of transactions in progress at retrieval time.

cdc_trans_applied_records_in_progress_count

Integer

The number of records for all transactions in progress at retrieval time.

cdc_trans_applied_comitted_transaction_count

Integer

The number of transactions committed since the last retrieval time or since the task was started.

cdc_trans_applied_records_comitted_count

Integer

The number of records for all committed transactions since the last retrieval time or since the task was started.

cdc_trans_applied_volume_comitted

Integer

The volume of change for all committed transactions (in bytes) since the last retrieval time or since the task was started.

cdc_trans_read_memory_events_count

Integer

The number of changes accumulated in memory until source commit at retrieval time.

cdc_trans_read_swapped_events_count

Integer

The number of changes accumulated on disk until source commit at retrieval time.

cdc_trans_applied_memory_events_count

Integer

The number of changes in memory during apply and until target commit at retrieval time

cdc_trans_applied_swap_events_count

Integer

The number of changes on disk during apply and until target commit at retrieval time.

cdc_source_latency

Integer

The average time gap between the original change in the source endpoint and capturing it (in seconds) at retrieval time.

cdc_apply_latency

Integer

The overall latency (in seconds) at retrieval time.

memory_usage_kb

Integer

The memory usage for the task (in kilobytes) at retrieval time.

disk_usage_kb

Integer

The utilization of disk space for the task (in kilobytes) at retrieval time.

cpu_percentage

Integer

The CPU consumption of the task (as a percentage of server CPU with a value of 0-100) at retrieval time.

data_error_count

Integer

The total number of data errors at retrieval time for all tables involved in a task.

task_option_full_load_enabled

Integer

Indicates if Full Load is enabled.

task_option_apply_changes_enabled

Integer

Indicates if Apply Changes is enabled.

task_option_store_changes_enabled

Integer

Indicates if Store Changes is enabled.

task_option_audit_changes_enabled

Integer

Indicates if Audit Changes is enabled.

task_option_recovery_enabled

Integer

Indicates if Recovery is enabled.

server_cpu_percentage

Integer

The CPU percentage being utilized by the Replicate server process running on the server machine at retrieval time.

machine_cpu_percentage

Integer

The total CPU percentage being utilized by all processes (i.e. not just Qlik processes) running on the server machine at retrieval time.

tasks_cpu_percentage

Integer

The total CPU percentage being utilized by all Replicate task processes running on the server machine at retrieval time.

aem_task_name

The aem_task_name table provides lookup information about the task name.

Available table columns
Column Data Type Description

server_id

Integer

The Replicate server ID.

task_name_id

Integer

The task name ID.

task_name

Character varying(1024)

The task name.

aem_task_previous_metrics

Used internally to calculate differences between the current run of a task and the previous run.

aem_task_profile

The aem_task_profile table provides lookup information about the task profile.

Available table columns
Column Data Type Description

task_profile_id

Integer

The task profile ID.

name

Character varying(1024)

The task profile name.

description

Character varying(1024)

The task profile description.

aem_task_state

The aem_task_state table provides lookup information about the task state.

Available table columns
Column Data Type Description

task_state_id

Integer

The task state ID.

name

Character varying(1024)

The task state dame.

description

Character varying(1024)

The task state description.

aem_task_stop_reason

The aem_task_stop_reason table provides lookup information about the reason that a task stopped.

Available table columns
Column Data Type Description

task_stop_reason_id

Integer

The stop reason ID.

name

Character varying(1024)

The stop reason name.

description

Character varying(1024)

The stop reason description.

Sample Queries

The following sample queries demonstrate how useful information can be extracted from the schema.

Provides a sampling of Full Load and Change Processing metrics for all tasks with a source endpoint named "Teradata DB" for the month of August 2017.

SELECT
  AEM_SERVER.SERVER_NAME AS "Server"
  ,AEM_TASK_NAME.TASK_NAME AS "Task"
  ,AVG(AEM_TASK.MEMORY_USAGE_KB / 1024::float) AS "Avg Memory"
  ,AVG(AEM_TASK.DISK_USAGE_KB) / 1024::float AS "Avg IO"
  ,MAX((CASE WHEN AEM_TASK.FULL_LOAD_TABLES_COMPLETED_COUNT IS NULL THEN 0 ELSE AEM_TASK.FULL_LOAD_TABLES_COMPLETED_COUNT END)+(CASE WHEN AEM_TASK.FULL_LOAD_TABLES_LOADING_COUNT IS NULL THEN 0 ELSE AEM_TASK.FULL_LOAD_TABLES_LOADING_COUNT END)+(CASE WHEN AEM_TASK.FULL_LOAD_TABLES_QUEUED_COUNT IS NULL THEN 0 ELSE AEM_TASK.FULL_LOAD_TABLES_QUEUED_COUNT END)+(CASE WHEN AEM_TASK.FULL_LOAD_TABLES_WITH_ERROR_COUNT IS NULL THEN 0 ELSE AEM_TASK.FULL_LOAD_TABLES_WITH_ERROR_COUNT END)) AS "Full Load Total Tables"
  ,MAX(FULL_LOAD_TOTAL_RECORDS_TRANSFERRED) AS "Full Load Total Records"
  ,MAX(AEM_TASK.FULL_LOAD_FINISH-AEM_TASK.FULL_LOAD_START) AS "Full Load Max Load Duration"
  ,AVG(AEM_TASK.FULL_LOAD_FINISH-AEM_TASK.FULL_LOAD_START) AS "Full Load Avg Load Duration"
  ,AVG((CASE WHEN AEM_TASK.FULL_LOAD_THRPUT_TRG_THRPUT_RECORDS_COUNT IS NULL THEN 0 ELSE AEM_TASK.FULL_LOAD_THRPUT_TRG_THRPUT_RECORDS_COUNT END)) AS "Full Load Avg Target Throughput Records per Second"
  ,SUM((CASE WHEN AEM_TASK.CDC_EVT_APPLIED_INSERT_COUNT IS NULL THEN 0 ELSE AEM_TASK.CDC_EVT_APPLIED_INSERT_COUNT END)+(CASE WHEN AEM_TASK.CDC_EVT_APPLIED_UPDATE_COUNT IS NULL THEN 0 ELSE AEM_TASK.CDC_EVT_APPLIED_UPDATE_COUNT END)+(CASE WHEN AEM_TASK.CDC_EVT_APPLIED_DELETE_COUNT IS NULL THEN 0 ELSE AEM_TASK.CDC_EVT_APPLIED_DELETE_COUNT END)+(CASE WHEN AEM_TASK.CDC_EVT_APPLIED_DDL_COUNT IS NULL THEN 0 ELSE AEM_TASK.CDC_EVT_APPLIED_DDL_COUNT END)) AS "CDC Total Applied Changes"
  ,SUM((CASE WHEN AEM_TASK.CDC_TRANS_APPLIED_COMITTED_TRANSACTION_COUNT IS NULL THEN 0 ELSE AEM_TASK.CDC_TRANS_APPLIED_COMITTED_TRANSACTION_COUNT END)) AS "CDC Total Applied Transactions"
  ,AVG((CASE WHEN AEM_TASK.CDC_THRPUT_TRG_THRPUT_RECORDS_COUNT IS NULL THEN 0 ELSE AEM_TASK.CDC_THRPUT_TRG_THRPUT_RECORDS_COUNT END)) AS "CDC Avg Target Throughput Records per Second"
  ,AVG((CASE WHEN AEM_TASK.CDC_APPLY_LATENCY IS NULL THEN 0 ELSE AEM_TASK.CDC_APPLY_LATENCY END)) AS "CDC Avg Apply Latency"
FROM
  AEM_TASK
  INNER JOIN AEM_SERVER ON (AEM_TASK.SERVER_ID = AEM_SERVER.SERVER_ID)
  INNER JOIN AEM_TASK_NAME ON (AEM_TASK.TASK_NAME_ID = AEM_TASK_NAME.TASK_NAME_ID)
  INNER JOIN AEM_SOURCE_DATABASE ON (AEM_TASK.ID = AEM_SOURCE_DATABASE.TASK_ID)
WHERE
    (AEM_TASK.RETRIEVAL_TIME >= TIMESTAMP WITH TIME ZONE '2017-08-01 00:00:00.00000+00')
    AND (AEM_TASK.RETRIEVAL_TIME < TIMESTAMP WITH TIME ZONE '2017-09-01 00:00:00.00000+00')
	AND (AEM_SOURCE_DATABASE.SOURCE_DATABASE_NAME = 'Teradata DB')
GROUP BY
  AEM_SERVER.SERVER_NAME
  ,AEM_TASK_NAME.TASK_NAME

Provides a trend throughout the day of the number of Completed, Queued, Loading and Error tables related to Full Load for the month of August 2017 and for a Replicate server named "rep-server1-prod".

SELECT
  SUM((CASE WHEN AEM_TASK.FULL_LOAD_TABLES_COMPLETED_COUNT IS NULL THEN 0 ELSE AEM_TASK.FULL_LOAD_TABLES_COMPLETED_COUNT END)) AS "Full Load Tables Completed"
  ,SUM((CASE WHEN AEM_TASK.FULL_LOAD_TABLES_LOADING_COUNT IS NULL THEN 0 ELSE AEM_TASK.FULL_LOAD_TABLES_LOADING_COUNT END)) AS "Full Load Tables Loading"
  ,SUM((CASE WHEN AEM_TASK.FULL_LOAD_TABLES_QUEUED_COUNT IS NULL THEN 0 ELSE AEM_TASK.FULL_LOAD_TABLES_QUEUED_COUNT END)) AS "Full Load Tables Queued"
  ,SUM((CASE WHEN AEM_TASK.FULL_LOAD_TABLES_WITH_ERROR_COUNT IS NULL THEN 0 ELSE AEM_TASK.FULL_LOAD_TABLES_WITH_ERROR_COUNT END)) AS "Full Load Tables Error"
  ,AEM_TASK.RETRIEVAL_TIME AS "Date and Time"
FROM
  AEM_TASK
  INNER JOIN AEM_SERVER ON (AEM_TASK.SERVER_ID = AEM_SERVER.SERVER_ID)
WHERE
    (AEM_TASK.RETRIEVAL_TIME >= TIMESTAMP WITH TIME ZONE '2017-08-01 00:00:00.00000+00')
    AND (AEM_TASK.RETRIEVAL_TIME < TIMESTAMP WITH TIME ZONE '2017-09-01 00:00:00.00000+00')
    AND (AEM_SERVER.SERVER_NAME IN ('rep-server1-prod'))
GROUP BY
    AEM_TASK.RETRIEVAL_TIME
ORDER BY
    "Date and Time" ASC
	

Provides an daily trend of Average Throughput and Latency metrics related to Change Processing for the month of August 2017 and for a Replicate server named "rep-server1-prod".

SELECT
  AVG((CASE WHEN AEM_TASK.CDC_THRPUT_SRC_THRPUT_RECORDS_COUNT IS NULL THEN 0 ELSE AEM_TASK.CDC_THRPUT_SRC_THRPUT_RECORDS_COUNT END)) AS "CDC Avg Source Throughput Records"
  ,AVG((CASE WHEN AEM_TASK.CDC_THRPUT_TRG_THRPUT_RECORDS_COUNT IS NULL THEN 0 ELSE AEM_TASK.CDC_THRPUT_TRG_THRPUT_RECORDS_COUNT END)) AS "CDC Avg Target Throughput Records"
  ,AVG((CASE WHEN AEM_TASK.CDC_SOURCE_LATENCY IS NULL THEN 0 ELSE AEM_TASK.CDC_SOURCE_LATENCY END)) AS "CDC Avg Source Latency"
  ,AVG((CASE WHEN AEM_TASK.CDC_APPLY_LATENCY IS NULL THEN 0 ELSE AEM_TASK.CDC_APPLY_LATENCY END)) AS "CDC Avg Apply Latency"
  ,EXTRACT(YEAR from AEM_TASK.RETRIEVAL_TIME) AS "Year"
  ,EXTRACT(MONTH from AEM_TASK.RETRIEVAL_TIME) AS "Month"
  ,EXTRACT(DAY from AEM_TASK.RETRIEVAL_TIME) AS "Day"
FROM
  AEM_TASK
  INNER JOIN AEM_SERVER ON (AEM_TASK.SERVER_ID = AEM_SERVER.SERVER_ID)
WHERE
    (AEM_TASK.RETRIEVAL_TIME >= TIMESTAMP WITH TIME ZONE '2017-08-01 00:00:00.00000+00')
    AND (AEM_TASK.RETRIEVAL_TIME < TIMESTAMP WITH TIME ZONE '2017-09-01 00:00:00.00000+00')
    AND (AEM_SERVER.SERVER_NAME IN ('rep-server1-prod'))
GROUP BY
  EXTRACT(YEAR from AEM_TASK.RETRIEVAL_TIME)
  ,EXTRACT(MONTH from AEM_TASK.RETRIEVAL_TIME)
  ,EXTRACT(DAY from AEM_TASK.RETRIEVAL_TIME)
ORDER BY 
  "Year", "Month", "Day"
	

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!