Denodo Dashboard - User Manual

Last modified on: 09 Oct 2023

Overview

Denodo Dashboard is a DenodoConnect component that allows the creation of a set of interactive dashboards with the aim of visualizing and analyzing the logged information into a database by the Denodo Monitor tool, included in the Denodo Platform, as well as metadata from the Denodo server itself.

These dashboards have been built to be imported into a Docker container run with the image Superset for Denodo, created by Denodo and based on Apache Superset. Apache Superset is an open-source modern data exploration and data visualization platform. The Superset for Denodo image adds, to the Apache Superset features, the ability to connect to Denodo. This feature offers the availability to analyze and visualize Denodo Monitor logged data and Denodo server metadata using the Superset platform, making use of all the functionalities it provides.

Execution

For visualizing and working with Denodo Dashboard, it is necessary to download and run or deploy the latest version of the Superset for Denodo image published at the Denodo Container Registry:

repository:"harbor.open.denodo.com/denodo-connects-8.0/images/superset-denodo"

Once the image has been downloaded, in order to start a Superset for Denodo instance, the following command can be used:

docker run -d --name superset-denodo -e SUPERSET_SECRET_KEY=<your_secret_key> -p 8088:8088 superset-denodo

Note that the environment variable called SUPERSET_SECRET_KEY is required by Apache Superset, it is not a Superset for Denodo requirement. This environment variable can be generated using:

openssl rand -base64 42

It can be set with the -e option, as in the example above, and it will be used by Superset to securely sign the session cookie and encrypt sensitive information on the database.

Now, navigating to http://localhost:8088 you can login (u/p: [admin/admin]) and take a look at Superset for Denodo.

Importing Denodo Dashboard into a Superset for Denodo instance

With Superset for Denodo started you can add the dashboards provided in the Denodo Dashboard component. The Denodo Dashboard distribution consists of:

One folder for each module:

Dashboards. This is the base module and includes an overview of the data generated by Denodo Monitor. It covers query, performance and cache reports, in addition to query analysis.

FinOps. This module offers data concerning the FinOps capabilities added since the Denodo update 8.0u20230914.

Metadata. This module contains Denodo server metadata as well as activity, user and performance information linked to the metadata itself.

Each module folder has a .zip file for each dashboard and a vql folder:

.zip file: contains the dashboards folders with YAML files organized in the corresponding folders of databases, datasets, charts and dashboards. The .zip file can be used directly in the Superset for Denodo dashboard import process.

vql folder: contains the VQL file with the VQL statements to create the metadata in Denodo and a properties file with .properties extension. The properties file allows you to set the connection configuration in order to read the data handled by the Denodo Dashboard.

Configuring and importing Denodo metadata

In order to visualize in Superset for Denodo the dashboards provided by the Denodo Dashboard, it is necessary to create in a Denodo server the metadata used by the component. This process involves the creation of a denodo_dashboard database under which the necessary metadata is grouped. Among these metadata elements, a data source is configured to access the data logged by Denodo Monitor into a database. To accomplish this, you should review and configure the values of the properties in the .properties files at the vql folders for each module.

First of all, you should review and configure the values of the properties in the denodo_dashboards.properties file, available at the vql folder of the Dashboards module. It contains the configuration data to create a data source in order to access the database where Denodo Monitor stores the logs:

# DENODO MONITOR DATA SOURCE PROPERTIES

# -------------------------------------

denodo.dashboards.database.denodo_monitor.DRIVERCLASSNAME=<DRIVERCLASSNAME>

denodo.dashboards.database.denodo_monitor.DATABASEURI=<JDBCURI>

denodo.dashboards.database.denodo_monitor.USERNAME=<USERNAME>

denodo.dashboards.database.denodo_monitor.USERPASSWORD=<PASSWORD>

denodo.dashboards.database.denodo_monitor.USERPASSWORD.ENCRYPTED=

denodo.dashboards.database.denodo_monitor.CLASSPATH=<CLASSPATH>

denodo.dashboards.database.denodo_monitor.DATABASENAME=<DATABASENAME>

denodo.dashboards.database.denodo_monitor.DATABASEVERSION=<DATABASEVERSION>

denodo.dashboards.database.denodo_monitor.SCHEMANAME=<SCHENAME>

Note that the metadata created on a Denodo server as a result of importing the VQL file and its properties from the Dashboards module, corresponds to the base structure of the metadata required by Denodo Dashboard. The other modules are dependent on this main structure. Therefore, denodo_dashboards.vql, together with the denodo_dashboards.properties file, must always be imported, regardless of which module you want to import.

After importing the denodo_dashboards.vql file, you can import the VQL files, with its properties, of the modules you are interested in.

The FinOps module properties file needs only information about the schema in which the Denodo Monitor logs are stored. This value should match the one specified for the Denodo Monitor data source in the denodo_dashboards.vql file.

denodo.dashboards.database.denodo_monitor.SCHEMANAME=public

The Metadata module properties file contains the connection data to a Denodo server that contains the metadata we are interested in viewing and analyzing. If the Denodo Monitor is monitoring a single server, this configuration will have the connection data to that server. In the scenario where the monitoring is being performed on a cluster, the connection data must correspond to that of a node in the cluster, as a representative of the involved nodes since the nodes in a cluster should have the same metadata, or it can also correspond to that of the load balancer.

# DENODO METADATA DATA SOURCE PROPERTIES

# --------------------------------------

denodo.dashboards.database.denodo_metadata.DATABASEURI=jdbc\:denodo\://<HOST>\:<PORT>/<DATABASE>

denodo.dashboards.database.denodo_metadata.USERNAME=<USERNAME>

denodo.dashboards.database.denodo_metadata.USERPASSWORD=<PASSWORD>

denodo.dashboards.database.denodo_metadata.USERPASSWORD.ENCRYPTED=

After completing the configuration in the properties files, for the purpose of importing the files, select "File" > "Import" in the VDP Admin Tool or Design Studio. The import window will appear and you will have a field to add the VQL file. To be able to add the properties file, check the "Use properties file" option and then add the .properties file.

Importing dashboards

The Denodo Dashboard distribution provides a set of dashboards that can be imported into a Superset for Denodo instance to explore and visualize logged information by the Denodo Monitor as well as Denodo server metadata.

With this aim, Denodo Dashboard includes three modules: Dashboards, FinOps and Metadata. Each module has a folder that contains its corresponding .zip files, one for dashboard, that can be included in Superset for Denodo using the Import Dashboards option.

During the dashboard import process, it will ask for the password to be used in the connection to the Denodo server that contains the metadata required by the Denodo Dashboard (see the Configuring and importing Denodo metadata section).

Once you have added a Denodo dashboard, the Database Connections option, under the Settings menu, will allow you to set the other connection data. For that, you should edit the denodo_dashboard connection and configure correctly the SQLAlchemy URI:

denodo+psycopg2://<USER>:XXXXXXXXXX@<HOST>:9996/denodo_dashboard

Denodo Dashboard overview

Superset for Denodo will allow you to explore, visualize and analyze the dashboards included in the Denodo Dashboard component. It is also important to highlight that users can configure and extend these dashboards, as well as create new ones (see the Configuring and extending dashboards section). Check out the Importing Denodo Dashboard into a Superset for Denodo instance section for more detailed information regarding the importing process.

Note that all dashboards have a default filter applied to load only the last week's data. This filter can be modified and configured.

Dashboards module

The Dashboards module offers four dashboards with several charts organized in tabs that contain information about the Denodo VDP server’s performance and usage. These dashboards are built on the logs generated and stored in a database by the Denodo Monitor tool.

The dashboards in the Dashboards module are:

Denodo Query Reports : dashboard-query-reports.zip
Denodo Incoming Query Analysis: dashboard-incoming-query-analysis.zip
Denodo Performance Reports: dashboard-performance-reports.zip
Denodo Cache Reports: dashboard-cache-reports.zip

Denodo Query Reports

This dashboard contains global information regarding queries (number of queries, duration, number of rows, etc.) categorized according to different criteria such as databases, views or status. This approach makes it possible to classify the content in ten tabs, which are detailed in the following sections.

Databases

This tab displays query information classified by Denodo databases.

The pie chart groups, by database, the number of views used in the queries executed in VDP. Besides, there are three bar charts with information regarding the total queries, average query execution time and average number of rows.

The table, at the bottom, shows a summary about relevant information regarding executed queries, following the criterion of organizing the data by Denodo database.

Views

This tab displays query information regarding the views involved in the queries.

At the top, it shows information about the total number of derived views and also base views implicated in requests to Denodo.

The graphs, located in the central part, show:

for the ten views more used in queries, the total number of queries where they are engaged.
the number of distinct views used in executed queries, by days.

The table, at the bottom, shows a summary about relevant information regarding executed queries, following the approach of organizing the data by view.

Queries

This tab shows information about the queries.

At the top you can see a summary of the number of different executed queries and the average number of queries per day.

Three bar charts are shown below. The first shows the ten most executed queries. The second shows the average execution time of the five queries with the highest average execution time. The last one contains the five queries with the highest average number of rows returned as a result of their execution.

The table, at the bottom, shows an overview of the relevant information related to the executed queries.

Query Status

The Query Status tab contains information regarding the queries based on their status. It is important to point out that from this tab you can apply cross-filtering to show all the Denodo Query Reports dashboard information by a selected status.

Execution Timeline

This tab shows information about number of executed queries, total number of returned rows, average of returned rows, average queries execution time and average queries waiting time during time, grouped per hours.

The table, at the bottom, shows a summary with the data used in the graphs and some extra relevant information (%cache and %successful).

Please note that it may be useful to use the cross-filtering in Databases and/or Views tabs. In this scenario, the Execution Timeline tab will display the same type of information but centered on a database and/or a view.

Cache

This tab shows query information about queries regarding the use of a cache.

The bar charts display the total rows and the queries duration differentiating between scenarios where a cache has been used, referenced with “True”, and those where it has not, referenced with “False”.

The table, at the bottom, shows a summary following the same criteria as the graphs.

User

The User tab displays information regarding the users that execute the queries received by Denodo.

The bar graph shows the 20 users with more executed queries. On the other hand, the User Overview table displays, categorized by user, more detailed information like the total executed queries (Total Queries), the average duration of these queries (Average Duration), average number of returned rows (Average Number of Rows), total number of rows returned by the queries (Total Number of Rows), average time that queries were waiting to be executed (Average Waiting Time), percentage of queries that has used the cache (%Cached) and percentage of queries that ended successfully (%Successful).

Access Interface

This tab shows query information about queries grouped by the access interface used by the client.

In the pie chart there is information about the total number of queries executed by each access interface. While the Access Inteface Overview table displays, categorized by the access interface used by the client, more detailed information like the total executed queries (Total Queries), the average duration of these queries (Average Duration), average number of returned rows (Average Number of Rows), total number of rows returned by the queries (Total Number of Rows), average time that queries were waiting to be executed (Average Waiting Time), percentage of queries that has used the cache (%Cached) and percentage of queries that ended successfully (%Successful).

Note that you can use the cross-filtering functionality in order to select in the Databases and/or the Views tabs a database and/or view to show in this tab the information related to only one of these items.

User-Agent

This tab contains query information about queries, categorized by the client user agent that performed the query.

In the pie chart there is information about the total number of queries executed by each client User-agent. While the User-Agent Overview table displays, categorized by the client user agent, more detailed information like the total executed queries (Total Queries), the average duration of these queries (Average Duration), average number of returned rows (Average Number of Rows), total number of rows returned by the queries (Total Number of Rows), average time that queries were waiting to be executed (Average Waiting Time), percentage of queries that has used the cache (%Cached) and percentage of queries that ended successfully (%Successful).

The cross-filtering functionality can be used in order to select in the Databases and/or the Views tabs a database and/or view to show in this tab the information related to only one of these items.

Client IP

The Client IP tab shows query information about queries, categorized by the IP of the client.

It displays the total number of different client IPs and a bar graph representing the number of queries according to the client IP. The ClientIP Overview table has a summary with query information organized by client IP.

In the bar chart there is information about the total number of queries executed by each client IP. While the ClientIP Overview table displays, categorized by the IP of the client, more detailed information like the total executed queries (Total Queries), the average duration of these queries (Average Duration), average number of returned rows (Average Number of Rows), total number of rows returned by the queries (Total Number of Rows), average time that queries were waiting to be executed (Average Waiting Time), percentage of queries that has used the cache (%Cached) and percentage of queries that ended successfully (%Successful).

Denodo Incoming Query Analysis

The Denodo Incoming Query Analysis dashboard contains several graphs that offer a general and summarized view of the executed queries, total and distinct incoming queries in addition to graphs where this data is categorized by database or by the status of the query. Besides, there is information about the number of users involved in the queries for which information is displayed and the most used views.

The table at the bottom shows some details of the queries: start time of the query (starttime), user that launched the query (username), client user agent that performed the query (useragent), the ip of the client (clientip), access interface used by the client (accessinterface), name of the target database of the query (database_0), the query (query), Yes/No value showing if the query was completed (completed), status of the query (state), number of rows returned by the query (numrows), duration of the query (duration).

Denodo Performance Reports

This dashboard shows information about the server or environment that is monitored using the Denodo Monitor Tool, categorized in four tabs which are detailed in the following sections.

Note that filtering by Host can be useful in order to see more behavioral information by isolating servers.

Connections & Threads

This tab shows information about the number of connections at every instant and also about the number of threads at every instant.

Memory

The Memory tab displays information about the memory usage of the JVM that executes the monitored server or environment.

First, it shows data of the Heap Memory usage: the maximum value (Max Heap Memory Usage) and a time series graph to see its evolution (Heap Memory Usage). Then it shows data of the Non Heap Memory usage: the maximum value (Max Non Heap Memory Usage) and a time series graph to see its evolution (Non Heap Memory Usage). It also displays a time series with the total memory usage information (VDP Total Memory).

The Memory Overview table contains detailed information regarding the memory usage classified by date:

Heap Memory: sum of the memory used/total size of the Eden Space, Survivor Space and Tenured Generation space.
Non Heap Memory: Sum of the memory used/total size of the Permanent Generation and the Code Cache spaces.
Eden space
Survivor space
Meta space
Code cache

Loaded Classes

This tab shows two time series graphs with information about the number of Java classes that are loaded and about the total number of Java classes loaded since the server or servers of the environment were started.

The table, at the bottom, includes the heap memory usage (Heap Memory column) for each instant, in addition to information regarding loaded classes.

CPU and GC Usage

This tab shows information about the usage of the CPU and the Garbage Collector at every instant, expressed in percentage. The table displays a summary about them.

Denodo Cache Reports

This dashboard shows information about the cache usage and management categorized in five tabs which are detailed in the following sections.

Databases

This tab displays cache information classified by Denodo databases.

The first graph shows the number of refresh cache processes executed by every Denodo database for the ten databases with the most cache refresh processes. The second chart shows the data storage time (maximum and average) that is spent when the cache process is executed for the five databases whose maximum data storage time is the longest. Finally, the last graph shows the number of rows (maximum and average) that are inserted in every process for the five databases whose maximum number of rows is the highest.

The table, at the bottom, contains a cache overview by Denodo database with relevant information regarding the cache process like number of partial load cache, number of full load cache, number of partial load matching exact query, etc.

Views

This tab displays cache information classified by Denodo views.

The first graph shows the number of refresh cache processes executed by every Denodo view for the ten views with the most cache refresh processes. The second chart shows the data storage time (maximum and average) that is spent when the cache process is executed for the five views whose maximum data storage time is the longest. Finally, the last graph shows the number of rows (maximum and average) that are inserted in every process for the five views whose maximum number of rows is the highest.

The table at the bottom contains a cache overview by Denodo database with relevant information regarding the cache process like number of partial load cache, number of full load cache, number of partial load matching exact query, etc.

Cache Timeline

This tab shows three time series graphs with information about the number of cache refresh processes per view. The first does not take into account the cache type. The second one shows information taking into account the full load cache processes and the third one displays information about partial cache processes.

The Partial Cache Overview table offers a general summary of the cache processes sorted by the time when the cached data started to be stored in the underlying cache table.

Cache Type

The Cache Type tab displays information regarding the cache process according to the cache mode.

The pie graphs show information about the number of cache processes taking into account the cache mode. Whereas, the bar graphs show the average storage time and average inserted rows depending on the cache mode.

The table, at the bottom, offers a summary of the cache processes based on the cache mode.

Conditions

This tab shows information about the different conditions involved in the load cache processes.

FinOps module

The FinOps module provides a dashboard with several charts organized in tabs that contain information regarding the usage of data taking into account some aspects such as region and providers. Note that this information is logged by the Denodo Monitor since the Denodo update 8.0u20230914, this dashboard will not work on previous versions.

The dashboard is:

Denodo FinOps Reports: dashboard-finops.zip

Denodo FinOps Reports

This dashboard shows information about the FinOps indicators that can be used to get insights about the usage of data in the organizations and to make cost estimations on the cloud usage. This data is categorized in seven tabs which are detailed in the following sections.

Overview

This tab information is classified in two main categories:

Data Transfer. The left half of this tab shows the total data transfer to Denodo, in megabytes, and several charts where it is possible to visualize the number of rows transferred per Denodo database, adapter or data source provider.

Execution Time. The right half of this tab shows data and graphs about the aggregated execution time at the data source level, for each query that Denodo sends to the underlying sources. This metric helps understand computation cost at the sources.

Note that it represents only source execution time, disregarding the additional Denodo processing time.

Cross-localization

The Cross-localization tab shows information about data that has been transferred between different providers or regions.

The first row of charts shows a summary of the total rows transferred grouped by Denodo database, adapter or data source provider.

The second row of charts displays information about the total rows transferred in a scenario where Denodo is running on one infrastructure provider and has transferred data from a datasource which is running on another provider. This data is displayed classified by the Denodo database in the first graph; by the data source adapter in the second graph and by the data source provider in the last one graph.

The last row of charts shows information about the total rows transferred in a scenario where Denodo is running on one infrastructure provider region and has transferred data from a datasource which is running on another infrastructure provider region. This data is displayed classified by the Denodo database in the first graph; by the data source adapter in the second graph and by the data source provider in the last one graph.

Data Source Provider

This tab contains data relating to the data transferred and the execution time spent, categorized by the data infrastructure provider where the data source is running.

The pie graph represents the amount of rows transferred classified by the data source provider. The Data Source Provider Overview table provides further information showing data about the number of rows returned, the estimated data transferred and the execution time.

Data Source Type

This tab displays data relating to the data transferred and the execution time spent classified by the type of the data source that the Denodo server accessed to execute the queries.

The pie graph represents the amount of rows transferred classified by the data source type. The Data Source Type Overview table provides further information showing data about the number of rows returned, the estimated data transferred and the execution time.

Data Source Region

This tab displays data relating to the data transferred and the execution time spent classified by the infrastructure provider region where the data source is running.

The pie graph represents the amount of rows transferred classified by the infrastructure provider region where the data source is running. The Data Source Region Overview table provides further information showing data about the number of rows returned, the estimated data transferred and the execution time.

Client Provider

This tab shows data concerning the data transferred and the execution time spent classified by the infrastructure provider where the client is running.

The pie graph represents the amount of rows transferred classified by the infrastructure provider where the client is running. The Client Provider Overview table provides further information showing data about the number of rows returned, the estimated data transferred and the execution time.

Client Region

This tab shows data concerning the data transferred and the execution time spent classified by the infrastructure provider region where the client is running.

The pie graph represents the amount of rows transferred classified by the infrastructure provider region where the client is running. The Client Region Overview table provides further information showing data about the number of rows returned, the estimated data transferred and the execution time.

Metadata module

The Metadata module offers a dashboard containing several charts, grouped in tabs, with information about the Denodo server metadata such as number of data sources, number of views, number of tags, etc. along with information retrieved from the database where Denodo Monitor logs information.

Be aware that this module contains metadata from a Denodo server which can be a node of a cluster (see the Configuring and importing Denodo metadata section for more detailed information). Therefore, the Dashboard only is meaningful if the data recorded by Denodo Monitor, retrieved by Denodo Dashboard through the Dashboards module configuration, belongs to a Denodo server or cluster where the unification of data and metadata makes sense.

The dashboard is:

Denodo Metadata Overview: dashboard-metadata-overview.zip

Denodo Metadata Overview

This dashboard provides information about the Denodo server metadata. This data is categorized in five tabs which are detailed in the following sections.

Overview

This tab displays a metadata summary regarding data sources and views. First, it shows the number of data sources, number of base views and number of derived views created in the Denodo server. The time series chart offers a quick overview of the number of different views used per day, whereas the pie chart shows the number and proportion of views per database. The table contains data about the number of base views that depend on each data source (dependency_name column).

Activity Summary

The Activity Summary tab shows information about the executed queries under different criteria.

The Queries Today chart has information on the trend line for the total executed queries per day. Besides, it shows the date and the number of queries executed on the last day with logged information.

The Queries Type per Day chart displays the number of different types of queries executed per day.

The two pie graphs, Total Queries by Access Interface and Total Queries by Useragent, show information about the number of executed queries grouped by the access interface used by the client and by the user agent that performed the query.

The Total Queries by Database chart is a bar chart where the ten Denodo databases with more executed queries are represented with their number of total queries.

The twenty queries with the longest execution time are displayed in the Longest Running Queries chart.

Finally, the time series bar chart Queries Timeline, at the bottom, shows the total number of incoming queries executed in Denodo over time.

Users Overview

The Users Overview tab contains a global view of users activity.

In addition to the total number of users, the tab has an Unique Users per Day chart where the number of different users that execute queries per day are represented in bars and a Top 10 users chart where the ten users with more executed queries are included showing the number of queries in the bars.

The User Overview table has activity information per user: total executed queries (Total Queries), the average duration of these queries (Average Duration), average number of returned rows (Average Number of Rows), total number of rows returned by the queries (Total Number of Rows), average time that queries were waiting to be executed (Average Waiting Time), percentage of queries that has used the cache (%Cached) and percentage of queries that ended successfully (%Successful).

Server Resources

This tab shows the usage of the CPU expressed in percentage (CPU Usage), the memory usage of the JVM expressed in Megabytes (Memory Usage), the number of threads (Threads Count) and the number of currently active connections to the Denodo server (Active Connections). All this data is grouped and displayed by server name.

Configuring and extending dashboards

The Denodo Dashboard component offers several dashboards with multiple graphs and tables. However, these dashboards are editable, therefore you can import them (see the Importing Denodo Dashboard into a Superset for Denodo instance section for further information) and then perform changes in the existing dashboards or charts. Nevertheless, you can also create new ones using available datasets or even register new datasets using the denodo_dashboard connection (created during the Denodo Dashboard import process) or creating a new connection:

For more detailed information about configure, extend or create new dashboards; please refer to the Superset documentation.

USER MANUALS

Denodo Dashboard - User Manual

Overview

Execution

Importing Denodo Dashboard into a Superset for Denodo instance

Configuring and importing Denodo metadata

Importing dashboards

Denodo Dashboard overview

Dashboards module

Denodo Query Reports

Databases

Views

Queries

Query Status

Execution Timeline

Cache

User

Access Interface

User-Agent

Client IP

Denodo Incoming Query Analysis

Denodo Performance Reports

Connections & Threads

Memory

Loaded Classes

CPU and GC Usage

Denodo Cache Reports

Databases

Views

Cache Timeline

Cache Type

Conditions

FinOps module

Denodo FinOps Reports

Overview

Cross-localization

Data Source Provider

Data Source Type

Data Source Region

Client Provider

Client Region

Metadata module

Denodo Metadata Overview

Overview

Tags

Activity Summary

Users Overview

Server Resources

Configuring and extending dashboards