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:

• name (string)

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

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

• 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

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

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

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

Example:

{
    "name": "test",
    "iamRole": "arn:aws:iam::Add here your role number:role/Add here your role name",
    "storageType": "AMAZON_S3",
    "bucketName": "uploaddataredshift",
    "bucketRegion": "us-east-2",
    "secretKey": "Add here your secret key",
    "accessKey": "Add here your access key",
    "storageIntegration": ""
}

Configuring the B2D Connection

1. Click the Admin tab > Rest API > 2.0.

   

2. Under Open API, click B2D Connection.

3. Select the POST /b2d-connection endpoint.

   

4. Click Try it out.

5. Create the connections for a Viewer and for a Writer:

   1. Enter the payload in the Request body:

      • Snowflake

        • Viewer

          Copy

          {
           "buildDestination": "snowflake",
           "createdByUser": "true",
           "description": "description",
           "enabled": "true",
           "name": "daniel_snowflake_viewer",
           "ownerId": "string",
           "parameters": {
            "connectionString": "jdbc:snowflake://<account_identifier>.snowflakecomputing.com/?warehouse=BE&role=VIEWER",
            "Database": "BE",
            "parameter": "allowMultiQueries=true",
            "role": "VIEWER",
            "password": "password",
            "UserName": "UserName"
           },
           "permissions": "viewer",
           "provider": "SnowflakeJDBC",
           "supportedModelTypes": [
            "EXTRACT"
           ]
          }

        • Writer

          Copy

          {
              "buildDestination": "snowflake",
              "createdByUser": "true",
              "description": "description",
              "enabled": "true",
              "name": "daniel_snowflake_writer",
              "ownerId": "string",
              "parameters": {
                  "connectionString": "jdbc:snowflake://<account_identifier>.snowflakecomputing.com/?warehouse=BE&role=WRITER",
                  "Database": "BE",
                  "parameter": "allowMultiQueries=true",
                  "role": "WRITER",
                  "UserName": "UserName",
                  "password": "password"
              },
              "permissions": "writer",
              "provider": "SnowflakeJDBC",
              "supportedModelTypes": [
                  "EXTRACT"
              ]
          }

      • Redshift

        • Viewer

          Copy

          {
              "buildDestination": "redshift",
              "createdByUser": "true",
              "description": "string",
              "enabled": "true",
              "name": "redshift_viewer",
              "ownerId": "string",
              "parameters": {
                  "connectionString": "<place your connection string here>:5439",
                  "Database": "be",
                  "UserName": "<place your Redshift user name here>",  
                  "Password": "<place your Redshift user password here>",  
                  "Parameter": "allowMultiQueries=true",
                  "role": "VIEWER"
              },
              "permissions": "viewer",
              "provider": "RedShift",
              "supportedModelTypes": [
                  "EXTRACT"
              ]
          }

        • Writer

          Copy

          {
              "buildDestination": "redshift",
              "createdByUser": "true",
              "description": "string",
              "enabled": "true",
              "name": "redshift_writer",
              "ownerId": "string",
              "parameters": {
                  "connectionString": "<place your connection string here>:5439",  
                  "Database": "be",
                  "parameter": "allowMultiQueries=true",
                  "role": "WRITER",
                  "UserName": "<place your Redshift user here>",  
                  "Password": "<place your Redshift user password here>"  
              },
              "permissions": "writer",
              "provider": "RedShift",
              "supportedModelTypes": [
                  "EXTRACT"
              ]
          }

   2. Click Execute.

   Note:

   • Create the connections one at a time. That is, first create a connection for a Viewer according to the Viewer payload above. Then, only after creating that connection (after clicking Execute), create a connection for a Writer according to the Writer payload above.

   • If the Viewer and the Writer will be different users, the user name and password must be changed accordingly.

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.

Build Configurations

Below are the B2D specific configurations that are used in the build process. The default settings are configured optimally and should not be changed. If you require changes, contact Sisense support.

FAQ

Yaml File Examples

The following are examples of configured connections in a yaml file.

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