By Sam Tyler (Senior Consultant):
I want to talk about something sexy: balancing numbers. Yes, I know – bait and switch. But it’s vital in analytics, which for purposes today, is focused on the application of data for decision making within business. Often, computer scientists and IT folks refer to analytics as the implementation of a solution. In business, they refer to analytics as making dashboards, reporting, or scorecards. But no one pays for a tool or a piece of paper unless it delivers value.
And if you’re going to deliver value and apply data to a decision, the data has to be right. “Right” means the data balances to some source of truth. Which source? That depends on what’s available. For one source, we can compare the numbers to detail reports within a host system. Alternately, you can compare them to another department considered a source of truth. Either is fine, although the approach is different.
My experience leads me to believe that technology-minded professionals underestimate this need. I was working with a client to develop a dashboard and we were called in to validate a report that IT was working on. It was a complete mock-up, but they were going to present it anyway. I advised that it would be better to get the numbers correct and then show it, but I was told I didn’t know the customer. The conversation was shortly over and I (and the client) were a little miffed at being rebuffed.
So, when the call happened, we decided to mute the phone and remain quiet. The first comment: “There is no way those numbers are right.” While the team tried to say it was a mockup, the business (including an actuary in the organization for 10+ years) was adamant they needed to see something with the numbers right first. And the actuary finished with this tip for them: “Use the balanced numbers from m4. I know that those are right and they’ve been through rigorous vetting.” Yes, we were high-fiving at the other end.
How do you validate that data? First, let’s be clear about something. “Validation” means something different in business than it does in technology. IT refers to validation as making sure the data elements themselves are valid. For example: a zip code is a real zip code, phone numbers aren’t missing area codes, names don’t have any funky characters in them. It’s a necessary part of something called “data cleansing”. But “validation” to a business means that the numbers match the source of truth – usually accounting. This is because reporting is a regulatory necessity. In addition, if you make a decision based on something other than what’s official, you’re in hazy territory. We recommend that you follow these tips when balancing the numbers to be used in your structured data sources.
Tip 1: Know the reports. Know how the reports float up to accounting and the financials or whatever the equivalent official figures are. If you’re on the provider side, talk to accounting. If you’re on the payer side, talk to actuarial. (The difference I’ve found is that with payers, actuarial has more political sway than accounting.)
Tip 2: Run the report and then immediately run your query. Check the numbers. Do they balance? Now, I prefer to the penny. Because of lags in data, that’s not always possible. However, it should be within 1% – all the time.
Tip 3: If it’s not correct, re-run the report with more detail – looking at the lowest level of granularity possible. This isn’t always a possibility. If you’re comparing your numbers to another department, compare your query to theirs. Make sure the filter criteria are identical. Keep repeating Steps 2 and 3 until they balance. Yes, this is very tedious work, but it’s necessary.
Tip 4: Build some sort of automated mechanism to compare the numbers or do it manually if that is the only approach. The beauty of SQL is that once the numbers balance, the structure of the query doesn’t change. You can run it again and again and it will still balance. But, you want to know for sure.
My first data warehouse that I built was a labor of love and I nurtured it for a long time. I had a mechanism built to automatically balance the warehouse and the host system. For years, it balanced every time. To the penny. And then one day, it didn’t. The RVUs were off by 12%. You know when you anticipate something being a huge problem and you get surge of adrenalin? Yeah. In my mind, I really don’t care if data’s missing unless it really messes things up. You can note and qualify things a lot before people get concerned. If they understand the issue and the magnitude, it’s fine – as long as the numbers are okay. If the numbers aren’t okay, all of Hades reigns on the Earth.
So, I pulled in the report details from the host system and did a line-by-line comparison with the warehouse. It all came down to three procedure codes. Someone had modified the master file and had done it in such a way that there were several rates effective at the same time. The host system can be programmed to a certain extent to ignore the data structure. SQL can’t. So, I found the problem, notified the business manager, notified accounting, and went back to normality. Keep in mind, this took about a half-hour to determine the problem. I had to re-run the queries to repopulate the database afterwards, but I was very thankful that I had that routine in place.
At m4 innovation, helping our clients get the maximum business utility from their analytics to reduce the time-to-decisions is one our core passions. Given healthcare is all that we do, we would be happy to have a conversation with you regarding how to leverage our expertise & data structuring processes for your organization. You can email me here to set up a discussion. As always, keep innovating—and keep making sure those numbers balance to bring maximum confidence to your decision making during this transformational time in healthcare.
Oh, and if you like this kind of thought leadership, you might be interested in our annual strategy group—4mul8—which is meeting in Denver this July. You can review the group’s details and request to join this invitation-only group here.