Your PM system’s database is one of the greatest sources of information about your practice. The problem is that the system’s canned reports don’t provide you with the meaningful information that you need to effectively manage your practice and make decisions. It’s time to bypass the canned reports and adopt an effective business intelligence solution.  

Using common business intelligence (BI) tools, such as Microsoft Excel Power Pivot or Microsoft Power BI, data visuals can be created that provide you with a 360-degree view of your practice.  To accomplish this, data feeds are established from your EPM/EHR system directly to the BI program using a structured database query language, such as SQL.  These feeds can load hundreds of thousands of rows of data into the BI system.   After the feeds are established, you can choose from a variety of different types of data visuals (e.g., charts, graphs, matrices, pivot tables, etc.) to display your data the way you want to see it.

So, what is the benefit of using these types of BI tools?  

The main benefit is that you can create multi-dimensional views of aggregated data and quickly slice through the data to view it from different perspectives.  You can also create calculated measures that greatly enhance the types of reports and analyses that you create. Let’s break it down a bit with an example.

Let’s say you wanted to create a report that showed revenue by provider and location for January and February.  Traditionally, using canned reports from the PM system, you would create the report and apply the following groups: time period, provider, and location.  The result of the report would display vertically, according to the order in which you grouped the data. It would look something like this.

While this report displays the revenue information we are after, it has many limitations.  To start with, the vertical format is not easy to read.  Furthermore, if you wanted to quickly see what the total for each location would be, you would have to open the report builder, change your groupings, and rerun the report.  In the same way, you would have to reopen the report builder to add/remove locations or doctors.

Using a BI application, you can build a revenue data feed and create a multi-dimensional view of your data.  Using the same example, here is what a revenue pivot table could look like.

Notice that the data cube already aggregates the totals for each dimension (i.e., location, doctor, month) as well as displaying a grand total for the locations.  To the left of the pivot table, there are filters that represent all the dimensions.  You can quickly include/exclude a dimension by clicking on/off the filters and watch the data change instantly.  There is no need to rerun the report. This is because all the revenue data related to location, doctor, time period, etc. has been fed into the application via the SQL query.  At this point, all you do is interact with the data.

These tools also allow you to create complex measures and calculations that you can build into the reports.  For instance, you could create a net collection ratio measure that would automatically calculate the net collection percentage for you practice.  You could even program the measure to only calculate payments/adjustments for a particular time-period so that you could see how your net collection % is after 6, 9, or 12 months.  By building this logic into the system once, you never have to recalculate the measure again. It simply becomes just another fields that you can move around and interact with.  

The great thing about using BI tools is that once you design your BI models – including the SQL query, format, measures, calculations, and visuals – you simply refresh when you want to see updated information.  Just one data visual can replace several canned reports from your PM system, giving you more time to spend on analyzing the data.

These tools are likely already at your disposal. For instance, Microsoft Excel has a PowerPivot plugin that will allow you to do everything previously described. Most offices already use Microsoft Excel, so there are no additional costs. Microsoft PowerBI is another BI application that can easily be deployed. While there is typically an additional fee for using the product, it does offer some additional features.

So, why don’t practice managers take advantage of these tools?

One of the major barriers is access to your PM system’s data. Many vendors make it difficult to access the data. I would strongly encourage you, however, to be persistent when it come to accessing your practice’s data.

Also, some database systems are easier to work with than others. All the examples on this site are based on a PM system with a Microsoft SQL Server database. SQL is very common and easy to work with.

Finally, some practice managers may not have the technical expertise to setup the data feeds. The good news is that there are plenty of experts out there who can assist with this step. Your IT team may even be able to accomplish this step. Once the data feed is established, you shouldn’t have to deal with it again. Interacting with data (i.e., building reports, creating visuals, etc.) has a much smaller learning curve – something that most managers can pick up fairly quickly.

It’s time to can the canned reports and start mining your data. If you’d like to learn more, don’t hesitate to email me at medicalpracticeintel@outlook.com.