Profit Diagnostix Integration: EXACT Dental Software (Software of Excellence)

Tutorial to set up a Profit Diagnostix Connector on a EXACT Dental Software server.

Overview

In order to allow Profit Diagnostix to build its Business Dashboards, relevant data tables like invoices and products have to be transferred from the EXACT server to the Profit Diagnostix server. To do this Profit Diagnostix provides a connector software that ensures a efficient encrypted data transfer.

Since the connector can not connect to the EXACT database directly, the EXACT data needs to be exported into a MS SQL EXPRESS database which then the allows the connector to access and transfer the data.
EXACT already provides a tool called “SoeAdmin” that allows to export (upsize) database tables into MS SQL database.

Requirements:

The personalized connector software provided by Profit Diagnostix as a download link.
The soe administration tool has to be installed usually under
- C:\Program Files (x86)\Software of Excellence\EXACT Server\SoeAdmin.exe

Steps:

Installation MS SQL EXPRESS
Installation Profit Diagnostix Connector Software
Configure SOE Admin Tool
Create Upsize Databases on MS SQL EXPRESS Server
Test Upsize process
Setup scheduled task for SOE Admin Tool
Send SQL SERVER Credentials to Profit Diagnostix support

1. Installation MSSQL EXPRESS:

Download and install MSSQL EXPRESS from Microsoft (free)
- https://www.microsoft.com/en-us/download/details.aspx?id=55994
- Configure
  - Disable Azure Extension for SQL Server
  - Disable SQL Replication Feature
  - Use default instance name (MSSQLSERVER)
  - Authentication mode: mixed
  - Set a strong password for user ‘sa’
    Keep the password for user ‘sa’ for later.
- During the installation the connection string is shown. It contains the name of the server instance, usually localhost\SQLEXPRESS. Keep the instance name for later.
Make sure TCP is enabled (“SQL Server Configuration Manager”)
Make sure that the following services are running and startup type is set to Automatic:
- SQL Server (SQLEXPRESS)
- SQL Server Browser
Make sure that the “sqlcmd” (try run in Poweshell) command line tool is installed. If not it can be downloaded from https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility

2. Installation Profit Diagnostix Connector:

Download and install the personalized Profit Diagnostix Connector. The download link gets provided by Profit Diagnostix.
Make sure that the following service is running and startup type is set to Automatic:
(Note: Slyncy is the product by Profit Diagnostix used to synchronize data between the practice management system an Profit Diagnostix)
- Slyncy Agent

3. Configure SOE Admin Tool

Create a new folder c:\SoeDump
Create a new file dump.bat with the following content:
(Note: Change the paths according to your setup)
- "C:\Program Files (x86)\Software of Excellence\EXACT Server\SoeAdmin.exe" AutoSQLUpsize "c:\SoeDump\SoeAdminCfg.xml"
Create a new file SoeAdminCfg.xmlwith the following content:
(Note: Change the paths according to your setup)
- <?xml version="1.0" encoding="ISO-8859-1"?> <ReportingViewsConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="ReportingViewsConfigurationSchema.xsd"> <databaselist> <database> <name>Slyncy</name> <parameters> <ISAMDB>Practice</ISAMDB> <ISAMDBLocation>C:\ProgramData\Software of Excellence\EXACT Server\data</ISAMDBLocation> <ISAMServerName>localhost</ISAMServerName> <UseDbServer>true</UseDbServer> <SQLServer>localhost\SQLEXPRESS</SQLServer> <SQLUpsizedDB>SoeDumpUpsize</SQLUpsizedDB> <SQLReportingDB>SoeDumpTemp</SQLReportingDB> <UIDCOTMapping>false</UIDCOTMapping> <CheckData>false</CheckData> <IncrementalISAMToSQL>true</IncrementalISAMToSQL> <IncrementalISAMToSQLMethod>CreatedUpdatedTimeStamp</IncrementalISAMToSQLMethod> <IncludeDeletedRecords>true</IncludeDeletedRecords> </parameters> <phase> <ISAMToSQL>true</ISAMToSQL> <ReportingDBPopulation>false</ReportingDBPopulation> </phase> <tables> <table>DEBTOR4</table> <table>DSERV4</table> <table>TRANS4</table> <table>INVLDNT5</table> <table>EMAILAD</table> </tables> </database> </databaselist> </ReportingViewsConfiguration>

Make sure that in dump.bat the path to SoeAdmin.exe is correct.
Make sure the <ISAMDBLocation> matches the Exact data location on the server and the <SQLServer> matches the instance name of the sql server.
(Note: To get the the Exact data location execute SoeAdmin.exe and click on “Practice” on the left. The “Directory” property on the right shows the data folder. “Practice” is also the <ISAMDB> value in SoeAdminCfg.xml and could be different)

4. Create Upsize Databases on MS SQL EXPRESS Server:

Open a PowerShell
- Execute SQLCMD -S "localhost\SQLEXPRESS" -Q "CREATE DATABASE SoeDumpUpsize"
- Execute SQLCMD -S "localhost\SQLEXPRESS" -Q "CREATE DATABASE SoeDumpTemp"
- Note: Make sure that the SQL EXPRESS server instance is matching your installation

5. Test upsize process

Execute c:\SoeDump\dump.bat
Wait until finished (command line window is closed, can take a while)
Check the SoeAdmin log files in the directory of the EXACT Server for errors, usually
C:\Program Files (x86)\Software of Excellence\EXACT Server\LogConfigParseSummary.html
and
C:\Program Files (x86)\Software of Excellence\EXACT Server\PracticeLog.html
If the ! Database conversion failed. Error 6. Slyncy (LogConfigParseSummary.html) and Reporting database population failed
Could not find stored procedure 'spSOEI_RunMain'. (PracticeLog.html) appears in the logs means that the reporting view was not installed on Soe. The upsize should have worked anyway (also see PracticeLog.html) so this can be ignored.

6. Setup scheduled task for SOE Admin Tool:

Open the “Task Scheduler” and create a new task “SoeDump” and set
“Trigger” to 2 o’clock daily
“Action” to start c:\SoeDump\dump.bat

Send SQL SERVER Credentials to Profit Diagnostix support:

To finalize the Integration in Profit Diagnostix we need the instance name of the MSSQL EXPRESS server and the password of the ‘sa’ user set during the installation. You can send this to support@profitdiagnostix.com or directly to our support staff.
As soon as we successfully tested the connection and verified the data quality we send an invitation to Profit Diagnostix to the email address of the person responsible for Profit Diagnostix.