How to connect your database to Powerslide?

Dernière mise à jour : 02/09/2021

In a world where it only takes a few seconds to get the information you want with a good Internet connection, it is essential for a company to be able to read, analyze and understand its data in a simple and fast way. Indeed, whatever the size of a company, it is important to be able to quickly adapt its strategy according to its results. As the data of a company is stored in databases, keeping a very short delay between the data collection and its analysis is a real challenge.

To help you meet this challenge, Powerslide allows you to create a connection between your database and the app. This will allow you to have up-to-date data that can be easily analyzed via your presentations.

In this article, we will help you determine if it is relevant for you to connect your database to Powerslide, then we will explain how to create this connection in a few clicks, and finally we will show you how to use this connection wisely. At the end of this article you will find the video summarizing how to create your connection, your data source and how to use them.

Connect my database to Powerslide

Is it appropriate to connect my database to Powerslide?

Before connecting your database to Powerslide, you should consider several parameters such as:

  • The confidentiality of your data
  • How often your data is updated
  • The accessibility of your database server

Indeed, when you connect your database to Powerslide, you must be aware that you are granting a read access to your database. So you cannot connect your database to Powerslide if it contains sensitive data for privacy reasons. Under no circumstances does Powerslide read, use or disclose your data outside the strict requirements of your dataset. But you should still make sure that this does not violate your data policy. Keep the General Data Protection Regulation (GDPR) in mind, the data exposed must not your customers or employees to be identified.

Also, if the data you want to use in your dataset is not going to change regularly, you need to evaluate the usefulness of creating the connection to your database. When Powerslide is connected to your data, it will send a query to your database every time the created dataset will be used. So there is no need to create such a connection if your data does not need to be updated in real time on your dashboard or reporting.

Another conclusion follows from this information: if your database server is not accessible from an external network, you will not be able to use the connection to your database to import your data into Powerslide. But don't worry, you can use other ways to get your data into the application.

However, if you need to create a dashboard that allows you to know for example your Key Performance Indicators, your business performance or your inventory status in real time, so that you can adapt your business strategies, the database connection was made for you!

Now, let's assume that all conditions are met for you to connect your database to Powerslide and let's see how to do it.

How do I connect my database to Powerslide?

Which databases are compatible with Powerslide?

To make it easier for you, we have developed some connectors according to our users' tools. Currently, we integrate the following databases:

  • PostgreSQL
  • MySQL
  • MariaDB
  • ClickHouse
  • MS SQL
  • Oracle
  • Snowflake
  • Denodo

If you have a particular need, please contact us and tell us about your use case.

How do I set up my connection?

To create the connection to your database, you will need to have the settings of your database at hand. The necessary information depending on the connector you want to use are:

  • For PostgreSQL, MS SQL, Oracle, Snowflake and Denodo:
    • The name of the database
    • The host address
    • The database schema (optional for PostgreSQL, MS SQL, Oracle and Denodo)
    • The access port to the database
    • The authentication credentials
  • For MySQL, MariaDB and Clickhouse:
    • The name of the database
    • Its host address
    • The access port to the database
    • The authentication credentials

Connection settings description

 

To add a connection, in the Powerslide app, go to the Datasources section, click on the SQL Query button, and then click on Add connection. Enter the settings corresponding to your database. You can then test your connection by clicking on Test connection. Finally, click on Save and you're done!

GIF connection creation

How to create the ideal dataset with SQL queries?

To illustrate this article, we will use a MySQL database having this structure:

Database structure

What is an SQL query?

A database allowing to store our data, we need a way to read, write, update and eventually delete these data. We then say that we make a query on the data. SQL (Structured Query Language) is a computer language that allows us to perform these actions. It is also the most widely used query tool.

However, when you connect your database to Powerslide, the application will obviously not update your data. It will only read it to allow you to create your datasource. The reading of a database is done via "SELECT" queries which are structured as follows:

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...
...: list of the fields you want to include into your dataset
...: table name
WHERE: to filter your data
...: filter condition
GROUP BY: to group your data
...: field by which to group your data
HAVING: another way to filter your data
...: condition
ORDER BY: to order your data
...: ASC for the ascending order, DESC for the descending order (By default the result is ordered by the ascending order)
LIMIT: to get a limited number of data
...: limit number

You can then combine several of these queries to create joins between different tables in your database. To know everything about MySQL databases, you can follow this tutorial.

How to write my SQL query in Powerslide?

After setting up the connection to your database, you can write your SQL query in the dedicated part of the interface:

query interface description

To create your dataset, enter your query in the SQL Request section, then click on the Execute button. Please note that you should prefix the name of your fields with the name of the corresponding table if there exists fields with the same name in different tables, and not put a semicolon at the end of your query. If you have to bring back many fields from different tables in your database, your SQL query can become quite long and incomprehensible when written on a single line. The button allows you to format the query to make it easier to read.

As you write your query, you will notice that the query interface also has auto-completion:

auto completion on SQL query

Depending on what you write, you will be given different options. You can choose one of the options with your mouse or by using the navigation arrows on your keyboard and the Enter key, or keep typing your query if none of the options are suitable.

The result of your query will be displayed in the Results area in the form of a table. If after pressing the Execute button you see this error message:

Error 404 - Table doesn't exist

It means that the database name is missing before your table name. For example:
If I write "select * from etalab_dvf" instead of "select * from ods_public_dataset.etalab_dvf".

If you get this error:

Error 400 - Syntax error

It means that you added a semicolon in your query. Delete it before trying to run your query again.

But if you get this message:

Cannot convert undefined or null to object

It simply means that there is no row in your database that matches the SQL query you wrote. This is probably an error in the filtering of your data.

Once your query has been executed, all that remains to be done is to check that the data obtained is indeed the one you want to have in your dataset and click on Create datasource. You will be automatically redirected to the Powerslide Datasource interface. And that's it, your datasource is created. Now all you have to do is integrate your data into your dashboards and reportings.

GIF use created connection

If you want to update your SQL query after creating your datasource, you just have to open this datasource, switch to edit mode and click on the button. For the new query to be validated, the structure of the datasource must remain the same. In short, you will have to keep the data columns already present in the dataset and keep the type of these columns, but you can add new columns at the end of your datasource.

 

In summary,

Creating a direct connection between your database and your data presentation tool can be a huge time saver after having checked that it does not violate the data policy of your company. Powerslide allows you to set up this connection, get the data you need to create your dashboards or reports, put them in graphical form and all this, in a few clicks!

You too, test the power of a connection to your database: