How to connect to MonitorPro for onward reporting - Connecting to the Database
One of the simplest ways to extract data from MonitorPro for onward reporting systems or for general onward integration is to connect directly to the MonitorPro SQL Server database.
For reporting, we recommend the use of the report_ stored procedures (a full list is included below). 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 selectionID as detailed in the section below.
Access
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.
Where the MonitorPro solution is hosted by the client on-premise, then ensuring the required firewall acceptions and access between MonitorPro and the onward system are made is the responsibility of the client's IT. If the MoniorPro solution is hosted by EHS Data then please contact support@ehsdata.com where we will be able to provide you with an IP address for connection and a user account for authentication. We will require the client to provide a fixed IP address where the calls will be made, and a specific firewall exception will be made on our servers between these endpoints.
Returning and filtering data
Once established a connection between the two databases can be made. The stored procedures can be run, simply passing in the relevant selectionID. The data which is returned, the date range, locations, etc are all controlled by the selectionID passed in. The stored procedure simply controls the formatting, associated data, and any processing logic included within.
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.
A list of the reporting stored procedures is as follows:
• report_001_SelectedData
• report_001_SelectedLocations
• report_003_SelectedVariables
• report_003a_SelectedSources
• report_003b_SelectedSampleTypes
• report_005_Data_Basic
• report_005_Data_Basic_AdditionalFields
• report_005v_Data_Basic_VariableOrder
• report_005vs_Data_Basic_VariableSamplePointOrder
• report_005vs_Data_Basic_VariableSamplePointOrderPreordered
• report_005vsd_Data_Basic_VariableSamplePointOrderDummy
• report_005x_Data_Deleted
• report_006_Data_Extended
• report_006a_Data_Extended_ComplianceGridsOnly
• report_006a_Data_Extended_ComplianceGridsOnlyBroken
• report_006avs_Data_Extended_ComplianceGridsOnly_VariableSamplePointOrder
• report_006b_Data_Extended_AllComplianceTypes
• report_006b_Data_Extended_CompliancePivot
• report_006b1_Data_Extended_AllComplianceTypes_ImportanceWarning
• report_006b2_Data_Extended_AllComplianceTypes_ImportanceInternal
• report_006b3_Data_Extended_AllComplianceTypes_ImportanceBreach
• report_006b4_Data_Extended_AllComplianceTypes_ImportanceHighPriorityBreach
• report_006c_Data_Extended_AllComplianceTypes_Incl_Metadata
• report_007_Exceedences
• report_007a_Exceedences_ComplianceGridOnly
• report_007b_Exceedences_AllComplianceTypes
• report_008_Data_Basic_By_Group
• report_008_Data_Basic_by_Grp
• report_008x_Data_Basic_By_Group_Extended
• report_009_Data_Basic_By_Suite
• report_010_Aggregates
• report_010_DataImportedYesterday_Detail
• report_010_DataImportedYesterday_DetailWithThresholds
• report_010a_Aggregates_ComplianceGridsOnly
• report_010b_Aggregates_AllComplianceTypes
• report_011_Extended_Incl_Suites
• report_011a_Extended_Incl_Suites_ComplianceGridsOnly
• report_011b_Extended_Incl_Suites_AllComplianceTypes
• report_012_Exceedences_Raw_Data
• report_012a_Exceedences_Raw_Data_ComplianceGridsOnly
• report_012b_Exceedences_Raw_Data_AllComplianceTypes
• report_013_Data_Basic_Monitoring_Performance_Report
To obtain a lSelectionID for the required data it can be useful to connect to tblSelection directly.
You can directly locate the required lSelectionID, by using the sSelectionstorename (the common name shown to the user in MonitorPro) as a filter in your query.
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. Units can also be selected separately as Unit.
Selection End and Start Dates – This is the date range of the underlying stored selection. 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 out 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
Variations
There are a number of variations to the reporting output that can be provided. Examples have been exports of data based on the updated date of data, so data can be shared with onward systems when changed or updated within the system.
Connection to GIS is also another example where up-to-date locational information can be shared with a company's wider GIS applications.
If these or other options might be of interest please contact support@ehsdata.com