Working with Data Warehouses
  • 18 May 2022
  • 6 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Working with Data Warehouses

  • Dark
    Light
  • PDF

Sisense allows you to prepare your data in your cloud warehouse and then create views in Sisense based on your cloud warehouse data. Sisense’s views enable you to define a data set that you can later materialize to use as a table in your models. When a view is materialized, Sisense creates a view on your warehouse under the sisense_views schema. Your users can then add this table to your models when adding data from a data warehouse. By transforming the data directly in the warehouse prior to distribution, analysts can reduce database workloads and optimize query performance that power self-service analytics.

Note:

Creating Materialized Views requires Data Admin or Data Designer privileges and knowledge of SQL.

Activating the Warehouse Tab

Sisense Administrators must activate the Warehouse tab from the Feature Management section of the Admin page.

Note:

Since data is sent to the Sisense Cloud Service, the Warehouse tab requires that you sign a DPA and that your Sisense server has internet access. Contact your Customer Success Manager for more information.

To activate the Warehouse feature:

  • In the Admin page, select Feature Management and toggle the Warehouse switch to enabled.

Preparing Your Data Warehouse

A few steps are needed to connect your data warehouse to Sisense. The warehouse must be accessible from both Sisense Cloud and your Sisense deployment. If your Sisense deployment is Cloud Managed by Sisense, the only configuration needed is on the data warehouse.

Allow Connections to Sisense Cloud

For specific instructions to allow connections to Sisense Cloud by your Warehouse, see Preparing your Redshift Data Warehouse and Preparing your Snowflake Data Warehouse. These guides can help you ensure that your warehouse allows connections from the Sisense Cloud IP address:

54.186.74.45
54.187.196.247

Additionally, if you managed your own Sisense deployment and you have a firewall for outgoing traffic, ensure your instance has these URLs whitelisted:

https://auth.cloud.sisense.com
https://usw.cloud.sisense.com

Create Database Users

Depending on your warehouse type, the commands needed to create users and grant privileges may vary. see Preparing your Redshift Data Warehouse and Preparing your Snowflake Data Warehouse for detailed instructions.

Connecting to a Data Warehouse

The first step to creating a view in Sisense is to connect to a data warehouse where the data to be used in the view is stored. Once you connect to the warehouse, it and its tables are displayed in Sisense.

When connecting to your data warehouse, your credentials are shared with Sisense and saved and encrypted in a secure cloud server maintained by Sisense.

To connect a warehouse:

  1. In the Warehouse page, click Manage Warehouses. A list of your warehouses is displayed.
  2. Select the relevant warehouse. The Add Warehouse dialog box is displayed.
    8-6datawarethumb0300.png
  3. Enter the following information:
    Display Name: Enter the name of your warehouse. This name is displayed in the Sisense Warehouse list on the left of the Warehouse page.
    Location: (Redshift only) Enter the address of your data warehouse.
    Connection String: (Snowflake Only) Enter your connection string to your Snowflake warehouse including the Warehouse connection parameter. To create a connection string, see JDBC Driver Connection String.
    Database: Enter the name of your database as it appears in your warehouse.
    Refresh Schema Automatically: Select to refresh your schema automatically every 7 hours.
    Encrypt Connection: (Redshift Only) Select if your Redshift database supports secure connections.
    Read-Only Credentials: Enter the user name and password of a read-only database user to create interactive queries in Sisense’s SQL editor while creating views.
    Admin Credentials: Enter the user name and password of a database user with write-access as described in the Prerequisites.
  4. Click Test & Save to add your warehouse to the list.
  5. Click Share Warehouse to enable other Data Designers to view the Warehouse page and create views from the warehouse. For more information, see Sharing a Warehouse.
    OR
    Click Close. Your warehouse is added to the Warehouse list. The next step is to share your warehouse with other users or define a view yourself in the Warehouse page.

Sharing a Warehouse

By default, the Warehouse page is not accessible or displayed to Data Designers. To allow your Data Designers to work with your views, you need to share your warehouses with them. Once a warehouse is shared, the Data Designer can view the Warehouse page link at the top of Sisense, and access it.

To share a warehouse:

  1. In the Warehouse page, click Manage Warehouses. A list of your warehouses is displayed.
  2. Open the warehouse’s menu and select Share.
    8-6sharewarethumb0300.png
  3. Enter the name of the Sisense user that you want to share the warehouse with. As you begin to type, any relevant results are displayed.
  4. Click Share. The relevant users now have access to the Warehouse page and the warehouse.

Defining a View

Users who have access to your warehouse can create views that can be used when creating models in Sisense. These views are saved in your data warehouse, but they do not modify your data in any way, they only read your data to create your desired view.
8-6adddatathumb0300.png

To define a view:

  1. In the Warehouse page, click + New View.
  2. Enter a name for your view. This appears in a list of views under your warehouse in the navigation pane.
  3. In the SQL editor, enter your code. Materialized views must be written in Redshift-compatible syntax.
    Note:

    The left-hand pane contains all of the available databases, tables, and columns in your data source. Use the Search bar to find tables and columns to include in your SQL:

    • Toggle the Match selector to Tables to search for tables
    • Toggle the Match selector to Columns to search for columns

8-6match-selectorthumb0300.png
4. After you have entered your code, click Run SQL. A preview of the view is displayed below.
5. Click Save. The view is added to your list under its warehouse. To materialize the view, so that it can be used in your models, you must schedule a materialization or manually materialize the view. See Scheduling a Materialization below for instructions.

Scheduling a Materialization

By default, Sisense schedules a materialization every 7 hours. To customize your materialization strategy, click the Scheduler icon in the SQL editor displayed when creating or editing a view.

8-6scheduler.png
There are two ways you can schedule materializations, on a daily interval or a custom interval. For daily intervals, you define the time of day when the view is materialized. For custom intervals, you select the time between materializations in days, hours, and minutes.

8-6scheduler1thumb0300.png

Materialized views in your warehouse will only materialize according to your scheduled settings. To manually refresh the view, select Refresh from your view’s menu inside the navigation pane.

8-6refreshviewthumb0300.png

Managing Views

After you have created a view, you can manage it from the Warehouse page. The Warehouse page has a table containing a list of all of your warehouses. In the navigation pane, you can click a warehouse to a list of all the views for that warehouse. In the Views table, you can see information describing each view for the selected warehouse, including who owns it, its materialization status, and when it is scheduled to be materialized. There are three possible statuses a materialization can have:

8-6greenicon.png : Materialized
8-6blackicon.png : Not materialized, pending a materialization, or no SQL defined
8-6redicon.png : Failed

For each view, you can edit or delete the view from its menu. You can access the view’s menu for the navigation pane or in the Views table.
8-6newviewthumb0300.png

Profile Statistics

Sisense provides Profile Statistics that allow you to quickly understand the data you are drawing from and how that data can be broken down and examined. Distribution information is useful for identifying the shape of your data, how similar the values are, outliers and bad data. Uniqueness counts tell you if your ID columns are truly distinct or if data assumptions or query logic is faulty. Statistics can save you a lot of time, typing, and painful logic mistakes.

Note:

Profile Statistics are run only on data previewed in the browser. Data is limited by query limit, preview limit, or 5MB, whichever is binding.

8-6profilestatsthumb0300.png

Limitations

  • If on the Redshift Cache naming conventions and syntax must follow Redshift guidelines, outlined in Redshift's documentation page.
  • The view cannot contain any duplicate column names.
  • The view must not contain comments at the end of the SQL code. Note that comments can be safely placed anywhere else in the SQL (beginning or middle of the code).

Was this article helpful?