Common Rules for Creating Database integrations

Creating an integration

To create an integration with your database, please do one of the following:

• Navigate to the Data sources page, click the big blue plus button, find and choose the necessary database from the list.
• Navigate to the Library page, click the big blue plus button and choose Integration from the submenu. Find and choose the necessary database from the list.

The Integration settings window will open. It contains four tabs. Let’s consider each tab below.

General

Here you need to enter your connection details:

• Host
• Database name
• Username
• Password

NOTE: For certain databases, the connection settings may vary, e.g. for Treasure Data.

Security

Slemma connects to your database in a read-only mode. This provides an additional layer of security against malicious queries. Nevertheless, we recommend to use a read-only user for connecting to your database. Create a new read-only user, if you haven’t yet, and grant the user privileges. Please refer to your DB documentation to learn how to create a read-only user. Here is an example of creating MySQL read-only user.

In order to use CREATE, DROP and INSERT statements please be sure to reach out to your database admin to grant the needed privileges and switch off the read-only mode in the integration settings.

Redshift database disallows creating variables and temporary tables when connection is set to read-only. For this reason, we allow Redshift users to switch off the read-only mode in the Redshift integration settings window if needed. If you are seeing an error message like “Error: Transaction is read-only”, disabling the read-only mode should resolve this issue. Just deselect the checkbox for Read-only mode.

Slemma enables you to connect to your databases in two ways: directly or through the SSH tunnel.

1. Direct connection requires you to whitelist Slemma’s IP address 52.21.150.73 (configure your firewall to allow the address 52.21.150.73 to connect to your server) and enter your connection details on the connection dialogue. In other words, it requires your database to be accessible from the Internet. Besides, most databases have their own settings that allow the remote access. Remote access should be also available from a certain IP. Please refer to your DB documentation to learn how to enable remote access. Here is a quick example article on how to modify Redshift Cluster security group in order to whitelist Slemma’s IP.
2. If your database is on a private network and you do not want it to be accessible from the Internet, you can set up a secure tunnel to forward encrypted connections from our SSH server to your database server. Please head over to this article for more info.

In order to protect your data and be ensure that all data remain private and integral, we recommend to select the Use SSL option (if it’s supported by your DB server).

Data refresh

This tab enables you to establish automatic updates of your data. Select the desired frequency, time and time zone of cache flushing. Head over to this article for more info on data refreshing.

NOTE: In Slemma, we have a dataset update time limit of 900 seconds. Please make sure that running your query takes less than 15 minutes.

Advanced

In some cases, an additional configuration of a DB connection is required. For this purpose, all available driver settings which are used to create a DB connection are displayed here.

When ready, click the CHECK CONNECTION button. If the connection is successful, you will see the corresponding message:

Now you are able to click the CONNECT button.

Once the integration with your database is created, you’ll see it on the Data sources page and on the Library page as well. Here you are able to share the integration with users and groups, change the integration settings or remove it. To view the options available, right click the integration in the list.

Creating a dataset

There are several ways to create a dataset on your database integration:

• Find and click the necessary integration from the Data sources page or from the Library page, and select the Browse datasets option.
• On the Library page, click the blue plus button at the bottom right and choose Dataset from the submenu. Then choose the necessary integration from the list of available data sources.
• Choose to insert a new chart in the Dashboard editor. Next, you will have to choose where your data is coming from or create a new data source. Click the necessary integration from the list of available data sources.

You’ll be redirected to a page where you should choose one of the available tables or views from the list or create a new one by choosing the Write SQL query option. To learn more about the Dataset editor, follow this link.

NOTE: Fields with size > 2000 bytes (about 2000 signs) will be cut off when building dimensions.

Editing DB integration settings

If for some reason you need to reconfigure your database integration settings, right click it from the Data sources page or from the Library page and select the Integration settings option. Make the necessary changes, check if the connection is successful and save the changes.