Creating a New Connection

Click the Data Connection link and click the  button to add a new connection. There are 2 mandatory fields that you must complete when creating a new connection: Data Connection Title and User Name.

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.

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.

User Name: - the user name 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 ‘User Name’.

Object Start Quote: - for example, on some databases this might be [ or ‘

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.

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.

Please note that if variables are used in the username or password fields then connection pooling will be disabled.

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.