Data Connector Reference

For each connector type, there's a different structure for the Dataset's connection property. In some cases, additional configuration at the Table level is also required, such as for CSV and Excel type connectors, defined via the Table's configOptions property.

The basic structure for any connection is:

{
    "provider": "string",
    "schema": "string",
    "parameters": {},
    "globalTableConfigOptions": {}
}

Properties:

Name	Type	Required	Description
provider	string	Yes	Connector type name (case sensitive!)
schema	string	Yes	Schema to connect to (required value varies by connector type)
parameters	object	Yes	Connection configuration (varies by connector type)
globalTableConfigOptions	object	No	Some connectors require additional configuration

For individual connector types see:

• CSV
• Excel
• Sisense Elasticube
• MySQL
• Microsoft SQL Server
• PostgreSQL
• Oracle
• Amazon RedShift
• Snowflake
• Salesforce

CSV

CSV File connectors allow for one or more CSV files from the cluster storage to be imported into a Datamodel.

CSV connections require additional configuration at the Table level via each Table's configOptions property.

Main Properties

Name	Type	Required	Value
provider	string	Yes	"CSV"
schema	string	Yes	Path of first file
fileName	string	Yes	Original file name of first file
parameters	object	Yes	Connection configuration
globalTableConfigOptions	object	No	N/A

parameters Properties

Name	Type	Required	Example Value	Description
ApiVersion	number	Yes	2	Use value 2
files	string[]	Yes		An array of file paths for all files
unionAll	boolean	No		Optional: set to true to union all files in the dataset as one table

configOptions Properties

Name	Type	Required	Example Value	Description
delimiter	string	Yes	","	Delimiter for CSV columns
hasHeader	boolean	Yes	true	Is CSV's 1st row a header row
excludeIfBegins	string	No	""
excludeIfContains	string	No	""
stringQuote	string	Yes	"\""	Character denoting a string value
ignoreFirstRows	string	No	""
culture	string	Yes	"en-US"	CSV file culture code

Example

"connection": {
  "provider": "CSV",
  "schema": "/opt/sisense/storage/datasets/storage/a8/a813808a-1df3-4193-a530-2eb9271005d1.csv",
  "parameters": {
    "ApiVersion": 2,
    "files": [
      "/opt/sisense/storage/datasets/storage/a8/a813808a-1df3-4193-a530-2eb9271005d1.csv",
      "/opt/sisense/storage/datasets/storage/77/77ad3229-f84e-4a78-92d8-323f7a5e1802.csv"
    ],
    "unionAll": true
  },
  "uiParams": {},
  "globalTableConfigOptions": {},
  "fileName": "Mostcommonprismfunctionsused.csv"
}

"configOptions": {
  "delimiter": ",",
  "hasHeader": true,
  "excludeIfBegins": "",
  "excludeIfContains": "",
  "stringQuote": "\"",
  "ignoreFirstRows": "",
  "culture": "en-US"
}

Excel

Excel File connectors allow for one or more Microsoft Excel files from the cluster storage to be imported into a Datamodel.

Excel connections require additional configuration at the Table level via each Table's configOptions property.

Main Properties

Name	Type	Required	Value
provider	string	Yes	"Excel"
schema	string	Yes	Path of first file
fileName	string	Yes	Original file name of first file
parameters	object	Yes	Connection configuration
globalTableConfigOptions	object	No	N/A

parameters Properties

Name	Type	Required	Example Value	Description
ApiVersion	number	Yes	2	Use value 2
files	string[]	Yes		An array of file paths for all files
unionAll	boolean	No		Optional: set to true to union all files in the dataset as one table

configOptions Properties

Name	Type	Required	Example Value	Description
fieldsInFirstRow	boolean	Yes	true	Is Excel's 1st row a header row
staticRange	string	No	""
culture	string	Yes	"en-US"	CSV file culture code

Example

"connection": {
  "provider": "Excel",
  "schema": "/opt/sisense/storage/datasets/storage/4e/4eb09302-ad0d-4ed1-a733-31613c682504.xlsx",
  "parameters": {
    "ApiVersion": 2,
    "files": [
      "/opt/sisense/storage/datasets/storage/4e/4eb09302-ad0d-4ed1-a733-31613c682504.xlsx"
    ]
  },
  "uiParams": {},
  "globalTableConfigOptions": {},
  "fileName": "tenants.xlsx"
}

"configOptions": {
  "fieldsInFirstRow": true,
  "culture": "en-US",
  "staticRange": ""
}

EC2EC

Main Properties

Name	Type	Required	Value
provider	string	Yes	"EC2EC"
schema	string	Yes	Name of datamodel
parameters	object	Yes	Connection configuration
globalTableConfigOptions	object	No	N/A

parameters Properties

Name	Type	Required	Example Value	Description
ApiVersion	number	Yes	2	Use value 2
Database	string	Yes	Sample Healthcare	Name of datamodel
userName	string	Yes	"tester@sisense.com"	Sisense username
password	string	Yes	"password"	Sisense password

Example

"connection": {
  "provider": "EC2EC",
  "schema": "Sample Healthcare",
  "parameters": {
    "ApiVersion": 2,
    "userName": "tester@sisense.com",
    "password": "password",
    "Database": "Sample Healthcare"
  }
}

MySQL

Main Properties

Name	Type	Required	Value
provider	string	Yes	"MySql"
schema	string	Yes	Name of SQL Schema
parameters	object	Yes	Connection configuration
globalTableConfigOptions	object	No	N/A

parameters Properties

Name	Type	Required	Example Value	Description
ApiVersion	number	Yes	2	Use value 2
Server	string	Yes	mysql.example.com	MySQL Server location
userName	string	Yes	"admin"	MySQL username
password	string	Yes	"password"	MySQL password
Database	string	Yes	"test1"	Name of database
SslSupport	boolean	Yes	false	Use SSL

Example

"connection": {
  "provider": "MySql",
  "schema": "test1",
  "parameters": {
    "ApiVersion": 2,
    "Server": "mysql.example.com",
    "userName": "admin",
    "password": "password",
    "SslSupport": false,
    "Database": "test1"
  }
}

Microsoft SQL Server

Main Properties

Name	Type	Required	Value
provider	string	Yes	"sql"
schema	string	Yes	Name of SQL Schema
parameters	object	Yes	Connection configuration
globalTableConfigOptions	object	No	N/A

parameters Properties

Name	Type	Required	Example Value	Description
ApiVersion	number	Yes	2	Use value 2
Server	string	Yes	sql.example.com	SQL Server location
UserName	string	Yes	"admin"	SQL Server username
Password	string	Yes	"password"	SQL Server password
Database	string	Yes	"AdventureWorks"	Name of database
encrypt	boolean	Yes	false	Use encryption

Example

"connection": {
    "provider": "sql",
    "schema": "Sales",
    "parameters": {
        "ApiVersion": 2,
        "Server": "sql.example.com",
        "UserName": "username",
        "Password": "password",
        "DefaultDatabase": "",
        "encrypt": false,
        "AdditionalParameters": "",
        "Database": "AdventureWorks"
    },
    "uiParams": {},
    "globalTableConfigOptions": {}
}

PostgreSQL

Main Properties

Name	Type	Required	Value
provider	string	Yes	"PostgreSQL"
schema	string	Yes	Name of SQL Schema
parameters	object	Yes	Connection configuration
globalTableConfigOptions	object	No	N/A

parameters Properties

Name	Type	Required	Example Value	Description
ApiVersion	number	Yes	2	Use value 2
Server	string	Yes	postgres.example.com	SQL Server location
UserName	string	Yes	"admin"	SQL Server username
Password	string	Yes	"password"	SQL Server password
Database	string	Yes	"postgres"	Name of database
SslSupport	boolean	Yes	false	Use SSL

Example

"connection": {
    "provider": "PostgreSQL",
    "schema": "public",
    "parameters": {
        "ApiVersion": 2,
        "Server": "postgres.example.com",
        "UserName": "username",
        "Password": "admin",
        "DefaultDatabase": "",
        "AdditionalParameters": "",
        "SslSupport": false,
        "Database": "postgres"
    },
    "uiParams": {},
    "globalTableConfigOptions": {}
}

Amazon RedShift

Main Properties

Name	Type	Required	Value
provider	string	Yes	"RedShift"
schema	string	Yes	Name of Schema
parameters	object	Yes	Connection configuration
globalTableConfigOptions	object	No	N/A

parameters Properties

Name	Type	Required	Example Value	Description
ApiVersion	number	Yes	2	Use value 2
Server	string	Yes	myinstance.somekey.us-east-1.redshift.amazonaws.com:5439	SQL Server location
UserName	string	Yes	"admin"	SQL Server username
Password	string	Yes	"password"	SQL Server password
Database	string	Yes	"dev"	Name of database
EncryptConnection	boolean	Yes	false	Should uses encrypted connection

Example

"connection": {
    "provider": "RedShift",
    "schema": "ec_sample_ecommerce",
    "parameters": {
        "ApiVersion": 2,
        "Server": "myinstance.somekey.us-east-1.redshift.amazonaws.com:5439",
        "UserName": "admin",
        "Password": "password",
        "DefaultDatabase": "dev",
        "EncryptConnection": false,
        "AdditionalParameters": "",
        "Database": "dev"
    },
    "uiParams": {},
    "globalTableConfigOptions": {}
}

Oracle DB

Main Properties

Name	Type	Required	Value
provider	string	Yes	"Oracle"
schema	string	Yes	Name of SQL Schema
parameters	object	Yes	Connection configuration
globalTableConfigOptions	object	No	N/A

parameters Properties

Name	Type	Required	Example Value	Description
ApiVersion	number	Yes	2	Use value 2
ConnectionType	string	Yes	"Service ID"	Connection type
Server	string	Yes	oracle11g.someserver.com	Oracle DB Server location
Port	string	Yes	"1521"	Oracle DB Server Port
UserName	string	Yes	"admin"	SQL Server username
Password	string	Yes	"password"	SQL Server password
ServiceId	string	No	"xe"	Required if ConnectionType: Service ID is used
Database	string	Yes	"dev"	Name of database
EncryptConnection	boolean	Yes	false	Should uses encrypted connection

Example

"connection": {
    "provider": "Oracle",
    "schema": "TEST",
    "parameters": {
        "ApiVersion": 2,
        "ConnectionType": "Service ID",
        "Server": "oracle11g.someserver.com",
        "UserName": "admin",
        "Password": "password",
        "ServiceId": "xe",
        "Port": "1521",
        "AdditionalParameters": "",
        "Database": "TEST"
    },
    "uiParams": {},
    "globalTableConfigOptions": {}
}

Snowflake

Main Properties

Name	Type	Required	Value
provider	string	Yes	"SnowflakeJDBC"
schema	string	Yes	Name of Schema
parameters	object	Yes	Connection configuration
globalTableConfigOptions	object	No	N/A

parameters Properties

Name	Type	Required	Example Value	Description
ApiVersion	number	Yes	2	Use value 2
connectionString	string	Yes	"jdbc:snowflake://example.eu-central-1.snowflakecomputing.com/?warehouse=DEMO_WH"	Connection string
userName	string	Yes	"admin"	Snowflake username
password	string	Yes	"password"	Snowflake password
Database	string	Yes	"BASICDEMO"	Name of database
useKeyPairAuth	boolean	Yes	false	true to use Key-Pair authentication, false to use username/password

Example

"connection": {
    "provider": "SnowflakeJDBC",
    "schema": "PUBLIC",
    "parameters": {
        "ApiVersion": 2,
        "connectionString": "jdbc:snowflake://example.eu-central-1.snowflakecomputing.com/?warehouse=DEMO_WH",
        "userName": "ADMIN",
        "password": "password",
        "useKeyPairAuth": false,
        "AdditionalParameters": "",
        "Database": "BASICDEMO"
    },
    "uiParams": {},
    "globalTableConfigOptions": {}
}

Salesforce

Main Properties

Name	Type	Required	Value
provider	string	Yes	"SalesforceJDBC"
schema	string	Yes	Name of Schema
parameters	object	Yes	Connection configuration
globalTableConfigOptions	object	No	N/A

parameters Properties

Name	Type	Required	Example Value	Description
ApiVersion	number	Yes	2	Use value 2
userName	string	Yes	"admin@example.com"	Snowflake username
password	string	Yes	"password"	Snowflake password
dToken	string	Yes	"VDPa3wOf0zeZftGWv3ApJI8J"	Security token
UseSandbox	boolean	Yes	false	Connect in sandbox mode

Example

"connection": {
    "provider": "SalesforceJDBC",
    "schema": "Salesforce",
    "parameters": {
        "ApiVersion": 2,
        "userName": "admin@example.com",
        "password": "password",
        "dToken": "VDPa3wOf0zeZftGWv3ApJI8J;",
        "UseSandbox": false,
        "prevProviderTypeIsSalesforce": false,
        "AdditionalParameters": ""
    },
    "uiParams": {},
    "globalTableConfigOptions": {}
}