Preparing the Destination and Storage

Configuring a Bucket in S3

Question Answer

How do I create a bucket that can be used by Sisense for B2D?

Follow the instructions provided Configuring B2D in Sisense.

Is there anything special required for setting up a bucket to be used by Sisense?

Policy/access permissions required are outlined in the bucket creation configurations instructions.

What about the original S3 configuration that is in place in the Management setup?

That configuration pertains to the historical capability for MonetDB in which S3 is used as a backup for the farm of the given cube. That capability configuration does NOT affect, nor is it used by, the B2D. This is also applicable to the Notebook configuration for S3.

The settings could be set to the same values; however, it is not recommended to mix up the features and separate out buckets' settings.

What happens if the bucket is not set up prior to building a cube?

If the bucket does not exist, Sisense will attempt to auto-create the bucket. However, it might not work if the proper permissions are not provided to allow for Sisense to auto-create the bucket. It is highly recommended that this be performed by your given AWS administrator.

Amazon S3 Setup Example

This section contains the DB configuration (different properties such as users/roles and their permissions) and storage configuration (s3 parameters, bucket permissions). For Amazon documentation, see Bucket Policies and User and Role Policies.

The authentication setup must allow all permissions for the bucket.

  1. On the top bar, select the IAM Service.

  2. On the left panel, under Access Management, click Policies.

  3. On the right side, click Create Policy.

  4. Select the JSON tab and paste the policy below into the AWS Editor. Make sure to replace the fields that are highlighted and bolded with the name of your bucket:

    {

    "Version": "2012-10-17",

    "Statement": [

    {

    "Sid": "ListObjectsInBucket",

    "Effect": "Allow",

    "Action": ["s3:ListBucket"],

    "Resource": ["arn:aws:s3:::bucket-name"]

    },

    {

    "Sid": "AllObjectActions",

    "Effect": "Allow",

    "Action": ["s3:*Object"],

    "Resource": ["arn:aws:s3:::bucket-name"]

    }

    ]

    }

  5. On the bottom-right, click Next and add any tags needed.
  6. Click Next: Review.

  7. Type a meaningful name and description for the policy.

  8. Click Create Policy.
  9. Once the Policy is created, you can attach it to Groups, Roles, or Users.

    For example, attach the Policy to a Group, since it is easier to manage several Users that belong to the same Group than attaching the Policy one User at a time.

  10. Select User groups on the left panel, and then Add permissions > Attach policies.

  11. Use the search box to find the Policy you created and click Add Permissions.
  12. If the bucket does not exist, by default Sisense attempts to create it. If there aren't permissions to create buckets, turn OFF the EnableBucketCreation flag. To set the flag on/off:
    1. Click the Sisense logo 5 times.
    2. In the Service Configuration > Build window, set the EnableBucketCreation flag.

Setting up the Destination Database

Question Answer

Is there any special recommendation when setting up the database?

Given the destination database supports the warehouse functionality, we recommend setting up Sisense in a warehouse that is separate from the existing setup. It is also recommended to setup a separate warehouse for the build and a different warehouse for query in order to make operations more efficient.

What happens if the database is not created prior to the cube build?

There is a capability in place that creates the database within the warehouse. However, if the wrong permissions are provided, this might not work.

Therefore, it is highly recommended that the warehouse/database will be created and proper permissions granted as part of the onboarding of this feature and prior to starting to use this feature to build cubes.

How do I configure the bucket to be linked to the Snowflake/Redshift database?

Follow the instructions provided for setting up the given destination database. See Snowflake Code and Setup Example.

Snowflake Code and Setup Example

  • To set up the destination database, you must be logged in to the database as a user with useradmin and account admin roles.
  • To build to Snowflake, you must define the database to build to, and define users with read/write permissions. This can be an existing database and existing users, or you can create new ones, and grant them the permissions, using the steps defined below.

Note:

If you are using existing users, make sure that the users have the correct permissions. See the Grant Permissions commands below.
  1. First create the writer and viewer users. This is done using the useradmin role.
  2. Enter their names and passwords, as well as their roles, warehouse and DB properties that will be used. (it is ok if they do not yet exist).
  3. All of these properties must be configured later under the Build (see the Destination Configuration section).

Note:

Each text that starts with "MY" should be replaced by the real field name. For example, MY_WRITER_USER should be replaced with the user name that has write permissions.

USE ROLE useradmin;

CREATE OR REPLACE USER MY_WRITER_USER

LOGIN_NAME='MY_WRITER_USER' PASSWORD='w123'

DISPLAY_NAME='My Writer User' DEFAULT_ROLE='MY_WRITER_ROLE'

DEFAULT_WAREHOUSE='MY_WAREHOUSE' DEFAULT_NAMESPACE='MY_DB';

CREATE OR REPLACE USER MY_VIEWER_USER

LOGIN_NAME='MY_VIEWER_USER' PASSWORD='v123'

DISPLAY_NAME='My Viewer User' DEFAULT_ROLE='MY_VIEWER_ROLE'

DEFAULT_WAREHOUSE='MY_WAREHOUSE' DEFAULT_NAMESPACE='MY_DB';

The following commands will require the accountadmin role:

USE ROLE accountadmin;

Creating the warehouse and database for all built data:

CREATE WAREHOUSE IF NOT EXISTS MY_WAREHOUSE;

CREATE DATABASE IF NOT EXISTS MY_DB;

Creating the roles and granting them to the users:

CREATE ROLE IF NOT EXISTS MY_WRITER_ROLE;

CREATE ROLE IF NOT EXISTS MY_VIEWER_ROLE;

GRANT ROLE MY_WRITER_ROLE TO USER MY_WRITER_USER;

GRANT ROLE MY_VIEWER_ROLE TO USER MY_VIEWER_USER;

Granting the warehouse-level permissions to the roles:

GRANT MODIFY, MONITOR, USAGE, OPERATE ON WAREHOUSE MY_WAREHOUSE

TO ROLE MY_WRITER_ROLE;

GRANT USAGE, OPERATE ON WAREHOUSE MY_WAREHOUSE

TO ROLE MY_VIEWER_ROLE;

Granting the database-level permissions to the roles:

GRANT MODIFY, MONITOR, USAGE, CREATE SCHEMA ON DATABASE MY_DB

TO ROLE MY_WRITER_ROLE WITH GRANT OPTION;

GRANT USAGE ON DATABASE MY_DB TO ROLE MY_VIEWER_ROLE;

Granting query permissions to the viewer user (the writer user will be the owner of the destination schemas):

GRANT USAGE ON ALL SCHEMAS IN DATABASE MY_DB TO ROLE MY_VIEWER_ROLE;

GRANT USAGE ON FUTURE SCHEMAS IN DATABASE MY_DB TO ROLE MY_VIEWER_ROLE;

 

GRANT SELECT ON FUTURE TABLES IN DATABASE MY_DB TO ROLE MY_VIEWER_ROLE;

GRANT SELECT ON ALL TABLES IN DATABASE MY_DB TO ROLE MY_VIEWER_ROLE;

 

GRANT SELECT ON ALL VIEWS IN DATABASE MY_DB TO ROLE MY_VIEWER_ROLE

GRANT SELECT ON FUTURE VIEWS IN DATABASE MY_DB TO ROLE MY_VIEWER_ROLE

 

If you are using an AWS environment where an access key and private key are not used for authentication, you must set up an S3 storage integration with write permissions, named:

SISENSE_INTEGRATION_MY_WRITER_ROLE

Note:

The name should be SISENSE_INTEGRATION_ and then the user role of the user with the permissions to write.

See https://docs.snowflake.com/en/user-guide/data-load-s3-config-storage-integration.html for more information.