Configuring B2D in Sisense

Overview

This topic includes:

• Enabling B2D in Sisense

• Configuring the B2D Destination Storage - Using the REST APIs to set up an AWS bucket

• Configuring the B2D Connection - Using the CLI commands and yaml files to create writer and viewer connections to the CDWH

• Configuring the Destination Database

• Build Configurations

• FAQ

• Yaml file examples

Enabling B2D in Sisense

To enable B2D in Sisense:

1. Open the Admin tab and click the Feature Management link.

2. Under the Management section, toggle the Build Destination switch on.

   

Configuring the B2D Destination Storage

The Destination Storage REST APIs are used to configure an AWS bucket. This is where the B2D build process will copy the exported CSV (or Parquet) files during the build.

In the Sisense application, click the Admin tab > REST API > 2.0 > and see the Destination Storage section to view all of the APIs:

Parameters of the JSON:

• accessKey (text) - The key used to access S3

• bucketFolder (string) -

• bucketName (string) - The name of the bucket that should be used. The bucket name must adhere to AWS policy (for example, it should be lowercase characters only).

• bucketRegion (string) - The region where the S3 instance is located.
  For example: us-east-2

• iamRole (string) - The role that was configured for the given bucket.
  For example: arn:aws:iam::<?>:role/<name of role>

• name (string) -

• secretKey (string) - The shared secret (text) for the key to access S3.

• storageIntegration (string) - Optional when Snowflake is the destination. This can be used instead of access key and secret key.

• storageType (string) - The type of storage used. AMAZON_S3 only.

Example:

{

"accessKey": "string",

"bucketFolder": "string",

"bucketName": "string",

"bucketRegion": "string",

"iamRole": "string",

"name": "string",

"secretKey": "string",

"storageIntegration": "string",

"storageType": "string"

}

Configuring the B2D Connection

1. Create a yaml file for the Connections, to be used to update the Sisense environment configurations:
   

   Connections yaml holds the CDWH details:

   connections:

   - connectionString: host_string:port
     user: the user name
     password: the password for the user above
     role: which role related to the user (required only for Snowflake connections)
     database: database name
     permissions: what permissions granted to the role defined (e.g writer)
     buildDestination: destination (e.g redshift)

   See Configuring B2D in Sisense.

2. Once the file is ready, place it or create it in this location: /opt/sisense/storage/ (a common method is to create it using vim commands or to use a freeware FTP tool).
3. SSH to the designated environment to configure the Connections in Sisense:
   1. Run CLI command si connections create -path [/opt/sisense/storage/FILE_NAME.yaml] to configure the Connections in Sisense using the file we created for that purpose
      1. Example: si connections create -path /opt/sisense/storage/[b2dstorage.yaml]

Configuring the Destination Database

The following are the instructions to configure Sisense to connect to and use the given B2D:

1. Only one destination database can be set up at this point for each given provider (e.g., one for Snowflake and one for Redshift).
2. Each destination database consists of a pair of configuration settings: One which defines the user used for the build with read/write permissions, and one for the query with read permission only.

The following is an example of a yaml file that is used in the si command to create/edit a connection to Snowflake and one for Redshift:

connections:       

- connectionString: jdbc:snowflake://companyname.us-east-2.aws.snowflakecomputing.com/?warehouse=MyWarehouse&role=WRITER
  user: WRITER
  password: Testing1234
  role: WRITER
  database: MyDatabase
  permissions: writer
  buildDestination: snowflake
		
- connectionString: jdbc:snowflake://sisensetest.us-east-2.aws.snowflakecomputing.com/?warehouse=BE&role=VIEWER
  user: VIEWER
  password: Testing1234
  role: WRITER
  database: MyDatabase
  permissions: viewer
  buildDestination: snowflake

- connectionString: compnayname.cyumewpbwr00.us-east-2.redshift.amazonaws.com:5439
  user: administrator
  password: Testing1234
  database: MyDatabase
  permissions: writer
  buildDestination: redshift

- connectionString:compnayname.cyumewpbwr00.us-east-2.redshift.amazonaws.com:5439
  user: administrator
  password: Testing1234
  database: MyDatabase
  permissions: viewer
  buildDestination: redshift

Build Configurations

Below are the B2D specific configurations that are used in the build process:

FAQ

Yaml File Examples

connection.yaml Examples

connections:

- connectionString: jdbc:snowflake://sisense.aws.com/?warehouse=EXWH&role=WRITER
  user: WRITER
  password: expassword
  role: WRITER
  database: EXDB
  permissions: writer
  buildDestination: snowflake
		
- connectionString: jdbc:snowflake://sisense.aws.com/?warehouse=EXWH&role=VIEWER
  user: VIEWER
  password: expassword
  role: WRITER
  database: EXDB
  permissions: viewer
  buildDestination: snowflake
		    
- connectionString: sisense.redshift.com
  user: exadmin
  password: expassword
  database: exdb
  permissions: writer
  buildDestination: redshift
		
- connectionString: sisense.redshift.com
  user: exadmin
  password: expassword
  database: exdb
  permissions: viewer
  buildDestination: redshift