CData Drivers

Important:

As of v2024.2.1, CData drivers are no longer available for use in Exago BI products. Use alternative methods of connecting to a database or web service. You must remove any CData dll files in your installer path to continue to use Exago BI.

With this removal, the following databases are not currently supported:

Elasticsearch

Google BigQuery

MongoDB

Redshift

Snowflake

Original CData Drivers Information

Important:

As of v2024.2.1, CData drivers are no longer available for use in Exago BI products. Documentation is retained here for reference purposes only.

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

Exago BI 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+

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 (not from CData Software Inc.). If you need more information, contact Exago Support.

The required license is an internally generated license specific to Exago BI, 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 may refuse support for CData drivers that are not included with the Exago BI installation and not licensed through Exago. 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 BI 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 (for example “Snowflake ADO.NET Provider”)
One product in which Exago BI is embedded (products are defined as per the individual Exago BI license agreement)
One or more Exago BI 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 the individual license agreement.

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 need more product information, including new purchases and upgrades, 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

This topic references <WebApp>/, <WebSvc>/ and <Sched>/as a placeholder for the Web Application, Web Service API and Scheduler Service's install location respectively. The default install location is C:\Program Files\Exago\ExagoWeb\ (/opt/Exago/ on Linux), C:\Program Files\Exago\ExagoWebApi\ ( /opt/Exago/WebServiceApi/ on Linux) or C:\Program Files\Exago\ExagoScheduler\ (/opt/Exago/Scheduler/ on Linux); however, these directories can be changed during installation.

When using a CData driver, the application reads <WebApp>\Config\Other\cdataconfig.json 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 Definition

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 topic.

Attribute Name	Description
Name	The outer label for the JSON object is the simplified name for the driver being added.
FriendlyName	The name of the data source type that will appear in the Admin Console
License	The license value to use the driver. By default this value will be TRIAL.
DateFormat	The specified Date format of dates returned by the data source.
Delimiter	Characters used to surround table names when passing SQL.
TopRowLimitSQL	Top 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.
dbInfo	This is another JSON Object with properties that define schema for the data source. These are equivalent to the corresponding fields in the Database Settings.
Provider	This 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 (for example Snowflake).
TableSchema	Table schema retrieval properties.
ViewSchema	View schema retrieval properties.
ProcedureSchema	Procedure schema retrieval properties.

Example Configuration File

{
  "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, "@database@", 
                          NameColumn:"TABLE_NAME", 
                          SchemaColumn:null",
            "ViewSchema":"Collection:"Views", 
                          Restrictions:[null, "@database@"], 
                          NameColumn:"TABLE_NAME", 
                          SchemaColumn:null",
            "ProcedureSchema":"Collection:"Procedures", 
                          Restrictions:[null, "@database@"], 
                          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