Data Connector Reference

A connection is a mandatory part of any dataset, as it enables Sisense to fetch data from the target data source into the application.

Connections can be created either through the Connection Management GUI or via the dedicated set of Connection Management REST API endpoints.

For each connector type, the connection property has a specific structure. In some cases, additional configuration at the Table level is required, such as for CSV and Excel connectors, defined through the Table's configOptions property.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	`"[email protected]"`	Sisense username
`password`	`string`	Yes	`"password"`	Sisense password

Example

"connection": {
  "provider": "EC2EC",
  "schema": "Sample Healthcare",
  "parameters": {
    "ApiVersion": 2,
    "userName": "[email protected]",
    "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	`"[email protected]"`	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": "[email protected]",
        "password": "password",
        "dToken": "VDPa3wOf0zeZftGWv3ApJI8J;",
        "UseSandbox": false,
        "prevProviderTypeIsSalesforce": false,
        "AdditionalParameters": ""
    },
    "uiParams": {},
    "globalTableConfigOptions": {}
}