Infrastructure Update Guide

Version 20.10

About

To improve your OP system performance, we’re excited to announce that we are upgrading our infrastructure. For many of you, this will be a non-issue as our plan is to facilitate this on your behalf.  Otherwise, if either of the following applies to your practice, this document is for you.

  • If you’ve written your own DBViewer queries, custom reports, or QIC queries, you may need to make a few updates to those queries.
  • If you have other entities that tap into your server for information, run queries that are passed off to another vendor, and/or connect with third-party vendors like Solution Reach, Rhinogram, or FigMD, share this document regarding the infrastructure update. They will need to work with OP to make the necessary changes by the end of Q4, so they can connect directly to your server. Please note that as of Q2 2022, your current and future third-party vendors will need to use our APIs to connect to your server. They will no longer be able to establish a direct connection.

Background

Office Practicum will be changing its backend database from Firebird to MySQL. Firebird and MySQL both use "dialects" of the SQL language. SQL (Structured Query Language) is the programming language that both make calls from and sends updates to, a practice's OP database.

OP will be updating most of the queries and reports in your instance automatically. However, if you have created any custom queries or reports in the Database Viewer or OP Reports,  you'll want to start following the guidelines outlined in this document now to ensure you are ready to go before we do your migration. The Cloud migrations begin early Q4 2021 and the On-Premise migrations begin in late Q4 2021. We’ll let you know your exact migration date at least two weeks prior to the event.

To learn how to write SQL that is compatible with both Firebird and MySQL, click here.

Where Would I Find Custom SQL

Custom SQL queries may be found in a number of areas within OP. Below are those areas and who will be addressing any incompatibilities.

Area in OPAddressing Incompatibility
Care PlansThe OP team will convert all SQL's associated with Care Plans in Office Practicum.
QICThe OP team will convert all SQL's that are used to calculate quality measures in the QIC tool.
DB ViewerThe OP team will automatically convert most SQL's stored in DB Viewer. The OP Help Center will contain updates to, or MySQL equivalents of, queries originally provided in the SQL Clip and Save Library. The OP team will work with customers to make adjustments to any remaining incompatible queries.
Date ParametersIf you have a date parameter in your query, make sure the parameter name ends in "_date", for example, start_date and end_date. When you do this, DB Viewer knows to use a date picker for that parameter.
OP Reports

The OP Team will be converting any incompatible reports that we include in OP with updated reports that work in MySQL. These include all reports in Billing Performance, InsCreditSummary, PatCreditSummary, and Standard reports delivered with OP. If you have created custom reports, the OP Team can work with you to make them compatible.

There are two kinds of reports in OP Reports: Standard reports and those that are built with an SQL statement.

  • If the report contains one of the below, you will need to follow the instructions in this section.
    •  Is an older standard report ("Magic SQL").
    • Does not run in MySQL.
    • Contains a join.
    • One or more of the report fields belong to more than one table.
  • Instructions for the above reports.
    • Right-click on the report and select Design.
    • Click the Data tab.
    • Click the funnel on the affected pipeline.
    • If the first column does not include a prepended table name, double click to remove then choose the field again from the top list of available fields.
  • You can identify the SQL statement based reports in DB Viewer using the following query that provides the name plus up to 4 branches of the folder path. Some reports exist under the same names in different paths.
select rb_item.name as report_name, coalesce(rf4.name||'/','')||coalesce(rf3.name||'/','')||
coalesce(rf2.name||'/','')||rb_folder.name as folder_path
from rb_item
left join rb_folder on (rb_item.folder_id=rb_folder.folder_id)
left join rb_folder rf2 on (rf2.folder_id=rb_folder.parent_id)
left join rb_folder rf3 on (rf3.folder_id=rf2.parent_id)
left join rb_folder rf4 on (rf4.folder_id=rf3.parent_id)
where rb_item.template like '%EditSQLAsText%'
order by 2,1

Other Connections

ODBC Connection

If you have an On-Premise server (i.e. you are NOT in OP Cloud) you may have an ODBC or JDBC connection to your database so that you can extract data into tools like Excel, or that a hospital can pull data from your database.  Those connections will need to be updated to use MySQL.

Vendors

If you have other entities that tap into your server for information, run queries that are passed off to another vendor, and/or connect with third-party vendors like:

  • Solution Reach
  • Rhinogram
  • FigMD

The practice will need to share this document regarding the infrastructure update. They will need to work with OP to make the necessary changes by the end of Q4 2021, that they may connect directly to your server. Please note that as of Q2 2022, your current and future third-party vendors will need to use our APIs to connect to your server. They will no longer be able to establish a direct connection.