Sisense uses connection strings to connect to some data sources and import data into the ElastiCube Manager.

Connection strings have the following structure:

jdbc:DataSourceName:Property1=Value1;Property2=Value2;

The following is an example of a Exact connection string:

jdbc:exactonline:OAuthClientId=xxxxxxxxxxxxx;OAuthClientSecret=xxxxxxxxxxxxx;Region=’United States’;Division=xxxx;InitiateOAuth=GETANDREFRESH;OAuthCallbackURL=localhost:12345;

This page lists the mandatory and optional parameters required by the data source you want to connect to. Mandatory parameters must be defined and included in your connection string whereas optional parameters are only recommended by Sisense.

In addition, some parameters are required by specific data sources. You can view these parameters by clicking the relevant link below:

Mandatory Properties

  • OAuthClientId: The client ID assigned when you register your application with an OAuth authorization server.
  • OAuthClientSecret: Set this to the App Secret in your app settings.
  • CallbackURL: The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. Set this to http://localhost.
  • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.
  • Timeout: The value in seconds until the timeout error is thrown, cancelling the operation. Set to 0 to disable time outs.

Optional Parameters

  • OAuthSettingsLocation: The location of the settings file where OAuth values are saved for each user when InitiateOAuth is set to GETANDREFRESH. See Switching between Accounts.
  • Logfile: Creates a log file. The value of this parameter is the location on your drive where the log file is to be created. To determine how much detail is included in the log file, see the parameter Verbosity below.
  • Verbosity: The verbosity level that determines the amount of detail included in the log file. Set the verbosity from 1 – 5.
    • 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors.
    • 2 will log everything included in Verbosity 1, cache queries, and HTTP headers.
    • 3 will additionally log the body of the HTTP requests.
    • 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.
    • 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.

Switching between Accounts

When you connect to the Facebook data source, Sisense saves your OAuth values in the file OAuthsettings.txt file located at …\Users\xxx\AppData\Roaming\CData\Facebook Data Provider on your Sisense server. To connect to the Facebook data source with another user on the same machine, you must delete the OAuthsettings.txt file. Sisense will then generate a new file for that user.

Another option to support multiple users is to define the location and file name of an OAuthsettings file for each unique user in your connection string through the OAuthSettingsLocation parameter. When each user connects to the data source, Sisense generates the OAuth file with the file name you specify in the location you define. In the examples below, two users are allowed to access the Facebook data source and for each user, Sisense generates a file that contains that user’s OAuth values in the location defined in the string.

jdbc:facebook:OAuthSettingsLocation=C:\facebook\auth\john.txt;OAuthClientId=11276856774486;OAuthClientSecret

=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;

jdbc:facebook:OAuthSettingsLocation=C:\facebook\auth\sally.txt;OAuthClientId=11276856774486;OAuthClientSecret

=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;

In the example above, to OAuth files are created, one for John and one for Sally in the location C:\facebook\auth\.

This is useful if you support many users who each need to access the Facebook data source.

Data Source Parameters

This section lists parameters that are required to be inside connection strings

Exact

  • Region: Set this to the region of the Exact Online service you want to connect to.
  • Division: Set this to the division of the Exact Online administration.

The following is an example of an Exact connection string:

jdbc:exactonline:OAuthClientId=xxxxxxxxxxxxx;OAuthClientSecret=xxxxxxxxxxxxx;Region=’United States’;Division=xxxx;InitiateOAuth=GETANDREFRESH;OAuthCallbackURL=localhost:12345;

Facebook

Rate Limitations

  • ThrottleSeconds: Indicates how many seconds to wait until sending another insight request when the ThrottleMaxPercent has been met.
  • ThrottleMaxPercent: The threshold set for throttling further insight requests. The defaults for ThrottleSeconds and ThrottleMaxPercent are 10 and 95.
  • RetryWaitTime: The amount of time in milliseconds to wait before retrying to query the Facebook API.
  • MaximumRequestRetries: The maximum amount of request retries.

The following is an example of a Facebook connection string:

jdbc:facebook:OAuthClientId=xxxxxxxxxxxxxxx;OAuthClientSecret=xxxxxxxxxxxxxxxxxxxx;
InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;Timeout=0;

Optional Parameters

  • Aggregate Format: The format aggregate or collection columns should return in.
  • Authenticate As Page: The name or Id of a page to authenticate as when making requests to Facebook.
  • Auto Cache: The AutoCache property controls automatic caching of data.
  • Cache Connection: The connection string for the cache database.
  • Cache Driver: The database driver to be used to cache data.
  • Cache Location: Specifies the path to the cache when caching to a file.
  • Cache Metadata: Whether or not to cache the table metadata. The cache will be stored in memory unless CacheConnection or CacheLocation are set.
  • Callback URL: The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
  • Firewall Password: A password, if authentication is required to connect through a firewall.
  • Firewall Port: The TCP port for the firewall FirewallServer — see the description of the FirewallServer option for details.
  • Firewall Server: Specify a firewall name or IP address to authenticate requested connections, if necessary.
  • Firewall Type: The type of firewall to connect through.
  • Firewall User: The user name to authenticate with the firewall.
  • Initiate OAuth: Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
  • Location: A path to the directory that contains the schema files defining tables, views, and stored procedures.
  • Logfile: A path to the log file.
  • Max Log File Size: A string specifying the maximum size in bytes for a log file (ex: 10MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end.
  • OAuth Access Token: The access token for connecting using OAuth.
  • OAuth Client Id: The client Id assigned when you register your application with an OAuth authorization server.
  • OAuth Client Secret: The client secret assigned when you register your application with an OAuth authorization server.
  • OAuth Settings Location: The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH.
  • Offline:Use offline mode to get the data from the cache instead of the live source.
  • Other: The other parameters necessary to connect to a data source, such as username and password, when applicable.
  • Pagesize: The maximum number of results to return per page from Facebook.
  • Proxy Auth: Scheme The proxy server authorization scheme (default: BASIC).
  • Proxy Auto Detect: This indicates whether to use the default system proxy settings or not. Set ProxyAutoDetect to FALSE to use custom proxy settings. This takes precedence over other proxy settings.
  • Proxy Password:A password, if authenticating with a proxy server.
  • Proxy Port: The TCP port for the proxy ProxyServer (default: 80).
  • Proxy Server: If a proxy server is given, then the HTTP request is sent to the proxy instead of the specified server.
  • Proxy SSL Type: The SSL type to use when connecting to the proxy server (default: AUTO).
  • Proxy User: A user name, if authentication is to be used for the proxy.
  • Pseudo Columns: Indicates whether or not to include pseudo columns as columns to the table.
  • Query Cache: The time duration, in seconds, for which the in-memory cached data is reused.
  • Readonly: You can use this property to enforce read-only access to Facebook from the provider.
  • RTK: The runtime key used for licensing.
  • Search Terms: Default SearchTerms if none are specified. Used for some tables, such as Users, where SearchTerms may be specified.
  • SSL Server Cert: The certificate to be accepted from the server when connecting using TLS/SSL.
  • Support Enhanced SQL: If set to true, the provider will cache the entire table in memory and issue the current query against the memory cache. Allows queries with aggregate functions that are otherwise unsupported. The entire memory cache is discarded after the query is executed, unlike QueryCache.
  • Tables: Comma-separated list of tables to be listed. For example: Tables=TableA,TableB,TableC.
  • Target: A default target if none is specified. Used for some tables, such as Comments, where a target may be specified.
  • Timeout:The value in seconds until the timeout error is thrown, canceling the operation.
  • Verbosity: The verbosity level that determines the amount of detail included in the log file.
  • Version: The Facebook Graph API version to use.

HubSpot

  • HubId: Set this to the Hub Id of the HubSpot account you are connecting to.

The following is an example of a HubSpot connection string:

jdbc:hubspot:OAuthClientId=xxxxxxxxxxxxxx;OAuthClientSecret=xxxxxxxxxxxxxxxxxx;InitiateOAuth=GETANDREFRESH;
HubId=xxxxxxxxx;CallbackURL=https://localhost;Timeout=0;

Quickbooks Online

  • CompanyId: Set this to the ID of the company you want to connect to.

The following is an example of a Quickbooks Online connection string:

jdbc:quickbooksonline:OAuthClientId=xxxxxxxxxxxx;OAuthClientSecret=xxxxxxxxxxxx;CompanyId=xxxxxxxxxxxxxx;
CallbackURL=http://localhost/;InitiateOAuth=GETANDREFRESH;Timeout=0;

ServiceNow

  • Password: Set this to your password.
  • Username: Set this to your username.
  • Instance: Set this to your instance.

The following is an example of a ServiceNow connection string:

jdbc:servicenow:InitiateOAuth=GETANDREFRESH;Instance=xxxxxxxxxx;OAuthClientId=xxxxxxxxxxxx;
OAuthClientSecret=xxxxxxxxx;Username=xxxxxxxxxx;Password=xxxxxxxxxxxxxxx;

Xero

  • XeroAppAuthentication: Set this to the type of your application. Allowed values: PUBLIC or PARTNER. Default: PUBLIC.

The following is an example of a Xero connection string:

jdbc:xero:OAuthclientId=xxxxxxxxxxxxx;OAuthClientSecret=xxxxxxxxxxxxx;
InitiateOAuth=GETANDREFRESH;XeroAppAuthentication=PUBLIC;

YouTube Analytics

  • ChannelId: Set this to the YouTube Analytics Channel (Profile). This can be set to the Id of the channel. If not specified, MINE will be used.
  • ContentOwnerId: Set this to the Id of the content owner.

The following is an example of a YouTube Analytics connection string:

jdbc:youtubeanalytics:OAuthClientId=xxxxxxxxxxxxxxxxxxxxx;
OAuthClientSecret=xxxxxxxxxxxxxx;InitiateOAuth=GETANDREFRESH;CallbackURL=http://localhost;