Data Source Setups

The following specific connection details are needed for each type of data source in addition to the general ones described here.

Amazon Athena

The JDBC connectors for Athena must be downloaded from the Connectors Marketplace and then uploaded manually. Both of these steps are performed using the Custom Connectors tools.

  • Port: usually 444.
  • Authentication Method: Select from one of four options:
    • Application Authentication: provide the application user and password.
    • Single Sign-on (OAuth) - End User: See below.
    • Single Sign-on (OAuth) - Specific User: See below.
    • Default AWS Credentials provider chain: use credentials from AWS. Only relevant and available if running inside AWS.
  • Region: select an AWS region from the list.
  • S3 Staging Directory: the location in S3 where queries are to be stored.

Single Sign-on (OAuth)

When using AWS for OAuth Authentication, you need to set up multiple fields describing how to connect to OAuth and providing the settings inside AWS. This includes setting up a new identity provider (Open ID Connect), a role with a policy for access to Athena, and a trust relationship with the provider account. The ARN for this role is required when setting up OAuth authentication in Pyramid.

  • Click here for more details

Amazon Redshift

  • Port: usually 5439
  • Authentication Method: Select from one of three options:
    • User name and password.
    • Single Sign-on (OAuth) - End User.
    • Single Sign-on (OAuth) - Specific User.

User name and password

To secure connections, provide the application user and password and select the Secure Connection options. If the certificate should also be validated, provide details describing how to find the certificate store on the host servers. This applies to the whole cluster. Select the elements needed for the certificate store:

  • Select the Encrypted (SSL) checkbox to secure connections.
  • If connections are secured, you can:
    • Select the Validate Certificate checkbox to validate the server certificate.
    • Select the Validate server certificate hostname checkbox to validate the hostname.
    • Select an option from the Use custom CA drop-down to import a custom root CA certificate or use a custom TrustStore.

Single Sign-on (OAuth)

When using AWS for OAuth Authentication, you need to set up multiple fields describing how to connect to OAuth and providing the settings inside AWS. This includes setting up a new identity provider (Open ID Connect), a role with a policy for access to Redshift, and a trust relationship with the provider account. The ARN for this role is required when setting up OAuth authentication in Pyramid.

  • Click here for more details

Amazon S3

There is no need for a server address. The connection is built from the region and keys instead.

  • Region: select an AWS region from the list
  • Authentication: provide the AWS access Key ID and secret access keys

Apache Drill

The JDBC connectors for Drill need to be uploaded manually using the Custom Connector tools. The driver can be found in the Connector Marketplace or can be independently downloaded from Apache Drill.

  • Port: usually 31010
  • Authentication: provide the application user and password

Apache Hive

The JDBC connectors for Hive need to be uploaded manually using the Custom Connector tools. The driver can be found in the Connector Marketplace or can be independently downloaded from Apache Hive.

  • Port: usually 10000
  • Authentication: provide the application user and password or use Keytabs

Apache Presto

  • Port: usually 8080
  • Authentication: provide the application user and password

Azure Blob

There is no need for a server address. The connection is built from the keys instead.

  • Authentication: provide the Azure account and keys

Azure Synapse

  • Instance Name provide the name of the instance if configured. This should be blank if using the default. This is not required if specifying a port.
  • Port: usually 1433
  • Authentication: provide the application user and password or choose Single Sign-On (OAuth).
  • Secure Connection: to secure connections ensure that the check box is enabled. If the certificate should also be validated, details need to be provided on how to find the certificate store on the host servers. This applies to all runtime, task and AI servers. Select the right elements needed for the certificate store and set the values.
    • Encrypted (SSL) - secure connections.
    • Validate Certificate - Check this to validate the server certificate. If its false, the connection will simply work with an unvalidated SSL certificate.
    • Override hostname in certificate hostname - ignore the certificate hostname and provide an alternative one.
    • TrustStore Settings - use this option to specify a custom TrustStore for the certificate if the certificate was not imported to the Java trust store.
    • Custom TrustStore - when using a custom trust store, provide the alternative trust store path and password.

  • For further details on configuring Single Sign-on between Microsoft Azure AD and Azure Synapse, please see here.

Box

A server address is not needed. The connection is effectively built from the authentication process.

  • Authentication: Select from 3 options:
    • Single Sign-on (OAuth) - Specific User
    • Single Sign-on (OAuth) - End User
    • Token

Single Sign-on (OAuth)

If OAuth is used, copy the Client ID and Client Secret from the Box website. If token is used, copy the token from the Box application to the Access Token field. The token is temporary and must be used within a limited time frame.

  • Click here for more details

BigQuery

A server address is not needed. The connection is effectively built from the authentication process. For details on configuring user level data access to Google BigQuery, consult the Google OAuth Setups page.

ClickHouse

  • Port: typically 8123
  • Authentication: provide the application user and password

Databricks

  • HTTP Path: as defined in the Databricks/ Spark control panel
  • Port: usually 443
  • Authentication: Based on an Access Token

Denodo

  • Port: default is 31010
  • Authentication: provide the application user and password or use Keytabs

Dremio

The JDBC connectors for Dremio must be downloaded from the Connectors Marketplace and then uploaded manually. Both of these steps are performed using the Custom Connectors tools.

  • Port: default is 31010
  • Authentication: provide the application user and password

Exasol

  • Port: typically 8563
  • Authentication: provide the application user and password
  • Secure Connection: to secure connections ensure that the check box is enabled. If the certificate should also be validated, details need to be provided on how to find the certificate store on the host servers. This applies to all runtime, task and AI servers. Select the right elements needed for the certificate store and set the values.
    • Encrypted (SSL) - secure connections.
    • Validate Certificate - Check this to validate the server certificate.
    • Custom TrustStore - Use this option to specify a custom TrustStore for the certificate if the certificate was not imported to the Java trust store.

Facebook

Facebook requires a more complex process for setup. Click here for more details.

FireBird

  • Port: typically 3050
  • Default Database: The connection must have at least 1 database included to work.
  • Authentication: provide the application user and password

FTP Server

  • Port: typically 21
  • Authentication: provide the application user and password
  • Optional: choose the encryption type (unencrypted, Explicit FTPeS, or Implicit FTPS) and mode (default, active, or passive).

Click here for more on connecting to FTP.

Glue (Datavard)

Datavard Glue needs to be installed into the host SAP system before the connection can be established.

  • URL: this is the REST API entry point from Glue
  • Authentication: provide the application user and password

Each intermediate Glue destination server (configured in Glue) needs to be mapped to its corresponding server in Pyramid. Both technologies need to be pointing at the same physical server and content for the connector to operate. Each Glue package is associated with a destination. The package will not be available unless that destination is added.

Google Analytics (GA4)

There is no need for a server address. The connection is built from the OAuth code.

  • Connection Type: "Google Analytics 4".
  • Security: Specify the details of your Google Analytics account. For details on configuring user-level data access to Google Analytics consult the Google OAuth Setups page.

Google Sheets

There is no need for administrative setup here, it can be setup from the Model interface directly. The sheets need to be transitioned to the "public" mode - where a simple URL provides access to the content.

Greenplum

  • Port: usually 6432
  • Authentication: provide the application user and password

IBM DB2

  • Port: typically 50000
  • Default Database: The connection must have at least 1 database included to work.
  • Authentication: provide the application user and password or use Keytabs

IBM DB2 AS400

  • Default Database: The connection must have at least 1 database included to work.
  • Authentication: provide the application user and password

Impala

  • Port: typically 21050
  • Authentication: provide the application user and password

Pyramid In-Memory

  • Port: typically 12170
  • Authentication: provide the application user and password. The user is usually "default". Using different user names on the same address, will hard segment the data files and effectively create stand alone instances on the same host machine.

MariaDB

  • Port: typically 3306
  • Authentication: provide the application user and password

Mongo BI Connector

The JDBC connectors for Mongo need to be uploaded manually using the Custom Connector tools. The driver can be found in the Connector Marketplace or can be independently downloaded from Mongo.

This connector requires the enterprise edition of Mongo.

  • Port:typically 3307
  • Authentication: provide the application user and password

Microsoft Analysis Services and Power BI

  • Instance Nameprovide the name of the instance if configured. This should be blank if using the default. This is not required if specifying a port.
  • Portusually 2382
  • Authentication: There are 3 options available when selecting the authentication method:
  • Alternative account Use the specified user name and password. A domain account that has administrative rights to Analysis Services needs to be added to the data card and you must define how end-user authentication will be accomplished.
  • Global account Use the user name and password from the global account settings.
  • Service account Use the user name and password that is running the Windows Connector service.
  • Multi Pass Authentication: Check this to set multi pass authentication. If authentication via impersonation fails, try other authentication options.

  • Machine Account Fallback: check this in the event the security mechanics can be ignored

  • MDX Compatibility: set this to "1" for older MDX functional compliance.
  • Azure Tabular: indicate if this is an Azure Tabular connection (only on Tabular types)
  • Connection Type: choose the manner in which the SSAS server is configured to receive connections. The connection type can be set to Direct, Azure, Power BI Premium Workspace, or HTTP MDpump before setting security.

Click here for more details about Analysis Services.

Microsoft and Azure SQL Server

  • Display Name: provide the name of the instance, if configured.
  • Port typically 1433
  • Authentication: provide the application user and password, or provide Windows Authentication mechanism.
  • Secure Connection: to secure connections ensure that the check box is enabled. If the certificate should also be validated, details need to be provided on how to find the certificate store on the host servers. This applies to all runtime, task and AI servers. Select the right elements needed for the certificate store and set the values.
    • Encrypted (SSL) - secure connections.
    • Validate Certificate - check this to validate the server certificate.
    • Override hostname in certificate - ignore the hostname in the certificate.
    • TrustStore Settings - use this option to specify a custom trust store for the certificate if the certificate was not imported to the Java trust store.
    • Custom TrustStore - when using a custom trust store, provide the alternative trust store path and password.

Note: Java 17 does not allow connections with TLS versions under 1.2.

Setting up Windows Authentication for MS SQL Server can be quite complex. Click here for more.

MySQL

  • Port: typically 3306
  • Authentication: provide the application user and password

Neo4J BI Connector

The JDBC connectors for Neo4J BI need to be uploaded manually using the Custom Connector tools. The driver can be found in the Connector Marketplace or can be independently downloaded from Neo4J .

  • Port: typically 7687
  • Default Database Name: neo4j
  • Authentication: provide the application user and password

Neo4J Cypher

Neo4j is not a relational database, and is not table-based. It uses its own query language to discover links between information sources.

  • Port: typically 7687
  • Authentication: provide the application user and password

Netezza

The JDBC connectors for Netezza needs to be uploaded manually using the Custom Connector tools. The driver can be found in the Connector Marketplace or is can be independently downloaded from IBM.

  • Port: typically 5480
  • Authentication: provide the application user and password

NetSuite

The JDBC connectors for NetSuite need to be uploaded manually using the Custom Connector tools. The driver can be found in the Connector Marketplace or can be independently downloaded from Oracle NetSuite.

  • Port: typically 1708
  • Authentication: provide the application user and password
  • Service Data Source: NetSuite.com
  • Host: provide a valid Host name
  • Account: provide a valid Account
  • Role: provide a valid Role

ODBC

Click here for more on configuring ODBC.

Oracle

  • Service Name: this is the separate Oracle service name or ID. Required.
  • Port: typically 1521
  • Authentication: provide the application user and password
  • Secure Connection: to secure connections ensure that the check box is enabled. If the certificate should also be validated, details need to be provided on how to find the certificate store on the host servers. This applies to all runtime, task and AI servers. Select the right elements needed for the certificate store and set the values.
    • Encrypted (SSL) - secure connections.
    • Validate server certificate DN - check this to validate the server certificate distinguished name.
    • Override DN in server certificate - ignore the distinguished name in the certificate. You can enter an alternative distinguished name.
    • Custom trust store - use this option to specify a custom trust store for the certificate if the certificate was not imported to the Java trust store. You can choose JKS or PKCS12 as type, then enter the network path and password for the server.
    • Custom Keystore - for client-based certificate authentication. Choose JKS or PKCS12 as type, then enter the network path and password for the server.

PostgreSQL

This connector will usually work for any implementation of PostgreSQL including Heroqu and Amazon Aurora.

  • Port: typically 5432
  • Authentication: provide the application user and password
  • Default Database: The connection may need at least 1 database included to work - it depends on the implementation.
  • Secure Connection: to secure connections ensure that the check box is enabled. If the certificate should also be validated, details need to be provided on how to find the certificate store on the host servers. This applies to all runtime, task and AI servers. Select the right elements needed for the certificate store and set the values.
    • Encrypted (SSL) - secure connections.
    • Validate Certificate - check this to validate the server certificate.
    • Validate server certificate hostname - check this to validate the hostname (which cannot be overridden). If this option is unchecked, the default Java TrustStore will be used to validate the certificate.
    • User custom certificate configuration - provide a custom root CA certificate.

SalesForce

You must provide the Consumer Key and Consumer Secret from your SalesForce environment. Click here to learn how to setup SalesForce for these details.

  • Authentication: provide the application user and password
  • Security Token: The security token for connections provided by SalesForce
  • Custom Domain: if required, the connection can be made to a custom SalesForce domain - which must then be provided.

SAP BW

SAP BW requires a more complex process for setup.

SAP DataSphere

SAP DataSphere uses the same mechanisms to connect has SAP HANA.

SAP HANA and SAP HANA Cloud

SAP HANA and HAN Cloud require a more complex process for setup.

SAP (Sybase) IQ

  • Instance Name: provide the name of the instance if configured. This should be blank if using the default.
  • Port typically 2638
  • Authentication: provide the application user and password.
  • Default Database: The connection needs at least 1 database included to work.

SFTP (SSH)

  • Port: typically 22
  • Authentication: provide the application user and password

SharePoint

Note: Only SharePoint Lists are supported as data sources.

  • URL: this is the SharePoint entry point
  • Authentication: provide the Microsoft ID user and password (especially for Office 365) or provide the Windows Authentication mechanism.

SingleStore (MemSQL)

  • Port:typically 3306
  • Default Database: The connection must have at least 1 database included to work.
  • Authentication: provide the application user and password

Note: You can also use the MySQL connector for MemSQL.

Snowflake

Snowflake does not require a server address.

  • Account Name: the snowflake account name
  • Region: set the Snowflake region where your data warehouse is hosted
  • Authentication: There are four different authentication methods:
    • User Name and Password, to be specified
    • Single Sign-On (OAuth) - Specific User
    • Single Sign-On (OAuth) - Proxy 1
    • Single Sign-On (OAuth) - Proxy 2
  • For each Single Sign-On, you can specify Client ID, Client Secret and Scope. OAuth Settings can be global, or Custom Settings, which can include:
    • JSON Web Keys URI
    • OAuth Token Endpoint
    • OAuth Authentication Endpoint
  • Warehouse: Provide the name of the warehouse you will be connecting to. Each warehouse needs a separate connection.
  • For further details on configuring Single Sign-on between Microsoft Azure AD and Snowflake, please see here.

Spark

  • Port usually 10000
  • Default Database: default
  • HTTP Path: sql/protocolv1/o//
  • Authentication: provide the application user and password

For more information regarding Spark SQL, refer to Spark Connection Properties

Starburst

  • Port typically 8080
  • Authentication: provide the application user and password.

Teradata

  • Port typically 1025
  • Authentication: provide the application user and password or use Keytabs

Vertica

  • Port typically 5433
  • Authentication: provide the application user and password.
  • Default Database: The connection needs at least 1 database included to work.

X (Twitter)

X requires a more complex process for setup. Click here for more details.