Creating a New Data Connection

On this page you will be guided through how to create a new data connection and how the parameters you can define help with making the connection. You will also be guided through how to Validate & Test the Connection are working as expected.

Creating A New Connection

Click the Data Connection link and click the button to add a new connection.

Generally, as shown below, there are two mandatory fields that you must complete when creating a new connection, these are the Data Connection Title and User Name.

However, there are a couple of 'Data Source Types' where the User Name field is optional i.e. Free Format JDBC and SQL Server. These are handled using the following parameters (see the parameters table below for more information);

Use Integrated Security
Supply Credential If Blank

Data Connection Parameters

There are several options on this screen, some of which are mandatory and identified with a red exclamation mark, but many you can just leave at the default values, depending primarily on what type of database(s) you want to connect to.

Parameter	Description

Parameter	Description
Data Connection Title	the name you want to give to this Data Connection. This field is always mandatory, and the title must be unique.
Category	this is the category that this Data Connection is assigned to. This is mandatory and, by default, will be set to ‘Home’ – which is a category that everyone has permissions for automatically. If you wanted to restrict access to this Data Connection and the charts connected to it then you could change the owning category to something more restrictive.
Data Source Type	this drop-down list shows the Data Source Types which we ship drivers for If the database you want isn’t listed, but you do have a JDBC driver for it, then you can use ‘Free Format JDBC’ to configure this, in which case the driver should be placed in {InstallDirectory}\Dashboard\tomcat\custom_jdbc_drivers
Host Name	the name or IP Address of the server where this data source is located. This machine must be ‘visible’ on the network from wherever this piDashboard server is located. The ‘Host Name’ field has a limit of 100 characters. If your host name is longer than this, you could try using a custom variable. Please take a look at this page for more information.
Port Number	the port number on ‘Host Name’ that the database is ‘listening’ on. You may need to create an incoming firewall rule on ‘Host Name’ to allow TCP/ IP traffic on this port.
Database Name	the name of the database on ‘Host Name’ that you wish to connect to.
Use SSL	for MySQL, Single Store and Sqream you can set the ‘Use SSL’ checkbox to set the flag in the driver. Consult the driver documentation for further detail on using this mode
User Name	the username configured in ‘Database Name’ that you wish to connect to the database as. This user will need to have at the very least, read permissions for ‘Database Name’.
Password	the password configured in ‘Database Name’ for ‘Username’. From the February 2023, these will be set by default for Snowflake connections.
Object Start Quote	for example, on some databases this might be [ or ‘. From the February 2023, these will be set by default for Snowflake connections.
Object End Quote	for example, on some databases this might be ] or ‘
Database Date Time Format	this is the datetime format for this database, if not populated the default format yyyy-MM-dd HH:mm:ss will be utilised. This is used for ‘Date Range’ Category Objects to resolve the end time by adding a day and removing a millisecond.
Connection Pools	if you are restricted by the number of open connections you can have in the database this will cap the number it creates. This also means that new pools will not be created when multiple charts are executed, instead existing connections will be ‘pooled’ and reused once free.
Chart Cache Minutes	by default, this is 0 (zero) meaning the chart data is not cached. If this was set to 60 then the first time a given SQL query from piDashboard was executed the result would be cached for the next 59 minutes and 59 seconds. Within that time period anyone looking at the same chart would get the same result as the original user got; the SQL would not be executed again. After 60 minutes have passed then the SQL would be re-executed, and then the clock would reset for another 59 minutes and 59 seconds. If you are connecting to a Data Warehouse for example, then it makes sense to set this to a higher number as the data is not going to be constantly changing and there is little point re-executing the SQL whilst the data is static. This setting might be worth considering if your data is only updated once a day.
Max Rows	this only applies to data tables. If this was set to 5,000 then any data tables built using this data connection would be limited to display only the first 5,000 rows retrieved. As the data in data tables can take some time to render (also depending on the number of columns) this helps address potential performance issues. This parameter does not apply to charts, if this was set to 5,000 but a given chart needed to read from 10m rows of data then it would be able to do so. This setting also applies to exporting - if a data table is exported, the number of rows included in the export will be determined by the number set here.
Query Timeout Seconds	if the chosen database supports Query Timeout then this will be applied after the time period specified in this parameter. The query would be cancelled and the piDashboard user would see a ‘Timeout’ message.
Pre Query	this can be used to define SQL commands that you might want to run prior to the dashboard executing the SQL it needs for charts. Examples might include setting session variables or flushing buffers. The Pre Query panel is only valid if the database is SQL Server.
Use Integrated Security (Windows Authentication)	to be used for SQL Server can be set to allow authentication through Windows Integrated Security i.e. use the current Windows identity established on the Operating System for verification rather than a User Name and Password.
Supply Credential If Blank	to be used for Free Format JDBC, this can be unchecked to prevent User Name and/or Password information being supplied if the field is left blank when trying to establish the data connection. This feature was added in the February 23 release.
Role	Allow Roles settings to be passed when using the Snowflake Data Connector. This feature was added in the February 23 release.

Don’t forget that you can use [[USER_VARIABLES]] and [[SYSTEM_VARIABLES]] for most of these options, if applicable, for your particular implementation.

Passwords and Usernames can be held in a [[VARIABLE]] but the ‘Keep Secure’ box MUST BE ticked. You will not be able to use these variables in other areas of the dashboard such as in charts.

Variables can be flagged as secure and used for database username and password. Secure variables will not work in locations other than those.

Use of secure variables for database username and/or password will disable any connection pooling for that connection. To improve security and facilitate a smooth upgrade any global variables used in data connection usernames will be upgraded to secure variables.

Validating a Data Connection

After you have configured the relevant connection options, click the ‘Save’ icon. This will save and validate the Data Connection.

If the following message is displayed, the Data Connection has not been validated.

If the following message is displayed, the Data Connection is valid.

Selecting an existing data connection in the left-hand list will allow you to edit that data connection instead.

Testing a Data Connection

Alternatively, if you just want to check your connection is valid before saving you can click the test connection icon. This will display one of the messages displayed in the previous section based on whether the credentials could be validated.

Failed Data Connections

If a Data Connection fails, a red notification message will be displayed in the bottom-right corner of the dashboard.

Click the notification icon to view the error message.

When the error has been dealt with, click the ‘Delete’ option beneath it to remove the message.

If you have more than one message to remove, click the ‘Clear All Errors and Close’ icon.