Exago Logo
Search
Generic filters
Exact matches only

CData Drivers

As of v2018.2, additional database connection types are available using ADO.NET drivers provided by CData Software Inc., in partnership with Exago Inc., to connect to database types not natively included in the application.

Exago BI now officially supports the following database types out-of-the-box via paid CData driver support:

  • Elasticsearch v2018.2+
  • Google BigQuery v2021.1.10+
  • MongoDB v2018.2+
  • Redshift v2019.1.11+
  • Snowflake v2019.1.8+

Note

The Exago BI web server must have full permissions for the Drivers folder in the Web Application installation directory.

CData drivers are initially available for a 30 day trial period. Using a CData driver in production requires a paid license. Once a successful connection is made to a data source using a CData driver, a 30 day trial will begin. At the end of the 30 days, a license will have to be obtained to continue using the driver.

Licensing and Support

CData licenses for use with Exago BI are purchased from Exago Inc. (not from CData Software Inc.). Submit a support request to purchase a license key for a CData driver.

Note

The required license is an internally generated license specific to Exago, and is not interchangeable with a standard CData license. This license is CData version specific and will need to be re-generated when changing CData driver versions.

Exago Inc. may refuse support for CData drivers that are not included with the Exago BI installation and not licensed through Exago Inc. If such support is requested, Exago may require a purchase of service hours.

License Model

CData driver licenses are not included with the base Exago BI license, and are purchased separately or as add-ons to the Exago license agreement.

Purchasing a CData driver license grants you one provider per product per year. More specifically:

One (1) CData driver license applies to the following:

  • One CData driver provider (e.g. “Snowflake ADO.NET Provider”)
  • One product in which Exago BI is embedded (products are defined as per your individual Exago license agreement)
  • One or more Exago instances comprising the product (an instance is an installation of the Web Application or Scheduler Application)
  • One or more data sources (databases) of the provider type and pertaining to the product
  • One calendar year of usage, renewed annually

This information may differ according to the terms of your individual license agreement. Please make sure to verify your specific terms with your business department.

Adding Additional Data Source Types

A list of additional data sources supported by CData can be found on the CData website. Not all sources listed are supported by Exago BI. If you are interested in using a CData driver that is not provided with the Exago installation, please submit a support request or contact your Exago Customer Success Manager.

When adding an additional CData driver, the driver must be placed in the Drivers folder of the web application installation directory.

When setting up an additional data source, create a new configuration in the cdataconfig.json configuration file. They will be available as data source types in the Admin Console when creating a new connection.

When a full license key is obtained, the value must be placed in the License key value pair in the object within cdataconfig.json. The server hosting the application will not have to be restarted. The next time the application is accessed, the connection will be available with the new license.

Configuration File

When using a CData driver, the application will refer to a file in the installation directory under ‘Config/Other’ named cdataconfig.json. The application will look to this file for information about the CData driver and how to use it.

Initially, the license key value for built-in CData sources is “TRIAL”.

cdataconfig.json

This configuration file is comprised of JSON objects that contain specific information for how the data type will appear and behave in the application. These objects are explained in more detail in the table below.

More information about JSON and how to use it can be found in the Using JSON article.

Attribute NameDescription
NameThe outer label for the JSON object is the simplified name for the driver being added.
FriendlyNameThe name of the data source type that will appear in the Admin Console
LicenseThe license value to use the driver. By default this value will be TRIAL.
DateFormatThe specified Date format of dates returned by the data source.
DelimiterCharacters used to surround table names when passing SQL.
TopRowLimitSQLTop row limiting syntax. For limiting the rows returned when using a Top filter.
BottomRowLimitSQL (optional)Bottom row limiting syntax. For limiting the rows returned when using a Bottom filter.
dbInfoThis is another JSON Object with properties that define schema for the data source. These are equivalent to the corresponding fields in the Admin Console Database Settings.
ProviderThis is the name of the library that is obtained from CData for the database driver. This will always have the format of System.Data.CData.{name} where {name} is the proper name of the source type (e.g. Snowflake).
TableSchemaTable schema retrieval properties.
ViewSchemaView schema retrieval properties.
ProcedureSchemaProcedure schema retrieval properties.

Example

{
  "mongodb":
  {
    "FriendlyName":"MongoDB",
    "License":"TRIAL",
    "DateFormat":"YYYY-MM-DD HH:mm:ss",
    "Delimiter":"[]",
    "TopRowLimitSQL":"",
    "BottomRowLimitSQL":" LIMIT {0}",
    "dbInfo":{
            "Provider":"System.Data.CData.MongoDB",
            "TableSchema":"Collection:"Tables", 
                          Restrictions:[null, "@[email protected]"], 
                          NameColumn:"TABLE_NAME", 
                          SchemaColumn:null",
            "ViewSchema":"Collection:"Views", 
                          Restrictions:[null, "@[email protected]"], 
                          NameColumn:"TABLE_NAME", 
                          SchemaColumn:null",
            "ProcedureSchema":"Collection:"Procedures", 
                          Restrictions:[null, "@[email protected]"], 
                          NameColumn:"ROUTINE_NAME", 
                          SchemaColumn:null"
    }
  }
}

Sample Connection Strings

Elasticsearch

Server=[SERVER IP OR HOSTNAME];Port=[PORT];User=[USER];Password=[PASSWORD]

The default port is 9200.

Refer to CData’s ADO.NET Provider for ElasticSearch documentation for a complete list of connection string options and further details.

Google BigQuery

InitiateOAuth=GETANDREFRESH;ProjectId=[GCP PROJECT];DatasetId=[GCP PROJECT];OAuthJWTCertType=GOOGLEJSON;OAuthJWTCert=[PATH TO GOOGLE SERVICE ACCOUNT JSON KEY];OAuthSettingsLocation=[PATH TO STORE SETTINGS];UseConnectionPooling=False;pollinginterval=60;
  • InitiateOAuth should always be set to GETANDREFRESH
  • OAuthSettingsLocation should always be set to a local server directory where OAuth values can be saved.
  • UseConnectionPooling should explicitly be set to False unless using connection pooling.
  • pollinginterval should be set higher than 1

Refer to CData’s ADO.NET Provider for Google BigQuery documentation for a complete list of connection string options and further details

MongoDB

MongoDB data sources should utilize the FlattenArrays and FlattenObjects connection string parameters for best results.

Server=[SERVER IP OR HOSTNAME];Port=[PORT];Database=[DATABASE]

The default port is 27017.

Refer to CData’s ADO.NET Provider for MongoDB documentation for a complete list of connection string options and further details.

Redshift

Server=[SERVER IP OR HOSTNAME];Port=[PORT];Database=[DATABASE];User=[USER];Password=[PASSWORD]

The default port is 5439.

Refer to CData’s ADO.NET Provider for Redshift documentation for a complete list of connection string options and further details.

Snowflake

In v2021.1.0–v2021.1.4, provide a directory and file name in the CredentialsLocation parameter that is writable by the web server. The CData driver will create the file. For example: CredentialsLocation=C:/Temp/SnowflakeCredentials.txtor CredentialsLocation=/var/tmp/SnowflakeCredentials.txt. A unique location must be provided for each Snowflake user account.

For versions v2021.1.0–v2021.1.4:

Url=[SNOWFLAKE_URL];Warehouse=[WAREHOUSE_NAME];Database=[DB_NAME];User=[USER];Password=[PASSWORD];CredentialsLocation=[LOCATION];

For versions pre-v2021.1 and v2021.1.5+:

Url=[SNOWFLAKE_URL];Warehouse=[WAREHOUSE_NAME];Database=[DB_NAME];User=[USER];Password=[PASSWORD]

Refer to CData’s ADO.NET Provider for Snowflake documentation for a complete list of connection string options and further details.

Caveats and Known Limitations of CData Data Sources

  • Elasticsearch
    • Only in-memory joins are supported
  • Google BigQuery
  • MongoDB
    • Utilize the FlattenArrays and FlattenObjects connection string parameters for best results.
Was this article helpful?
0 out of 5 stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
How can we improve this article?
Please submit the reason for your vote so that we can improve the article.
Tags:
Table of Contents