Denodo
Connection Type | ODBC (64-bit) |
Driver Details | Bulk write support is available for standard and in-database workflows. In-Database processing requires 64-bit database drivers. You can download the driver from Denodo Drivers. |
Type of Support | Read & Write (Materialized tables only), In-Database |
Validated On |
|
The Denodo cache engine must be enabled in the Server. Go to Denodo documentation for more details on how to activate the cache.
Alteryx Tools Used to Connect
Standard Workflow Processing
In-Database Workflow Processing
Configure a Connection Using a DSN
Go to ODBC and OLEDB Database Connections | Alteryx Help for information on how to create a Data Source Name (DSN) using the Denodo ODBC driver, and how to map it to the Input or Output tools.
For more information on DSN configuration, go to the Denodo documentation.
To use username and password authentication, save the username and password with the DSN or enter it when you configure the connection in Alteryx.
To use Kerberos authentication, select Use Kerberos in the ODBC DSN and set the Krbsrvname to the "service class" of the Service Principal Name of the Server. Usually, the default of HTTP is correct. Your Denodo admin can help verify the value. When you use Kerberos authentication, any entry for username and password is ignored.
Configure a Connection Using a DSN and DCM
Drag an Input Data tool or Output Data tool onto the Designer canvas.
Select the Use Data Connection Manager (DCM) check box.
In the Data Connections window, go to Data Sources and select Denodo ODBC. If Alteryx can't detect an ODBC driver, a pop-up indicates that you need to install a driver.
The Connection Manager window opens. Select +New to add a new data source.
The dropdown pre-selects Denodo ODBC by default.
Enter a descriptive Data Source Name for the new data source, for example, "Denodo Marketing Server DSN".
Select the ODBC DSN field to see a list of available DSNs and select a DSN to use for the data source. If you don’t have a DSN configured, go to the Denodo documentation to create one.
Select Save.
To link a credential, select + Connect Credential.
Select an Authentication Method from the dropdown.
Select Username and Password to authenticate with a username and password.
From the Credential dropdown, select an existing Credential or select Create New Credential to enter a new username and password.
Give the new credential a Credential Name.
If applicable, select a Vault.
Enter the username and password.
Use Test Connection to make sure it has been configured correctly.
Select Create and Link to link the new Credential to the Data Source.
To use Kerberos authentication, select No Credentials. For this option, you must have your ODBC DSN configured for Kerberos authentication.
Use Test Connection to make sure it has been configured correctly.
Select Link to link the Credential to the Data Source.
Select Connect to use the Data Source and Credential in an Input Data our Output tool.
Configure a DSN-less Connection Using DCM
Drag an Input Data tool or Output Data tool onto the Designer canvas.
Select the Use Data Connection Manager (DCM) check box.
In the Data Connections window, go to Data sources and select Denodo Quick Connect. If Alteryx cannot detect an ODBC driver, a pop up prompts you to install a driver.
The Connection Manager window opens. Select +New to add a new data source. If Alteryx can't detect an ODBC driver, a pop up prompts you to install a driver.
The dropdown pre-selects Denodo ODBC DSN-less.
Enter the information for the connection:
Enter a descriptive Data Source Name for the connection, for example, "Denodo Cloud Instance DSN-less".
Enter the Host name.
Select the Driver field to change the driver selection. The default should be set to DenodoODBC Unicode(x64).
Enter the Database name.
Change the Port if needed. The default is set to 9996.
Select SSL Mode. The default is set to Disable. Select Require if SSL is required for the connection.
The User Agent is set to Alteryx_OnPrem by default to help with logging on the Denodo side.
Select Save to save the new data source.
To link a credential, select + Connect Credential.
Select an Authentication Method from the dropdown.
Select Username and Password to authenticate with a username and password.
From the Credential dropdown, select an existing Credential or select Create New Credential to enter a new username and password.
Give the new credential a Credential Name.
If applicable, select a Vault.
Enter the username and password.
Use Test Connection to make sure it has been configured correctly.
Select Create and Link to link the new Credential to the Data Source.
To use Kerberos Authentication, select Kerberos - SSPI.
From the Credential dropdown, select an existing Credential or select Create New Credential.
Give the new credential a Credential Name.
If applicable, select a Vault.
Set the Krbsrvname to the "service class" of the Service Principal Name of the Server. Usually, the default of HTTP is correct. Your Denodo admin can help verify the value.
Use Test Connection to make sure it has been configured correctly.
Select Create and Link to link the Credential to the Data Source.
Select Connect to use the Data Source and Credential in an Input Data our Output Data tool.
Configure a Denodo Bulk Connection to Write Data
The Denodo Bulk option uses Postgres' libpq API to write the data, which allows for faster loads. It's only available in the Output Data tool if DCM is used.
Note
The Denodo Bulk option is currently only available to write materialized tables and it always treats table names and column headers as quoted.
Drag an Output Data tool onto the Designer canvas.
Select the Use Data Connection Manager (DCM) check box.
In the Data Connections window, go to Data sources and select Denodo Bulk.
The Connection Manager window opens. Select +New to add a new data source.
The dropdown pre-selects Denodo ODBC DSN-less.
Enter the information for the connection:
Enter a descriptive Data Source Name for the connection, for example, "Denodo Bulk Materialize Tables".
Enter the Host name.
Select the Driver field to change the driver choice. The default should be set to DenodoODBC Unicode(x64).
Enter the Database name.
Change the Port if needed. The default is set to 9996.
Select SSL Mode. The default is set to Disable. Select Require if SSL is required for the connection.
The User Agent is set to Alteryx_OnPrem by default to help with logging on the Denodo side.
Select Save to save the new data source.
To link a credential, select + Connect Credential.
Select an Authentication Method from the dropdown.
Select Username and Password to authenticate using a username and password.
From the Credential dropdown, select an existing Credential or select Create New Credential to enter a new username and password.
Give the new credential a Credential Name.
If applicable, select a Vault.
Enter the Username and Password.
Use Test Connection to make sure it has been configured correctly.
Click Create and Link to link the new Credential to the Data Source.
Select Kerberos - SSPI to use Kerberos Authentication.
From the Credential dropdown, choose an existing credential or select Create New Credential to create a new credential.
Give the new credential a Credential Name.
If applicable, select a Vault.
Set the Krbsrvname to the "service class" of the Service Principal Name of the Server. Usually, the default of HTTP is correct. Your Denodo admin can help verify the value.
Use Test Connection to make sure it has been configured correctly.
Select Create and Link to link the Credential to the Data Source.
Select Connect to use the Data Source and Credential in an Input Data our Output Data tool.
Configure an InDB Connection
To connect to Denodo using the InDB tools, create a new connection via the Manage In-DB Connections window. Select Denodo as the Data Source and Select New to create a new connection and give it a Connection Name.
Note
Denodo InDB is only supported using DCM. You must have DCM enabled to use Denodo InDB. The InDB tools only support writing to materialized tables and always treat table names and column headers as quoted.
Configure the Read tab
Select Setup Connection to set up a new connection.
Follow the instructions above to Configure a Connection using a DSN and DCM or to Configure a DSN-less Connection using DCM.
Configure the Write tab
The Write tab allows 2 options: None or Denodo Bulk Loader.
If None is selected, no further configuration is needed but you won't be able to write using the InDB tools.
To use the Denodo Bulk Loader, follow the instructions above to Configure a Denodo Bulk Connection to Write Data.
Limitations
For Remote table Write support, see Denodo: Remote Table Support.
COUNT DISTINCT is not supported in the InDB tools for Denodo. To COUNT DISTINCT: Use a Summarize InDB tool to group by the field to be counted. This returns a list of unique values. Filter out NULL values. Use a Summarize tool for a row count of every unique value.
Database is a required field when you connect to Denodo. If you don't provide a database name, this error displays: "connction string lacks some options" or "Missing database name in call to CC_connect".
Denodo has character limitations for passwords. If you store your password in DCM, you need to escape these characters:
%as%25,+as%2B,{as%7Band}as%7D. Go to the Denodo documentation for more information.These Postgres data types are not supported by Denodo:
Postgres boolean data type.
Oracle Time data type.