Lookout

Expert trails guide Denodo users through all the relevant materials related to a specific topic, including official docs, KB articles, training, Professional Services offerings, and more. The main goal is to give users a single place with references to all the information that they need to become a Denodo expert on any specific topic.

“How to connect to my Data source from Denodo?” is usually the first question that comes to our mind when starting a project with the Denodo Platform.

In this Expert Trail, you will learn different types of options to access your data source, configuration techniques, and some of the advanced features in data source configuration.

The Hike

Stage 1: Different ways to access data in real-time

The first step in a Denodo project is to create data sources to connect to various data repositories, APIs, etc. Other steps on the Denodo project are described in the Expert Trail: Development Lifecycle and how the metadata organization should be set up is described in the Expert Trail: Modeling and Metadata Organization.

The following are the different types of data sources available in Denodo:

• JDBC: to access relational databases like Oracle, Snowflake, etc.
• Web Service: to access the SOAP Web service.
• Multidimensional DB: to obtain data from multidimensional databases like Azure Analysis Services,  Oracle Essbase, etc.
• XML: to access the XML files.
• JSON: to access data from the JSON files.
• Delimited File: to access data from delimited text files like CSV.
• Object Storage data: to access data from Object Storage that are in Parquet and Delta format.
• Excel: to access data from Excel files.
• LDAP: to delegate authentication tasks to an LDAP server.
• Salesforce: to access data from Salesforce REST API.
• BAPI: to obtain data stored in SAP ERP and other SAP applications.
• MongoDB: to retrieve data from MongoDB sources.
• OData 2 & OData 4: to access OData services.
• Custom: to access ad-hoc wrappers developed in Java for which no standard connector is available.
• SOFTWARE AS A SERVICE wizard contains Denodo Templates (Note that these are not data sources by themselves, these are just a collection of JSON and XML data sources). This includes the templates to create the necessary data sources and views to query the REST API of several popular SaaS services (Microsoft Dynamic 365, Marketo, Google Analytics, ServiceNow, Microsoft Sharepoint, etc.).

The document Creating Data Sources and Base Views explains how to create a data source for each type mentioned above. There are a few predefined custom connectors to access some of the data sources, and file formats stored in HDFS, S3, Azure Data Lake, etc, that can be downloaded from the DenodoConnects section of the Support Site.

Stage 2: What do you need to configure the data sources

Let’s focus on the different configuration parameters required to create data sources in Denodo.

• For the JDBC data source, you need a JDBC driver, driver class, and connection URI. The Supported JDBC Data Sources documentation provides the details of the JDBC adapters and the JDBC drivers included in the Denodo installation. You could use the option File > Extension management from the Administration Tool to upload a custom JDBC driver or to upload a JDBC driver not included by default in the Denodo installation. The section Importing a JDBC Driver explains how to do this.
• When you create a data source of the type XML, JSON, Excel, Direct connection type of ODBC source, or Delimited File (DF), you have to specify a path to the data files. The document Path Types in Virtual DataPort explains in detail the different path types available in Virtual DataPort.

• For the SOAP web service, the WSDL file that contains the Web service specification needs to be specified.

• For SAP BW 3 and SAP BI 7, Azure Analysis Services, Microsoft SQL Server Analysis Services (SSAS), Mondrian, Oracle, and Essbase you need to create Multidimensional Database Sources and provide the required parameters for the appropriate adapter selected.
• For LDAP data sources, you need the LDAP server URI, credentials to connect to it.

• For SAP BAPI data sources, you need to install the SAP Java Connector and Grant Privileges in SAP for BAPI Data Sources.

• For Salesforce data source, the main aspect to configure is the authentication flow. This connector uses the protocol OAuth 2.0 and supports these authentication flows:

• Web server flow
• Username and password flow

• MongoDB source can be created by using authentication options like Login and password, Kerberos with login and password, Kerberos with Keytab, and Use Pass-through session credentials.
• OData2 and OData4 custom wrappers are included in Virtual DataPort and you no longer need to download it from the Support site and import it. You can directly provide the OData Service Endpoint, OData Service format and other necessary parameters to access your OData services.

In the case of the custom data source, you would need to develop a custom wrapper using the APIs provided by Denodo. The section Developing Custom Wrappers explains how to develop them.

Stage 3: Authentication

Now, let's focus on different authentication methods available in the data source configuration

• Use login and password: This is the easiest way to connect to the data source. This option is available in data sources such as JDBC, ODBC, BAPI, Multidimensional DB, and LDAP.
• Use Pass-through session credentials: If selected, Virtual DataPort will connect to this source using the credentials of the user that executes the query. This option is available almost in all data sources except LDAP and Salesforce data sources. Please note that using this option is depending on the capabilities of the source underneath. To utilize this feature provided by Denodo, the underlying source needs to support it. The section Considerations When Configuring Data Sources with Pass-Through Credentials explains in detail the considerations for using the Pass-through.
• Use OAuth: Virtual DataPort supports connecting via OAuth authentication against some of the JDBC databases. More information about it can be found from the section Connecting to a JDBC Source with OAuth Authentication.
• Use Kerberos: This network authentication protocol is an option available in JDBC, BAPI, and Multidimensional data sources. In case of using a JDBC data source, read the section Connecting to a JDBC Source with Kerberos Authentication for more information about this.
• In the case of using HTTP connections for the data source such as XML, JSON, Excel, and delimited text file (DF) data source, the supported authentication methods are as follows: Basic, Digest, Mutual (two-way SSL), NTLM, SPNEGO (Kerberos), OAuth 1.0a and OAuth 2.0. The documentation Authentication in HTTP Paths explains in detail.
• The Salesforce data source uses the OAuth 2.0 authentication protocol to securely access data.

More information about Security can be found in the Expert Trail: Security.

Stage 4:  Advanced features in different data sources

Next, we will see some of the advanced configurations to optimize the data source.

Connection pooling:

To minimize the cost of opening connections, Denodo utilizes an optimization technique called connection pooling. A connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. This option is available in data sources such as JDBC, ODBC, LDAP, Web services, Multidimensional, and BAPI data sources. The Pool of Connections of the JDBC Data Sources documentation explains more in detail.

Source Configuration:

The Source Configuration in the data source specifies certain characteristics of the underlying sources, such as their support operations. For instance, if you have created a custom delegatable function then you can append this function to the Delegate scalar functions list property to delegate the function to the data source. This option is available in JDBC and ODBC data sources. The Data Source Configuration Properties documentation explains in detail each property available in the data sources.

Driver Properties:

The Driver properties are the list of name/value pairs that will be passed to the JDBC driver when creating a connection with the underlying database. For instance, 'v$session.osuser'='@{USER_NAME}' in the Oracle driver properties will assign the Virtual Dataport session username to oracle JDBC property v$session.osuser. In the case of JDBC, the option “Driver properties” will allow you to set the driver properties. Similarly, for the ODBC source, click “DSN Properties Configuration” to add properties to the connection URI.

Bulk load API:

JDBC data sources can be configured to use the bulk load APIs and this option useful for writing data into a database. This option is useful for caching, remote tables, summaries, and data movement optimization.  The Bulk Data Load documentation explains more in detail.

Proxy:

For the data source that establishes an HTTP connection if the HTTP requests are sent through a proxy, Virtual DataPort provides an option to configure the proxy server information. The documentation Default Configuration of HTTP Proxy explains in detail how to configure these default values.

Secure Network Communications (SNC):

Secure Network Communications (SNC) provides stronger authentication and encryption mechanisms than the default security options of SAP. This option is available in Multidimensional and BAPI data sources for SAP.

Check Certification:

This option is available in the data sources that establish HTTP connections such as XML, JSON, Excel, delimited text file (DF) data source, Web Service datasource. You could utilize this option if the service requires SSL client authentication. The Importing the Certificates of Data Sources (SSL Connections) documentation explains how to do this.

Filter:

Virtual DataPort can access data files that are compressed or encrypted. This option is available in XML, JSON, Excel, and delimited text file (DF) data sources. The Compressed or Encrypted Data Sources documentation explains in detail this option.

Pagination:

This option will allow you to limit the number of records it returns per request. You can configure DF, JSON, and XML data sources to retrieve data from a REST API paginated. The section Pagination of Virtual DataPort Administration Guide elaborates in detail this option.

Stage 5: Error analysis

In the final stage, we will focus on how to debug the errors that may occur during the data source connection.

Let's take an example: Following is the error you may receive when trying to make a connection to SQL Server “ IOException: Connection timed out error”.

First, look into the vdp.log file located under <DENODO_HOME>/logs/vdp directory for the detailed error message. The error message suggests the data source is not reachable from the Denodo host machine. For further analysis, make sure there are no firewall restrictions or network issues. You can do so by doing a PING to the data source host or connecting from a different JDBC client. Similarly, if the issue is for a REST API try connecting to the same data source from POSTMAN to rule out connectivity issues.

 
Logging System:

Denodo Platform uses the library Apache Log4j 2 to log its activity. To debug the error and to analyze its root cause, you could look for generated logs. The Log column details Knowledge Base article describes the content of the VDP logs and Denodo Monitor logs that could provide insights into which log file what kind of information to debug.

Modifying the log level:

To get additional information from the log file, you could define different log levels for different log categories. The section Configuring the Logging System of  Virtual DataPort Administration Guide and Using logcontroller and useful log categories provide insights on configuring the different log levels and some useful log categories.

More information about Monitoring can be found in the Expert Trail: Monitoring.

Exploration

Fill up your backpack with additional gear:

Guided Routes

Denodo Training Courses

Denodo training courses provide expert data virtualization training for data professionals, including administrators, architects, and developers.

If you are interested in DataSource Connectivity you should enroll in the following course:

• Implementing My First Logical Data Integration: This course provides data developers with Denodo Platform 8.0 concepts, terminology, and skills needed to develop a data virtualization project.

Success Services

Denodo Customer Success Services can help you at the start or any part of your Self-Service Analytics trail. You can find information about the Denodo Success Services offering in:

Success Services

Advisory Sessions

Denodo Customers with active subscriptions have access to request Meet a Technical Advisory sessions.

These are the sessions available related to DataSource Connectivity.

Success Accelerators

In addition to Advisory sessions, Success Services includes Success Accelerators that can help you.

If you are a Denodo customer, you can reach out to your Customer Success Manager for details about any Guided Route that you need.

Big Hike Prep Check

Let’s see if you are ready to start your big trial. Take this 5-question questionnaire to check your readiness for an enjoyable hike.

Read the questions below, think about the solution and check if you got them right by looking at the solution. Have you become an expert?

1. You have created a JSON data source to read a set of log files with a local path that includes date range, and the data source will read all the log files in order. If one of the dates mentioned in the specific date range is missing, the query will fail. Would it be possible to avoid this kind of error in Denodo?

Click here to check if you got it right

2. What can you do when Denodo does not provide a standard connector to connect to your data source?

Click here to check if you got it right

3. Would it be possible to get the data source access information from the log? If yes, what was the exact log category that would be beneficial to retrieve information about XML data sources?

Click here to check if you got it right

4. You have created a JDBC data source to retrieve data from Oracle, and you want this data source to list its synonyms. Would it be possible to introspect its synonyms in Denodo? 

Click here to check if you got it right

5. How could you access a file when it is encrypted with the algorithm PBE with HMAC-SHA256-512 and AES-256?

Click here to check if you got it right