Introduction

This document describes the steps and process to analyze the usage of a Denodo Virtual DataPort (VDP) Server to help define the operational and business metrics necessary to monitor and evaluate the Denodo Platform adoption and added value. Also, to help administrators and stakeholders better understand the usage patterns of data consumers and increase the visibility of the Denodo Platform added value to the business.

Prerequisites:

• Infrastructure for the Denodo Monitor logs is ready to be used.
• Monitoring should be enabled from Solution Manager to have a combined view of all the Denodo environments.
• Denodo’s monitoring logs and information must be stored in an external database.

For more detailed information on setting up and using the Denodo Monitor refer to these resources:

• Monitoring Denodo Servers
• Denodo Monitor
• Best Practices for using the Denodo Monitor in Production Environments

Step 1: List Available Sources and Formats

The first step in establishing a robust monitoring framework is identifying and documenting all relevant monitoring sources and the formats in which data is collected. Understanding the nature and structure of this data enables more effective monitoring, alerting, and downstream analysis.

Sources

Identify where monitoring data originates. Common monitoring data sources include:

• Application Logs
  Logs generated by applications, middleware, or runtime environments. These provide insight into application behavior, errors, and performance patterns. In Denodo’s case, the  Denodo Monitor logs serve  as the Application logs.

• For details on the various log files and their columns generated by Denodo Monitor, see: Log column details

• Telemetry Data
  Instrumentation data captured during system operation.
  Examples:
  

• JMX Events (for Java-based applications).  Learn how to monitor Denodo servers using JMX: Monitoring Denodo servers with JMX.
• OpenTelemetry traces, metrics and logs. For integrating Denodo with OpenTelemetry for comprehensive observability: Denodo Observability with OpenTelemetry

Data Formats

Knowing how data is formatted is essential for ingestion, parsing, and visualization.

• Log files
  Unstructured data from traditional log files or diagnostic outputs. The Denodo Monitor logs are captured by default in log files in plain text format. To easily query the Denodo log files see Reading the VDP logs with Denodo Log Custom Wrapper.
• SQL Tables
  Structured, queryable data accessible via SQL queries. The Monitor logs can be stored into a relational database as well. Guidance on externalizing Denodo log files to a database: How to externalize Denodo log files
• OpenTelemetry Protocol (OTLP)
  A vendor-agnostic protocol for transmitting telemetry data (traces, metrics, logs) between services and backend systems.

Step 2: Analysis Platforms

Once data sources and formats are established, the next step in the monitoring framework is identifying the platforms used to analyze, visualize, and derive insights from the data. These tools enable teams to monitor system health, detect anomalies, and make informed operational decisions.

Dashboards

Used for creating real-time and historical visualizations of metrics and KPIs. Dashboards help stakeholders monitor system health and performance at a glance.

Tools that could be included:

• Denodo Dashboard
• Power BI
• Tableau
• Grafana

With the dashboard applications,the Denodo Monitor logs can be ingested using the following approach:

• Create sources pointing to the log files location or the external database in the Virtual DataPort server and connect to the Dashboard via the VDP server to access the logs.

This approach of connecting to monitor logs via Virtual DataPort server is beneficial for applications that support only SQL compatible sources and does not allow connecting to File Systems or Cloud storage.

Log Management and Analysis

Focused on collecting, indexing, searching, and analyzing log data from various sources. These platforms support incident investigation, root cause analysis, and compliance monitoring, such as Splunk and ElasticSearch.

Splunk can be configured to ingest the log data from the machines where the Virtual DataPort Server is running to have data accessible across an organization for identifying data patterns, providing metrics, diagnosing problems, and providing intelligence for business operations.

Data Management Tools

These tools are designed to handle large-scale, high-performance data operations. In a monitoring framework, they play a crucial role in storing, aggregating, and querying structured data such as performance metrics, operational logs, or transactional records. It often supports both real-time analytics and historical trend analysis, enabling teams to generate reports, identify patterns, and support long-term planning.

The Denodo Monitor logs can be stored on relational databases for faster access.

Other Monitoring and Telemetry Tools

Specialized tools that support specific telemetry data types such as metrics, traces, and alerts. These are commonly integrated into observability stacks for deeper insights.

Tools Include:

• Prometheus (metrics monitoring and alerting).
• Jaeger (tracing and distributed system analysis).

Step 3: Monitoring Methodologies (to match scalability requirement)

As the Server usage grows so does its monitoring data. Here’s are few examples of scalable monitoring methodologies for data and systems:

1. Sampling:

Collect and analyze only a subset of the data to infer trends or identify anomalies. When working with a large set of data, sampling the data will help perform quality checks on them. It can also help with Performance monitoring for applications.

Eg: Considering only a week's worth of Denodo Monitor logs to check how the server’s resource utilization was for the week.

Benefits: Reduces computational load while providing actionable insights.

Scalability: Easily scales by adjusting sample sizes.

2. Partitioning:

Dividing data into smaller, manageable segments based on key attributes (e.g., date, region).

Partitioning the data helps in identifying resource usage trends for specific key attributes.

Benefits: Simplifies monitoring by focusing on critical metrics.

Scalability: Scales by aggregating data at different levels (e.g., per hour, per user group).

3. Aggregation:

Summarizing data into higher-level metrics or statistics for quick analysis.

Benefits: Simplifies monitoring by focusing on critical metrics.

Scalability: Scales by aggregating data at different levels (e.g., per hour, per user group).

In order to achieve scalable monitoring and to have better performance when querying the Monitor logs generated by the Denodo Platform Server. The following can be performed

• Create views in the Denodo Platform server, pointing to the Monitoring logs or the External Database where the logs are stored.
• Create separate derived views for each KPI or key attribute to avoid retrieving all data each time the monitor logs are accessed for information extraction

Step 4: Design KPIs with VQL Examples

Different stakeholders using the Platform require different KPI  to be measured and visualized.

For example,

• The server administrators would look for more information regarding the operations of servers: performance, failing queries, restarts on the servers, etc
• The Business Intelligence team would require information on Business usage on the server, that is, information on the usage of the servers in terms of team users, requests to the servers, etc.
• The executive team and the Data security team would look for more information on governance and risk compliance, like execution SLA time, view counts, information on roles/users querying sensitive data assets, etc.

These requirements can be answered using the different logs captured by the Denodo Monitor. Below are a few example queries that can be used to answer a few important KPI requirements for different stakeholders. These queries are based on base views created from the Denodo Monitor logs.

Operational Monitoring

1. # of Queries Executed  - Total number of queries run per day. Track utilization.

• The relevant Denodo Monitor table is request_notification.

SELECT COUNT(*) AS NumberOfQueries, datevalue FROM (SELECT *,formatdate('yyyy-MM-dd', starttime) as datevalue FROM bv_request_notification WHERE requesttype IN ('SELECT VIEW','SELECT BASE VIEW','INSERT INTO'))GROUP BY datevalue;

2. #of Queries Failing

• The relevant Denodo Monitor table is request_notification

SELECT count(*), query , state,requesttype FROM bv_request_notification where requesttype IN ('SELECT VIEW','SELECT BASE VIEW','INSERT INTO') GROUP BY state, query,requesttype HAVING state is not null and state not in ('OK','ROW_LIMIT_REACHED','STOPPED','PROCESSING')

3. Most queried views (in number of queries) - The most SELECTION view or the combinations of views in the Server.

• The relevant Denodo Monitor table request_notification.

SELECT distinct(elements),count(*) AS query_count FROM bv_request_notification GROUP BY elements ORDER BY query_count DESC LIMIT 10;

4. Most common queries (count of queries) - The most commonly executed query along with their count.

• The relevant Denodo Monitor Table request_notification

SELECT distinct(query),count(*) AS query_count FROM bv_request_notification where requesttype in('SELECT VIEW','SELECT BASE VIEW') GROUP BY query ORDER BY query_count DESC LIMIT 10;

5. Most accessed data sources - The Datasource that are most frequently queried from the Virtual Data Port Server.

• The relevant Denodo Monitor Table query_datasources_notification

SELECT distinct(data_source_name),count(*),data_source_type FROM bv_query_datasources_notification GROUP BY 1,3;

6. Least Queried Views - The views that are unused from the Virtual Data Port Server, which would ideal for clean-up or optimization.

• The relevant Denodo Monitor Table request_notification

SELECT elements, COUNT(*) AS query_count FROM bv_request_notification WHERE request_type LIKE 'SELECT%' GROUP BY elements ORDER BY query_count ASC LIMIT 10;

7. Top users by Query Volume - Useful for understanding who is consuming the most data..

• The relevant Denodo Monitor Table request_notification

SELECT user_name, COUNT(*) AS query_count FROM bv_request_notification GROUP BY user_name ORDER BY query_count DESC;

8. Usage and Health of Queried Data Sources - Shows which data sources are being used in requests, along with their connection pool status, ping status, and query volume.

• The relevant Denodo Monitor Table request_notification & datasources

SELECT ds.database_name AS datasource_database, ds.name AS datasource_name, ds.type AS datasource_type, ds.total_requests, ds.active_requests, MAX(ds.pool_size) AS pool_size, MAX(ds.active_connections) AS active_connections, MAX(ds.idle_connections) AS idle_connections, ds.ping_status, MAX(ds.ping_execution_time) AS ping_execution_time, ds.ping_down_cause, COUNT(r.id) AS total_queries FROM bv_datasources ds LEFT JOIN bv_request_notification r ON ds.database_name = r.database_name GROUP BY ds.database_name, ds.name, ds.type, ds.total_requests, ds.active_requests, ds.ping_status, ds.ping_down_cause ORDER BY total_queries DESC;

Executive Monitoring

Demand over time

• The relevant Denodo Monitor table is request_notification.
• Relevant fields are duration, cpuusageavg, and cpuusagemax.

SELECT   FORMATDATE('yyyy-MM-dd HH',starttime) AS UsageHour,   SUM(duration) AS TotalDuration,   SUM(cpuusageavg) AS TotalCpuUsageAvg,   MAX(cpuusagemax) AS MaxCPUUsage FROM bv_request_notification GROUP BY UsageHour ORDER BY UsageHour;

Application (by user agent) demand

• The relevant Denodo Monitor table is request_notification.
• The relevant field is useragent.

SELECT     useragent,     COUNT(*) AS NumberOfQueries FROM request_notification GROUP BY useragent ORDER BY NumberOfQueries DESC;

Performance Metrics

Heap Memory usage

• The relevant Denodo Monitor table is resource_polling.
• Relevant fields are resourcepollingdate, heapmemoryusage, and vdpmaxmemory.
• For example, to show average memory by year/month/week/day:

with maxpercentmemory as (select heapmemoryusage, (CAST(SUBSTRING(heapmemoryusage, instr(heapmemoryusage,'/')+1, len(heapmemoryusage)) AS decimal(18, 2))) as total,resourcepollingdate from bv_resource_polling) SELECT     formatdate('yyyy-MM-dd', resourcepollingdate) as datevalue,     (AVG(CAST(SUBSTRING(heapmemoryusage, 0, instr(heapmemoryusage,'/') ) AS decimal(18, 2)))/total)*100 as avg_mem_percent,     (MAX(CAST(SUBSTRING(heapmemoryusage, 0, instr(heapmemoryusage,'/') ) AS decimal(18, 2)))/total)*100 as max_mem_percent FROM maxpercentmemory GROUP BY datevalue,total ORDER BY datevalue

Waiting time for queries

• The relevant Denodo Monitor table is request_notification.
• Relevant fields are requesttype, query, and waitingtime.
• For example, to show average wait time:

SELECT  requesttype,  query,  avg(waitingtime) as AvgWaitingTime FROM bv_request_notification GROUP BY requesttype, query HAVING requesttype = 'SELECT VIEW' ORDER BY AvgWaitingTime DESC

 

CPU usage per query - top 10

• The relevant Denodo Monitor table is request_notification.

SELECT  query, cpuusagemax FROM bv_request_notification ORDER BY  cpuusagemax desc LIMIT 10;

Capacity Planning

Percentage of time at which data sources are at capacity

• The relevant Denodo Monitor table is datasource_polling.
• Relevant fields are databasename, datasourcename, maxactive, and numactive.
• Note the field numactive is polled for the default interval of one minute by default.

SELECT datasourcepollingdate, databasename, datasourcename, maxactive, numactive, (numactive/maxactive)*100 as PercentageActive FROM datasource_polling WHERE maxactive = numactive ORDER BY datasourcepollingdate

Threads per unit of time

• The relevant Denodo Monitor table is resource_polling.
• Relevant fields are resourcepollingdate and threadcount.
• For example, to show min, max, and average by year/month/week/day/hour:

SELECT       formatdate('yyyy-MM-dd', resourcepollingdate) as datevalue,     min(threadcount),     round(avg(threadcount)) as Average,     max(threadcount)         FROM bv_resource_polling     GROUP BY datevalue

Step 5: Logical Design for Performance and Scalability

A well-designed monitoring framework must support high volumes of data with minimal latency and maximum reliability. This step focuses on creating a logical architecture that ensures scalability, performance, and efficient data flow across all layers of the monitoring stack.

Data Ingestion Pipeline

Establish a robust data ingestion mechanism that can collect telemetry, logs, and metrics from various distributed sources.

• Use agents and forwarders such as Fluentd or OpenTelemetry collectors.
• Apply partitioning (by time, service, or region) to isolate workloads and improve parallelism.
• Use sampling to reduce overhead for high-frequency data, especially in trace and log collection.

Storage Architecture

Ensure that collected data is stored in a scalable, queryable, and cost-efficient manner and inline with the Organisation standards for the Database Management/ Administration

• Use distributed storage systems like AWS S3, or Azure Blob Storage to handle large volumes of structured and unstructured data.
• Index logs for fast search and filtering using tools like ElasticSearch or Splunk.

Processing and Analysis

Choose appropriate data processing strategies based on latency and complexity requirements.

• Stream Processing (for real-time alerts and insights): Use platforms such as Apache Kafka, Apache Flink, or Kinesis.
•  by user agent (for deep analysis and reporting): Use tools like Apache Spark or data warehouses to handle scheduled queries.
  

Visualization

Visualization is the layer where raw monitoring data is transformed into meaningful, human-readable insights. Dashboards help different stakeholders quickly assess the state of systems, identify anomalies, and make informed decisions. Hence, stakeholders will be able to access actionable insights through role-based dashboards.

• Design custom dashboards using Denodo Dashboard, Grafana, Power BI, or Tableau.
• Tailor visualizations to specific roles (e.g., DevOps, Engineering, Business Ops).
• Embed drill-down capabilities to go from high-level views to root cause details.

Scalability Considerations

Scalability ensures that the monitoring system remains performant and reliable as the system grows, whether in terms of users, services, data volume, or geographical distribution. Build your monitoring framework to handle increasing data volume and system complexity.

References

Monitoring 

Log column details