Emerging business intelligence tools like Power BI have opened up endless ways for businesses to use data for strategic initiatives, and to put data analysis in the hands of business users without overtly technical backgrounds.
To illustrate a real-world example, we’ve put together a simple market research example useful to healthcare. We used Power BI for the project, which we recently featured on our blog as a robust, self-service business intelligence tool. It allowed us to evaluate data quickly: there were no databases to set up and no server space or expensive hardware to buy.
In our example, we assume a physician practice management company needs to identify high revenue producing physicians to recruit into new practices as it enters a new geographic region: the state of New York. For this rapid analysis, we elected to use publicly available data sources.
Getting the Data Together
It turns out that New York state publishes de-identified patient data about hospital admissions and discharges, along with cost and revenue information. We downloaded this source as a CSV.
The data did not include physician name, but did include the license number for the attending physician. We want the name, so we downloaded the national provider registry from CMS as a CSV file as well. This data set includes the physician’s state license number.
A Clean and Simple Story from Our Data
By combining these data sources and organizing the data, we were able to create a prioritized list of the top 20 providers to approach as you can see below. If you are curious to know how we did it, we walk you through the steps following the image:
How We Used Power BI for the Analysis
To combine the files in Power BI so that we could grab the provider name from the CMS data and the hospital revenue number from New York’s hospital data set, we loaded both CSV files into Power BI’s desktop application. Weighing in at 3 GB for the provider file and 800 MB for the hospital data, this is definitely something we couldn’t do in a “standard” Excel analysis, but it was essentially as easy as using Excel to put the data in Power BI.
Once the data was in Power BI, we created a lookup using a drag and drop interface, resulting in a one-to-many relationship between the two data sets and allowing us to create a list of physician names with revenue.
Next we needed to create measures (ie, formulas) to explore the data. Power BI uses a query language called DAX, which is comparable to the syntax we are all familiar with in Excel. Here are a few examples of the measures we created:
Revenue
Cost
Unique Count of Providers
Next, we created a simple chart to display the names.
There were a ton of names. To help us focus, we wanted to easily reduce the list to a manageable number. We created a rank of each provider using the DAX formula RANKX:
What this formula does is for a list of all providers, rank them sequentially by the amount of revenue for each provider.
Of course, our data set includes thousands of physicians. That is too many to focus on. We created a table to display the information, and then applied a filter to show only the top 20 providers by revenue:
Next, we noticed that not all names matched, resulting in many rows with a revenue figure but a blank name. This is a classic data quality issue. Data quality management is outside the scope of this post, so for simplicity’s sake, we created a simple list of physicians for which no physician name was found in NPPES data, listing the license number instead of the name:
With this list in mind, we know that unless we hunt down the names, we are potentially leaving “money on the table,” and it invites us to brainstorm with our colleagues on how to find the names, either through another publicly available data source, an individual Google search, or another approach to the problem.
The Result of Our Power BI Research
In summary, this analysis allowed us to:
- Perform market research to find physicians in a geographic area who are likely to be highly profitable.
- Complete the research in an afternoon.
- Spend no money on the analysis.
- Use business resources in place of technical resources for the analysis
