Thursday January 18, 2007

Spotlight On: Data Exploration with Drill Down

When we began building Planwatch, the focus was primarily on creating a great set of high level reports. It became obvious early on that these reports, while highly informational on there own, would also be perfect starting points for deeper analysis and data exploration. These ideas would shape much of the architecture and design of the system and led to the adoption of tools and techniques commonly found in Data Warehousing.

Drill down is the first of many features that takes advantage of the underlying analytics engine and brings Planwatch firmly into the realm of modern Business Intelligence software.

What is "Drill Down"?

In its simplest form, "drill down" means nothing more than "give me more detail."

High level reports condense large amounts of claims activity into easily digestible summaries - a ten-thousand foot view of your plan's performance, if you will. While these reports tell you a lot about your overall plan performance, they often lead to additional questions that require a more detailed look at specific activity. Drill down lets you instantly drop down a few hundred feet to get a slightly more detailed view of activity. If the question is still unanswered, you can drill down further still, in small or large increments, until the answer is apparent.

Let's take a look at a specific scenario.

 "Wikipedia - Data Warehousing"

Interactive Analysis Starts with Reports

The following is a sample benefit analysis report covering an entire plan year for a group with 300 active employees (900 total members). The purpose of this report is to provide a general idea of which benefit categories are most (or least) costly and is often a good starting point for potential optimization:

Suppose the Chiropractic category jumps out as being somewhat high (in this case it's under control at less than 1% of total plan expense but will serve just as well for demonstration purposes). Clicking Chiropractic drills down into that specific category where, among other things, we can get a better feel for how cost is distributed between members and providers and see how well network contracts are being utilized.

Narrowing the Analysis with Drill Down

The Chiropractic link takes you to a simple but highly functional data exploration screen:

At this point, we are exploring Chiropractic related activity and can slice and dice the view based on what we need to know. There are a number of things worth pointing out here:

  1. The table includes one row per employee and displays the aggregate number of claims, amount paid by the member, amount of savings generated by network contracts, and the amount paid by the plan. Clicking the employee drills down further.

  2. All Personal Health Information (PHI) is blinded but can be "unblinded" selectively by clicking the show PHI link.

  3. The top level reports are always only click away, making it easy to find your way back to the higher level analysis.

  4. The View box determines what is displayed in the table. We'll take a closer look at this in the next section.

Changing The Perspective

When we initially drilled into the Chiropractic category we were taken to a list of employees but you can change the perspective by selecting between Employees, Members, Plans, Divisions, Places of Service, Network Contracts, Providers, Charge Types, and Claims. The result list is instantly updated with aggregate values for the selected item:

Here we've selected the Network view and are now able to see how our Chiropractic related activity breaks down across contracts. As you can see, network usage is very high with only a small amount of activity occurring in secondary (less savings) and non network providers.

Putting It All Together

Now that we've seen how to drill down for more detail as well as how to change the perspective, let's combine the two to show how powerful this form of interactive analysis can be. We'll first narrow the analysis further by clicking Primary Network 1 and switch the perspective to Providers:

From here, we continue to narrow activity to include only claims that were generated by DEMO CHIRO INC and switch to the employee perspective:

Now we see the employees that incurred claims at DEMO CHIRO INC and were discounted by Primary Network 1. Let's go even further and take a look at the individual claims by clicking on the SAMP000:0241:1 employee:

We've arrived at a listing of claims generated by employee SAMP000:0241:1, incurred at DEMO CHIRO INC, and discounted by Primary Network 1. We've also left a convenient trail of breadcrumbs at the top of the page, allowing us to return to any previous context quickly.

From Days to Minutes

Data exploration makes it possible to quickly answer many types of common questions. We've had multiple clients tell us that issues that previously required a data specialist and a few days of work are now being resolved in minutes by anyone with a few clicks of the mouse.