Preparing the Destination and Storage
  • 23 May 2022
  • 4 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Preparing the Destination and Storage

  • Dark
    Light
  • PDF

Prerequisites

  1. Sisense version L2022.5 onwards.
  2. Requires an Intermediate Storage with Write permission: 
    • Supported Storage: Amazon S3 for Redshift and Snowflake CDWH
    • Amazon S3 Storage with Write permissions to a bucket
    • Configure the intermediate storage, which is required for Build to Destination as a temporary storage before transferring it to the CDWH.
      To set up the storage, see Amazon S3 documentation.
      For example, Amazon S3 Buckets should be added, or add the required permissions for Sisense to create buckets. See Amazon S3 Setup Example.
  3. Requires a CDWH for Data Model Management and as Query layer for Live connectivity
    1. Supported Destinations: Snowflake, Redshift
    2. Prepare a CDWH Destination, the Destination database for the Build process and eventually the Source for the Dashboard and queries sent to the B2D model. 
    3. For these purposes will define two connections to work with, a Reader and a Writer. This is required for security reasons and in order to utilize costs:
      1. Reader - is For executing Read queries, for example, queries that are being sent from the Dashboards 
      2. Writer - For executing the write and manipulate related queries as part of the build process 
      3. This separation is required to Govern the Users permissions to perform actions on the CDWH 
      4. To allow the flexibility to choose the Warehouse to perform which task (Read or Write). A common strategy is for Write and Data Manipulation purposes to choose a small warehouse, while for the Reader and Query tasks choose a larger Warehouse
    4.  Required configurations on the CDWH:
      1. Create or use an existing Database. For reference “MY_DB”
      2. Create or use an existing Warehouse. For reference “MY_WAREHOUSE” 
      3. Create or use an existing User for write tasks. For reference “MY_WRITER_USER” with:
        1. Permissions to Add/Remove Schemas in Database “MY_DB”
        2. Permissions to perform all commands (DDL, DML, DCL, TCL, DQL) on Database “MY_DB” in generated Schemas
      4. Create or Use an existing User for read tasks. For reference “MY_VIEWER_USER” with Permissions to Query Database “MY_DB”
      5. On how to apply the above on each Database, please refer to the relevant CDWH documentation. See Snowflake Code and Setup Example.

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).

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 it in the AWS Editor. Make sure to replace the fields that are highlighted and bolded below with the values that you require:
  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.

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;

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.


Was this article helpful?