Data Engine on Your Warehouse - Redshift

👤 This documentation page is intended for customers who are connecting their own Redshift database into Sisense for Cloud Data Teams as a writable destination. If you have a Snowflake database for this purpose, please see the documentation page here.

Sisense for Cloud Data Teams normally only requires read access to your warehouses. In order to use Data Engine on Your Warehouse (DEYW), Sisense also requires read-write access to parts of your warehouse. To avoid any accidental issues, it's important that you carefully grant write permissions to only the necessary resources. See the Acknowledgements section at the bottom of this page for more information.

The following instructions will walk through establishing a Redshift database connection via a read-user, and then defining and applying a write-access user.

1. Enforce SSL

The Redshift cluster must force SSL upon connections. This is a hard requirement, as unsecured connections are not allowed. To configure the cluster to require SSL, see the documentation page here:

1. https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-ssl-support.html

2. Prepare an Uncached Space

“Data Engine on Your Warehouse” will only work on sites/spaces that do not have a cached database. A quick way to check whether a space is cached is to see if the “Cache” tab is available underneath in the “Settings” gear icon:

If the site is not cached, then proceed to Step #3.

If the site has the Sisense cache, then a new space must be created. This new space will be used for all subsequent steps. By default, the new space will cache upon connecting a database. After completing Step #2, please contact Sisense Support to un-cache that space. In the interim, you may proceed with the following steps.

3. Connect the Redshift Database (Read User)

If the Redshift database has not already been connected within the Sisense account, the following documentation page walks through how to add it:

1. https://doc.periscopedata.com/article/connecting-to-periscope-menu#Whitelist

The database should be connected in the same space verified from Step #2. The database user must have read-access at a minimum. Sisense recommends that this be the same database that the user will query and build charts against.

Note: It is possible to configure the same Redshift database across multiple spaces for DEYW purposes. If this is a use-case you would like to support, please keep the following in mind:

• All materialized views and CSV’s are stored in a “periscope_views” schema. Views and CSV’s cannot share names across spaces with the same DEYW Redshift database.
• If the same READ user is used in more than one space, it will be able to access materialized views that are not present on that specific space via the SQL editor.
• If different WRITE users are used, you will need to GRANT the same permissions for that user that were granted for the others.

4. Prepare an Admin User

In order to allow view materialization from within Sisense, it is necessary to configure a database user with the proper level of write-access (Step #5).

The following credentials are required to properly create and grant permissions to such a user:

1. Admin username
2. Admin password
3. Database name (the same name as the origin Redshift database connected in Step #3)

Do not proceed unless the above credentials are verified and available for use in Step #5.

5. Create and Permission Write User/Group

The following commands must be run via an admin with the credentials from Step #4. When performing these commands, it is important to verify that the password is secure for your organization, and that the commands are being executed on the same database configured in Step 3.

Create WRITE Access User

CREATE SCHEMA periscope_views;
CREATE USER <sisense_write> PASSWORD '<password>';
GRANT USAGE ON SCHEMA periscope_views TO <sisense_write> WITH GRANT OPTION;
GRANT CREATE ON SCHEMA periscope_views TO <sisense_write>;
# <referenced_schemas> should include any schemas you will reference tables or views from.
GRANT USAGE ON SCHEMA <referenced_schemas> TO <sisense_write>
GRANT SELECT ON ALL TABLES IN SCHEMA <referenced_schemas> TO <sisense_write>;

Grant READ User Privileges

Note: This user should be the same database user connected in Step #2.

GRANT USAGE ON SCHEMA periscope_views TO <sisense_read>;
GRANT SELECT ON ALL TABLES IN SCHEMA periscope_views TO <sisense_read>;

# optional -- replace <schema> with whatever schemas the user should have access to
GRANT USAGE ON SCHEMA <schema> TO <sisense_read>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO <sisense_read>;

(Optional) Define Group for User Management

To simplify the complexity of permission management, admins can control permissions via user groups rather than individual users. You can create a group with both the read and write access users included in it, and perform future permission changes on just the group:

CREATE GROUP <sisense_group> WITH USER sisense_write, sisense_read;

6. Apply the Write User

Click on the “Settings” gear icon in the lower-left corner, and navigate to the same “Database” page you used to connect the database in Step #3. If there is a drop-down available, ensure that the database from Step #3 has been selected.

At the bottom of the form, there should be a section to input the “Warehouse Admin Credentials”. This will be the username and password configured in Step #5. Fill in the text boxes with the correct credentials and hit the “Submit” button.

Acknowledgements

Capitalized terms used herein shall have the meanings assigned to them in database language.

“DEYW Service”: Sisense'’s Data Engine on Your Warehouse (“DEYW”) Service allows and offers a User, in addition to, connecting an origin Database where they can only QUERY that database; bring User’s choice of warehouse, where User can leverage Sisense’s technology to extract tables from other sources, and MATERIALIZE view into said warehouse.

“USER”: Any entity, including but not limited to, a prospect or an existing customer of Sisense allowing Sisense access as set forth herein to perform the DEYW Service.

“Sisense”: The Service Provider for DEYW Service.

“READ”: The ability to DISPLAY, LIST, VIEW.

‘WRITE”: The ability to modify, to the extent that such command shall permit but are not limited to, ADD, CREATE, DELETE, RENAME, UPDATE.

As expressly set forth herein, User grants Sisense READ, and WRITE permissions in order to perform the DEYW Service. This permission allows Sisense to READ existing schema, tables, and rows and allows Sisense to WRITE a schema, WRITE tables within that schema, and WRITE to those tables within User’s data warehouse of choice.

By virtue of executing the aforementioned instructions and providing Sisense READ, WRITE access to your warehouse, you expressly accept the DEYW Service governed by Sisense's Terms & Conditions.