Denodo Dashboard - User Manual
You can translate the document:
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.
Installation
To install the Dashboard, the following steps must be taken:
- Installing Apache Superset.
- Configuring Denodo Monitor.
- Importing the VQL.
- Importing the database into Superset.
- Importing the dashboard.
Installing Apache Superset
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" |
Select the version you prefer, for Denodo 8: denodo-connects-8.0 and for version 9: denodo-connects-9 and after that the superset directory.
At this point you will choose the version of superset-denode you want to download.
Once the version has been selected, the desired tag can be selected. Once selected, the pull command will be copied.
Once you have copied the pull command for docker, run it in the system terminal.
The superset image for Denodo will be downloaded.
By executing the docker images command you can see the new downloaded image:
If you want to simplify the call to this image as the name is long, you can generate a tag for the same image (in this case superset-denode has been used):
docker tag <image_id> <new_name> |
Before starting a Superset for Denodo instance we need to generate the value of the SUPERSET_SECRET_KEY. This environment variable is required by Apache Superset, it is not a Superset for Denodo requirement.This value can be generated for example by using:
openssl rand -base64 42 |
Once the image has been downloaded and the SUPERSET_SECRET_KEY has been generated, in order to start a Superset for Denodo instance, the following command can be used:
docker run -d --name <container_name> -e SUPERSET_SECRET_KEY=<your_secret_key> -p 8088:8088 <repository_name:tag> |
The SUPERSET_SECRET_KEY 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.
Configuring Denodo Monitor
Some dashboards need to have the information recorded by the Denodo Monitor tool saved in a database. To do this, the Denodo Monitor tool must be configured to save the generated log information in a database. In order to configure this database correctly follow the steps in the following documentation:
Check that you have created the necessary tables in the database where you want to store the information from the Denodo Monitor Tool and that the Denodo Monitor Tool is correctly configured to record the information into the database.
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 (VQL)
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. In Desing Studio go to “Administration” > “Database management” > “+ New” and create the following database:
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.
You should always start with module dashboards because if you want to import any other module, you will need to import the vql of this 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.
For each type of database there is a VQL file, denodo_dashboards_oracle.vql is for Oracle databases, denodo_dashboards_sqlserver.vql for sql server databases and for all others there is the file denodo_dashboards_generic.vql.
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.
Once the vql has been imported and the data can be accessed through the imported views in the denodo_dashboard database we will move on to configuring Superset.
We will start by importing the Denodo database denodo_dashboard into Superset.
Importing Denodo as a database in Superset
Before importing any dashboard, the Denodo database must be imported.
This can be done in two ways depending on the environment in which you work with superset.
Instructions for creating a superset database from a script when using a docker container.
If you are running superset in a docker container the steps to follow are as follows:
- Open a terminal.
- Copy the script from the directory where you have the dashboard distribution to the superset docker container:
docker cp <distribution_directory>/superset-database/script_create_database.sh <container_name>:app |
- In the same terminal enter the container.
docker exec -it <container_name> bash |
- Run the script.
./script_create_database.sh |
Next you will be asked to enter the following fields one by one:
- Superset user: user with which you login to superset.
- Superset password: password to log in superset.
- Denodo database user: user with which the connection to the Denodo database is established.
- Denodo database password: password to enter the Denodo database.
- Host: host where Denodo is located.
- Port: port to connect to Denodo.
At the end you will have the denodo_dashboard database imported into Superset.
Import the database from a ZIP file.
- Unzip the .zip file superset_database.zip located in superset-database folder.
- Edit the file superset_database/databases/denodo_dashboard.yaml. The following sqlalchemy_uri fields must be modified: USER, PASSWORD, HOST and PORT (9996 by default).
denodo+psycopg2://<USER>:<PASSWORD>@<HOST>:<PORT(9996)>/denodo_dashboard |
IMPORTANT: When adding the password to the URI, note that if it contains special characters they must be escaped. For example, the password admin@ should be expressed as follows: admin%40
For more information: https://www.ietf.org/rfc/rfc3986.txt
- Compress the .zip file again.
- Import it into superset. Settings > Database Connections > + Database > Import database from file.
At the end you will have the denodo_dashboard database imported into Superset.
You can now import the .zip files of the dashboards.
Importing dashboards in Superset
Once the database has been imported, the dashboards can be imported. 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.
IMPORTANT: Due to a Superset bug when updating dashboards, if you want to overwrite a dashboard you must delete the dashboard and its charts (one by one from the charts list) before uploading the new dashboard version.
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.
When performing the queries to obtain all the information that can be found in the dashboards, the data on the ping queries have been omitted.
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.