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.8+
- MongoDB (v2018.2+)
- Redshift (v2019.1.11+)
- Snowflake (v2019.1.8+)
NoteThe 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.
NoteThe 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 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 Admin Console 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 (e.g. Snowflake). |
| TableSchema | Table schema retrieval properties. |
| ViewSchema | View schema retrieval properties. |
| ProcedureSchema | Procedure 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, \"@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;
InitiateOAuthshould always be set toGETANDREFRESHOAuthSettingsLocationshould always be set to a local server directory where OAuth values can be saved.UseConnectionPoolingshould explicitly be set toFalseunless using connection pooling.pollingintervalshould be set higher than1
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
- Does not support primary or foreign key constraints. Therefore, the Automatic Database Discovery feature will be unable to add primary keys or find joins.
- Calculating sums or averages of a floating point column can provide non-deterministic results. Refer to BigQuery's documentation for more information.
- MongoDB
- Utilize the
FlattenArraysandFlattenObjectsconnection string parameters for best results.
- Utilize the