Data Sources and Connections

Creating A New Data Connection

This is where we configure the data sources that we want piDashboard to connect to and build charts against.

At the top-right of the dashboard, click the ‘Dashboard Configuration’ icon.

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.

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.

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.

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.

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.

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.