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
• aem_meta_source_database
• aem_meta_target_database
• aem_server
• aem_source_database
• aem_target_database
• aem_target_processes
• aem_task
• aem_task_name
• aem_task_previous_metrics
• aem_task_profile
• aem_task_state
• aem_task_stop_reason

aem_endpoint_type

The aem_endpoint_type table provides lookup information about the Replicate endpoints.

aem_meta_source_database

The aem_meta_source_database table provides lookup information about the source endpoint.

aem_meta_target_database

The aem_meta_target_database table provides lookup information about the target endpoint.

aem_server

The aem_server table provides lookup information about the Replicate server.

aem_source_database

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

aem_target_database

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

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.

aem_task_name

The aem_task_name table provides lookup information about 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.

aem_task_state

The aem_task_state table provides lookup information about the task state.

aem_task_stop_reason

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

Sample Queries

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

Query 1:

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

Query 2:

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
	

Query 3:

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"