Introduction

This document describes how to create some views in Denodo Virtual DataPort (VDP) to access the information of the jobs and their reports created in Denodo Scheduler. To access this information, we will use Scheduler’s REST API, which allows you to interact with the Scheduler servers.

Scheduler API Documentation

The Scheduler REST Client API explains the basics on how to use Scheduler’s REST API. The documentation about the different endpoints available for this API can be found in the following URL:

• http://<host>:<port>/webadmin/denodo-scheduler-admin/swagger-ui/index.html

where you have to specify the host and the port where your Denodo Scheduler is running.

In this example, we will use two APIs endpoints. The first endpoint gets information about the jobs and the second endpoint gets information about the reports for a specific job.

Accessing the API

Authentication

In order to use this API, an authentication action needs to be done. There are several ways to do it. 

1. Public which uses HTTP Basic Authentication (RFC 7617) in all requests (stateless) or OAuth <https://oauth.net/2/> which could be used in stateless or stateful mode.
2. Private which performs a login before any request and logout after all them. The private alternative is deprecated to be used by external REST API clients and it should not be used.

Data source

Scheduler API is a REST API and returns responses in JSON format. So the first step is to create a JSON data source. To create the JSON data source, go to the Server Explorer and then to New > Data source > REST API > JSON.

The Tool will display the dialog to create the data source:

1. Data route: HTTP client.
2. Initially we use a Base URL, this URL has been obtained from the documentation of the API: http://localhost:9090/webadmin/denodo-scheduler-admin/public/api 

In this example we are pointing to a Scheduler server that is located on the same host as the VDP server and with the default port for the web container.

3. In this example we will use Basic Authentication: login and password with pass-through credentials.

Base View to get the list of Scheduler jobs

Once we have created the data source we have to create a base view to retrieve information about the jobs of the Denodo Scheduler jobs.

1. We create a base view from the data source.
2. We have to specify the relative URL: /jobs?uri=%2F%2Flocalhost%3A8000 

In this relative URL we should put the server´s uri, localhost to access a local Scheduler server and port 8000 for the default port but this might change.

3. In this view we will not use pagination, so we have to enable “Use pagination options of the data source”. It will use the pagination options defined in the data source, in this case no pagination was defined.

4. Finally, we specify the tuple root to flatten the response of the API and click on Create Base View. We need to enter the tuple root /JSONFile/JSONArray, this is the default tuple root when no name is included for the element in the first level of the JSON response.

Base View to get the reports for a job

Once we have accesed the jobs, we will access reports information, so we will create a base view from the data source to retrieve information from the reports.

We will use the same data source as before to create the new base view:

1. According to the documentation of the API in the relative URL we have to specify two parameters, one for the job ID and another for the project ID. We will be using interpolation variables to provide these values. Also, we will include the uri as with the previous view.

/projects/@param_projectid/jobs/@param_jobid/reports?uri=%2F%2Flocalhost%3A8000

2. In this base view we have to activate pagination, we have to specify two parameters: one parameter (count) for the page size and another (start) for the index of the first records. We will also set a maximum number of requests. To do this, we will have to do the next steps:

• Enable “Define pagination options for this base view”.
• Enable “The service returns the data paginated”
• Maximum number of requests:100.
• Enable “Paging indices”:

1. Parameter in URL for page size:count.
2. Page size: 100.
3. Parameter in URL for next records: start.
4. Index of first records: 1.
5. Offset for next requests:100.

3. After configuring the pagination we have to introduce a jobID and a projectID sample values to create the view.

4. We specify the tuple root to flatten the response and click on Save: /JSONFile/list.

5. Finally, we edit the view to change the data type of some of the fields param_jobid (int), param_projectid (int), starttime and endtime (timestamp). This is just to access the information of the report in an easier way.

Derived views

Jobs selection view

After creating the base view that obtains the list of jobs, we are going to create a derived view on top of it to select the output fields that we are interested in.

1. Click on the base view and go to New > Selection.
2. In our example we are going to remove from the output some fields we are not interested in: draft, disabled, extractionsection, exportationsection, triggersection and reportsection.

Reports selection view

After creating the report’s base view, we create a derived view to select the output fields that are of our interest.

1. New > Selection
2. We leave these output fields: param_projectid, param_jobid, starttime, endtime, result, retrycount, query.

Final result

Join view

Eventually, to obtain the final result, we make a join between the derived views, in order to associate each job with its reports.

1. Click on the Jobs derived view and go to New > Join.
2. We have to make an inner join where the join conditions are:

1. id = param_jobid and
2. projectid = param_projectid

3. Finally, we remove in the output section: projectid, id. We have to remove these fields because they are duplicated.

Expected result

If we execute the join view that we have created, we can see (as the following screenshot): the project ID, the job ID, the type of the job, the name of the project, the name of the job, the description of the job, start time, end time, the result of the job, the retrycount and the query used.

Visualization

Once we have our Denodo views ready, they can be easily consumed using any reporting tool. In this example we are just showing a small dashboard using Microsoft Power BI.

Connecting Power BI to Denodo

The connection between Power BI and Denodo Platform is usually made through the use of a DSN and Dendo’s ODBC driver or using a connection string. You can view in detail how to connect Power BI to Denodo by reading this document.

Example of a dashboard

In the dashboard that we have made with PowerBI, you can see four charts:

1. Count of results: this chart shows the count of jobs grouped by execution status.
2. Executions per date:this chart shows how many jobs have been executed each day.

References

Scheduler REST Client API

Pagination

Connecting Denodo from PowerBI