How to connect Tableau to MonitorPro
Introduction
Tableau is a Business Intelligence (BI) tool that allows users to visualise their data. As with many BI tools there is a need to connect to an external data source(s) to achieve this. Data held within MonitorPro can be accessed by Tableau and dashboards and other output created with data stored within. To do this a connection to the underlying MonitorPro database is required. This document is a basic guide to assist MonitorPro users and administrators to connect to MonitorPro using Tableau. It gives some examples of data presentation but is not a guide on the use of Tableau or is designed to replace any Tableau manuals or documentation.
This guide was created using Tableau Server Version: 2020.2.2 (20202.20.0614.2338) 64-bit Windows and connecting directly to the MonitorPro database running on Microsoft SQL Server 2017.
Database Connection
From Home navigate using the side menu to External Assets. Add a new Workbook and configure the connections on the Connect to Data tab under Connectors. Choose Microsoft SQL Server and then enter the Server name. You can enter a specific database or if you have access to multiple databases on the same SQL instance leave it blank (where you can then select them later on for reporting).
To authenticate you will need either a valid Windows Account or SQL username and password with access to the database(s) in question.
Users can be created for this purpose or existing credentials with access to MonitorPro can be used. If a dedicated account is to be used, then the ‘Monitor Reporting’ role should be allocated. This role has limited access to particular tables and functions specifically used for outbound reporting and can’t access other system tables not typically required.
For reporting, we recommend the use of the report_ stored procedures. These stored procedures have been specifically designed to output data in a number of formats useful for onward reporting. They contain all the basic fields required such as Location, Sample Point, Variable/Parameter, Data Source, Date/Time etc. They are named in such a way as to give a good indication of the fields included, however you can connect and preview the data to confirm they contain the fields you require. If you are unsure or believe you need custom fields not contained in these stored procedures please contact support@ehsdata.com.
You can connect to tables in the database directly but as the database is relational and the data normalised, without comprehensive knowledge of the database schema, even basic table linking could be onerous. There is a limited need to do this when using the stored procedures, as the work has been done for you. The only direct table linking required is in obtaining a lSelectionID as detailed in the section below (if you don’t at this stage know your required lSelectionID, it's worth reading the below section first before proceeding).
We would recommend users start by using report_005_Data_Basic. Simply drag this stored procedure into the ‘Drag tables here’ window. On doing this you will be prompted for a lSelectionID. This parameter enables you to pre-filter the data from MonitorPro before further refining it in Tableau. Users of MonitorPro will be familiar with the concept of Stored Selections or Datasets and how they can be created and updated. This approach in Tableau gives a shortcut to accessing the data required without returning the entirety of the database.
SelectionIDs will not change and the data presented will continue to represent that contained within the Dataset (previously known as Stored Selections). Datasets used for this purpose should contain dynamic date ranges or broad date ranges to retain current data. They should be broad enough to allow for further refinement, but not too large to impact long-term performance. Additional Datasets can be made by the users and added to the Business Intelligence tool at any point.
Once the connection has been set up you can preview the data.
It’s likely useful to rename the connection to include the details of the data it contains (i.e. the contents or name of the Dataset used to refine it.
This process can be followed multiple times, refining each connection to a different underlying Dataset. This can then make further reporting and filtering much easier than using Tableau alone to filter out the entire database to locate data for specific locations or variables/parameters. How many Datasets you use and how broad these are would be specific to the output you are looking to create.
Within Tableau, you can then easily choose the required data in your sheets.
Obtaining a lSelectionID
To obtain a lSelectionID for the required data it can be useful to connect to tblSelection in Tableau (or this can be done directly in the SQL Database). The below explains the process for creating a Workbook in Tableau for future reference and lookup of any required SelectionIDs.
First, make a connection as above. From here you can search for tblSelection, then drag this table into the pane.
From here you can either directly locate the required lSelectionID from the data preview, by using the sSelectionstorename (the common name shown to the user in MonitorPro). Or if required you can further refine the details using the Tableau functionality to filter and search the sSelectionstorename as required to obtain the lSelectionID.
Data Fields
The report_ stored procedures will give you in almost all cases the fields you require, even those titled as basic. Below are the field names of the common items found. The naming in most cases is self-evident and common to any user of MonitorPro. A number of fields will generally have a long and short variant and will relate to the long and short names in MonitorPro.
Values – Values are stored as a numeric field Data_Value and a text field Data_Text_Value. Choose the field applicable to the output (for example graphing would use the Data_Value).
Site_Long_Name and Site_Short_Name – The parent grouping of Sample Points aka as Location/Monitoring Type. Location can also be used as a shortcut combining the Site and Sample Point Short name.
Sample_Point_Long_Name and Sample_Point_Short_Name – The individual locations where data is stored
Variable_Long_Name and Variable_Short_Name – The substance being monitored aka parameter. Measure is a useful shortcut as it’s a combined field of Unit and Variable. Unit can also be selected separately as Unit.
Dataset End and Start Dates – This is the date range of the underlying Dataset. These can be set to dynamic or fixed ranges in MonitorPro.
Data_Source_Name indicates the Data Source of the data. It’s used to differentiate the source of data (field or lab for example). The data in the selection may already be refined or data can be further refined in Tableau using this field.
Sample_Date – Is the date and time of the value
Filtering on Data Source
If required, below is an example of filtering data based on excluding data for a particular Data Source.
Including Breach/Exceedance Data
A number of the stored procedures contain compliance-based data, such as the applicable compliance associated with them and if a data point is breaching a limit. The example below includes this information.
This gives options to filter on breaches, count the number of breaches present, or further refinement to include the name of the compliance.
Further examples
Below are some examples of how data might be presented using particular fields.
Dust data used as a simple summary.
Dust data presented as a time series.
