Documentation

Connect Database

Before you start connecting your database please make sure that datapine supports the connection to your type of database and that you have whitelisted our IP addresses (83.141.3.28 and 83.141.3.29). 

 

Start the Database Connection

 

  1. Click on Settings in the right upper navigation bar and you will be redirected to the data sources tab.
  2. In the Add a new data source widget, click on the Database button. This will guide you to the Database Connection screen, where we will ask you to enter your connection details.  Please follow the steps under Setup to start connecting your database.

Setup

 

On the setup page, we will ask you to provide your database credentials so we can establish a connection to your database. Please follow the step by step instructions below to learn how to setup your database connection.

 

database-setup

 

 

  1. Please choose an internal name for your data connection. This database name will appear in the data source structure in the tool and helps you to identify this specific data source.
  2. Additionally we will need the actual name of your database to setup the connection.
  3. Select the database type from the dropdown menu.
  4. Please provide the IP address or domain name of your database server.
  5. Make sure you have created a user that has access rights to your database and that we can use to connect your database to datapine. The user should have read-only permission and for analytical purposes be allowed to create temporary tables.
  6. Type in the password corresponding to the username provided above.
  7. Please add a database port number. This is optional and despite your provide a port we will use a default port depending on the database type you have selected.
  8. If you would like to add any additional JDBS URL Parameters such as SSL settings or character encoding settings please use the following format: ?sslmode=require&ssl=true& sslfactory=org.postgresql.ssl.NonValidatingFactory
  9. Choose if you want to setup a SSH Tunneling Connection. If your database is on a private network and you want to setup an SSH tunnel for this connection please see Setup SSH Tunneling Connection for detailed instructions.
  10. Please select Import existing database structure to connect your current database. If you want to import a SQL View please follow the documentation for SQL Views.
  11. Choose whether you want to connect your database remotely or transfer your data to our data warehouse. See Data Warehouse vs. Remote Connection for more information.
  12. To save your current database credentials and continue with the data import or connection setup click on Save and Proceed.

Import

 

After you have successfully entered your connection details you will proceed to the import overview. In the following you will learn how to customize the data schema displayed in datapine.

 

database-import

 

 

 

  1. You can easily customize the data import by selecting the tables and fields you want to use for your data analysis. We recommend to only import the tables necessary for your analysis. Note that you cannot deactivate fields that are part of foreign keys.
  2. If your database is ready for import click on Save and Proceed.

Note: If you add a new table or field to your database you will need to fetch the database schema and run the data extractor to populate these changes to the data source structure in the Chart Editor.

 

References

 

The next step of setting up a data connection is to define foreign key relations. In the Reference overview you can review your existing foreign keys in your database and add new connections between your tables.   A foreign key is a relationship between two tables of your database that allows you to query and combine data from multiple tables. This function is particularly interesting in case you want to run cross-database queries too.   datapine tries to detect the existing references in your database and automatically adds them to the list of foreign keys. This does not always work and you might want to add or delete them manually. Please see Define Foreign Key sto learn how to set your foreign keys manually in datapine.

 

Label Editor

 

After you have successfully set your foreign keys, you can now change the name of your fields appearing in the tool. This can help to make field labels more meaningful to other users and avoid cryptic labels in your data visualizations without changing the table or column names in the underlying database structure.

 

label-editor

 

 

  1. Click on the table that you wish to relabel. On the right next to the table names you will now see the original labels of your columns, internal labels as well as the data types of your fields.
  2. The original table names are shown in bold. If you wish to change the name of the database table please type a new name into the field next to the original label.
  3. If you wish to change the name of a column in your table please enter the new label into the field next to the original column names. You also have the option to change the data type of the individual fields by clicking on the dropdown menu in the column data types and selecting the preferred data type.
  4. After editing the field labels of your database click on Save and Proceed to start the data transfer.

Data Transfer

 

In case you have selected a remote connection you will get a notification as soon as the setup of the new connection was successful. You can now proceed to the Chart Creator to start visualizing your data.

 

If you have chosen to transfer your data to our data warehouse you will need to trigger the data transfer by clicking the Start data transfer now button in the center of the screen. This will execute the extraction of your selected tables and fields into our data warehouse. This process might take a while depending on the size of your database. After the data transfer was successful, you can start analyzing your data in datapine. Please note that the data in our data warehouse will be automatically updated every 24 hours. You can also use the Data Extraction Assistant to optimize this extraction process if necessary.