When troubleshooting the Intuitive for SAFEQ 6 solution, issues typically arise around the data capture of the PostgreSQL or SQL Server targets.
Architecture
Before we get stuck in, let’s remind ourselves of the architecture of Intuitive for SAFEQ6.
We retrieve data from a target database, again either PostgreSQL or SQL Server via an ODBC connection.
When we access that data, it’s through our ETL service. This service can either be located on the same server as SAFEQ (colocation) or as a standalone implementation on a server on the customers network.
We access that database through a set of credentials stored in the ODBC connection on the Intuitive server (collocated or standalone) which are also stored in the ETLService.config file.
That set of credentials should have permissions to read (not write) from that target database, as we insert the contents into a SQLite database, so we perform the day-to-day operations of the interactions of the dashboard software away from a production database.
That data import is performed once per day, and is performed in the early hours of the morning, typically when the target server is going through its maintenance cycle or when users are not likely to be accessing the SAFEQ 6 service.
With the SQLite database updated, the dashboard software accesses that SQLite database through another ODBC connection. That SQLite database is used to populate the datafeeds, which have their own schedule, which allow data to fill out the premade dashboards.
The datafeed schedules are usually an hour or so after the initial load into the SQLite database. This is to give the ETL time to fully ingest all of the data, but again they run in the early hours of the morning.
You may have noticed a secondary Intuitive database, but this just contains the application data. Such as which users can sign in, their roles, where components are located onscreen etc.
With all that in mind, it may have started to stir where issues may arise during the data import process.
The best place to start, is at the start itself.
SAFEQ6 checks
If you open SAFEQ6, you can check through the logs in that software for the last available dates of print activity. That can be used as a basis for what SAFEQ is presenting you, vs what Intuitive is presenting to you.
If there is a data mismatch by more than a day (again, bear in mind that we load data once per day in the early hours of the morning), then that is an indication to move firther down these troubleshooting steps.
One area to check before we move on, is to check that the SAFEQ 6 software has been fully licensed, as data will not import correctly when your SAFEQ 6 software is unlicensed / expired.
Checking logs
Moving on to the Intuitive side, we can check the Scheduler log files to check for any important information.
These log files will present any errors in the structure of the target database. Meaning, if you’re having issues with SQL Server, the errors will be in a structure that SQL Server admins will be familiar with. Similarly, this is also the case for PostgreSQL.
If there are any errors in these log files from the date you’ve noticed the data is not updating, they may be fairly self-explanatory. It’s usually a case of either the credentials you are using are invalid, or the target database is unreachable.
ODBC Checks
Let’s start with the former, if the credentials are incorrect, you can open the ODBC connection to your target SAFEQ6 database and test the connection to prove the credentials are correct. If they are not, you will need to update them on this page.
If the ODBC connection is not able to be configured / tested, the target driver used may not have been available prior to installation. You will need to ensure that the driver it is using has been installed, and then you can proceed with another data load.
Regardless of the Test Connection, you will need to check that the ETL Service config file has the correct credentials listed in there too. Those credentials will need to be identical to the ODBC connection.
The ODBC/ETL user will need read permissions on the target database, which can be checked by logging into the target database as that user via SQL Server Management Studio (SSMS), or pgAdmin, depending on target database type.
Database Checks
Checking if the target database is accessible is the next step from this, again we need to use either of the aforementioned tools to sign in.
You’ll need to ensure that the ODBC target is the exact same as where the SAFEQ 6 data is located. For example, if your connection is to SERVER01, the database of SQDB6, to the data warehouse of dwhtenant_1, you’ll need to ensure that the target database follows that same structure. If any of them mismatch, e.g. the data is on a different server, the database has been renamed or the data warehouse has moved, you will need to alter the ODBC target to suit.
In the event of that happening, you will also need to alter the nominated target database/tables in the full_load.SQL script.