Process Mapping and Report Development
1. Corporate Overview
UPD Dental Associates (UPD) is a leading dental provider in Western New York whose services include pediatric and general dentistry, oral and maxillofacial surgery, and orthodontics. UPD’s mission is to improve the community’s oral health through education, outreach, and premier dental care provided by highly trained professionals. UPD operates out of eight dental clinics and employs 23 dentists and attendings along with 14 pediatric residents.
UPD has opened three clinics while also adding three additional services. As the new clinics and services are assimilated into the practice, I worked closely with the Accounting Manager to design and develop financial dashboards in an effort to streamline month end reporting as the practice continued to expand.
2. Challenge
Previously, with no reporting or analytics tools currently in place, the Accounting Manager used reporting tools native to each of our electronic dental record (EDR) systems, Dentrix and Eaglesoft. This posed a certain set of challenges as the reports are not easily customizable. The reports include data for charges, relative value units (RVUs), and patient visit counts. The Accounting Manager spent nine hours per month running and aggregating 115 reports to finalize monthly production reports for the 23 residents and attendings.
In addition to time constraints, the existing process posed additional challenges:
- Due to the high volume of reports run, the server performance was negatively impacted.
- Each report was exported as a PDF and then converted to an Excel document.
- To gather the necessary data for each provider, five reports were run per provider. Occasionally, a provider joins the practice or changes clinics. In response, all five reports must be modified.
3. Solution
Working with the Accounting Manager and the Leadership Team, I developed a strategy for using QlikView Business Intelligence tools to build a series of dashboards that addressed the aforementioned challenges. I began by dissecting the current workflow in an effort to truly understand the process. Using Microsoft Visio, the Accounting Manager and I completed process mapping.
Once I understood the origins of each piece of data, I began sifting through the Dentrix and Eaglesoft database dictionaries to make educated guesses as to where I could locate the data in the SQL Server and Interactive SQL, respectively. I worked closely with Dentrix and Eaglesoft technical support to verify my findings.
Moving forward, I recreated production reports from both Dentrix and Eaglesoft. I was able to identify the exact query criteria to yield results equal to the native reporting tools in both EDRs. Once the report was validated, I combined both data sets into consolidated dashboards. I worked with the Accounting Manager to determine her visual reporting needs and then completed the front end development.
4. Impact
The report is complete and hosted on QlikView AccessPoint (server) where it is utilized by the Accounting Department via a web browser. The report is refreshed on a monthly basis after the month end is closed in our EDRs. The duration of the process was shortened by one business day, making the month end financial reports available sooner. By eliminating the majority of the manual Microsoft Excel work, we have improved accuracy. This is particularly important because this data is used to compensate the providers. In addition, a lesser impact on the server reduces the lag and strain on the computers running Dentrix and Eaglesoft which also improves the front desk’s efficiency.