(electronic whirring)
- Coming up on How We Built It I'm joined by
US-based health and well-being organization Humana,
who are adopting Power BI and Azure
to create a single self-service reporting platform
that builds a unified profile of their members,
so that they can proactively reduce their health risks
with access to key services and healthcare.
Now, we're going to look at
how they've approached self-service reporting
for their team, from building query-optimized schemas
and identifying the right filters and visualization.
To do that, I'm joined by Business Intelligence Engineer
Sean Chandler from Humana.
Welcome to Microsoft Mechanics.
- Hi, thanks, it's great to be here.
- And thanks again for joining us today.
So not only is Humana helping people at retirement age
or with disabilities get the health care coverage they need,
but it also has an initiative to make its members healthier.
So, what role then, does the data team play in all of this?
- So, we want all of our members to
be able to achieve their best health,
which means building the most complete profile we can
of our members across multiple dimensions.
To get ahead of potential health risks,
while also meeting their existing needs.
And something that Humana looks at very closely
is something called social determinants of health,
which are upstream influencers of health outcomes
that describe our member's living conditions,
their communities and their social interactions.
And to understand social impacts on health,
we screen our members for things like food insecurity, loneliness,
financial strain, housing quality, neighborhood safety
and other things.
And these things are important because one
would expect a person without access to healthy food
and good housing and transportation
to their medical appointments to have worse health outcomes
than a member who does have all of these things.
So we're curious about social determinants of health
and how they correlate with other things that we know
about our members.
Like their existing conditions, their age, their ethnicity,
their gender and a whole range of other variables.
And when we find positive matches for these social needs,
we can intervene.
For example, if a member is identified as food insecure,
an intervention that Humana might take could be
to connect them with a local food pantry.
Or if we know that a member requires
regular medical supervision, but they don't have
reliable transportation to get to their appointments,
maybe we can intervene by connecting them
to community transportation.
- So, how are you approaching that insights challenge?
What was your starting point then on the data team?
- [Sean] Well, we had to solve for a number of things.
We have multiple external partners
and internal teams screening members for social determinants
of health and all that data lives in seven
to nine disparate data sources and there's not a lot
of consistency in survey formats.
The questions could differ from survey to survey
as well as their scales or response types.
And some of this data goes directly
into our on-premise data warehouse, while other data
might go directly to specific departments or users via CSV
or Excel files and they then use a variety of BI tools
and business logic to clean up and analyze that data.
And of course, different data stories build up over time
from all these different pathways
and it gets even more complex if we want to bring in data
on member demographics and health conditions
as this lets in other data sources too.
- All right, so it sounds like a big challenge now.
How did the data team then solve for all this?
- [Sean] So our first step was to rationalize
these different data sources and build the business logic
to properly relate the questions that map
to a single social determinant like food insecurity or isolation.
And we also needed to rationalize these responses,
so that we knew which members screen positive
or negative for a given social need.
This helped us create a masters screening data set
of member level data in our data warehouse,
which enabled us to connect with other related data sets
by matching a unique member ID to other tagged data sources
that have the same ID.
From there, we can ingest a cleansed data report
into our Power BI via ODBC, resulting
in a single self-reporting business insights platform
to explore the data further.
As you can see here on our dashboard,
which shows a collective view of member screenings
across all of our social determinants.
And this initial version
of the self-service platform took a few weeks to stand up,
but within three months, we were largely in the state
that you see today and that's a short amount of time,
given the complexity of where we were coming from
and also what we're used to doing
with other tools at Humana.
- And it really sounds like a lot of kind of complex data
behind that dashboard.
It was really fast, I think it looks like,
to get it all stood up.
But how did you approach things like query performance,
given all the data coming in?
- Well, one of the big challenges that we wrestled with
in the beginning was that because this was a brand-new
and constantly evolving data set,
we could only guess at the eventual size
of the data model and would probably be easier
if I showed you.
So, the first version of this report ran just fine,
but as time went on and the model grew from
tens of thousands of rows to over 1.3 million rows,
features that used to load in seconds
were suddenly taking close to a minute.
And of course, that's not the kind of user experience
that we wanted visitors to have.
Our problem was that the data set we created ended up
being quite wide, almost 120 columns in fact.
And our main metric, social determinants screenings,
was spread out among dozens of columns.
So, in order to slice by a specific domain in my report,
I had to write this really complex DAX measure
just to derive my most important number.
And this conditional logic of course, brought my report
to a crawl, so I knew I needed to try something different.
Fortunately, this report was a great chance for me
to familiarize myself with one
of Power BI's most incredible tools, the power query editor.
I was able to take a big table like this
and just remove what I didn't need by applying
all these steps and then pivot my metric columns
into a single column, as you can see here called screenings.
And this allowed me to conserve
the overall size of the model,
while greatly improving the report's performance.
So then I just spun off all the other dimensions
into smaller tables and joined those back
to the Screenings Fact table in the schema editor
as you can see here.
All the other tables in my schema contain the attributes
of my data set.
Things like age group, division, and gender,
and my screening's metric resides in this fact table
where all I have to do now is just sum it.
While that fact table is now 11 million rows
because of by pivoting the data, it's much easier
for Power BI to sum 11 million rows in a narrow table,
than to run a bunch of conditional logic
on only 1.3 billion rows.
And so this allowed me to condense my original DAX code
from dozens of lines to just a single line of code
with no if-then logic at all.
- That's great, so going back to the dashboard itself,
I can see it's a great looking report.
But how did you build it then to meet all the needs
of the users at Humana?
- So from a design standpoint, I knew one
of the most important features of the tool was
to let users filter screenings by each social determinant
of health domain.
And since I knew that this was so vital,
I knew that we needed to set this filter apart
from all the other filters in the report.
Which is why I thought it'd be really cool to represent them
as interactive icons in a banner
that would immediately draw users attention.
To pull this off,
my business users helped me select appropriate icons
for each domain and I put those into a SharePoint folder,
which I can connect you from Power BI.
With all of the images stored in Power BI as web URLs,
I downloaded the Chiclet Slicer
from the Power BI Custom Visual Marketplace,
which is a really neat tool that allows developers
to use icons or images as filters,
instead of just text or numeric values.
And that way, if users want to see
food insecurity screenings, all they have to do is
just click the Apple icon.
- How did these standardized the approach, in terms
of filtering out those different views within the report?
- Well, we worked closely with our business partners
for weeks and we were able to carefully curate a list
of filters in the right hand filter pane
to include only the most important fields.
But we also knew we had a group of users who needed
to break down screenings quickly by these different values
in order to best serve their customers,
but we also couldn't expect users to just sit
and manually filter through all 50 states, for example,
just to find the insights that they needed.
By creating a pivoted DAX table
of the most important fields,
I was able to give users control
over the horizontal bar chart on that page.
And with that table,
I was able to create multiple relationships to my schema
as you can see here.
And I'm connecting the new pivoted dimension table
I just created to my primary dimension table, Diem Main.
So now if we go back to the dashboard, you'll see that
this now allows users to select the axis on the chart
and change it from something like division to region
almost instantly in order
to see the top screening's drivers.
But beyond filters, I also needed to think about
which Power BI visuals could help me convey information
to users about the report itself.
So one novel idea I had was to use the tree map visual.
Now, although it may not look like it,
these three nondescript blocks on the left hand side
of my report are actually separate tree maps
that link tool tips for filters, metrics
and social determinant of health definitions.
So now, when users hover over the tree map,
they see a pop-up with definitions
for the various social determinants of health.
- This is really nice to see and I can imagine then
as more and more stakeholders start to see the tool
and you introduce it to them, they're going to want more
and more views of data, so how do you accommodate their
variable needs and kind of additional needs they have
on the data set?
- So one of the great things about Power BI is its ability
to help users ask questions of their data.
Of course, curiosity can also lead
to reports becoming cluttered with visuals that attempt
to answer every potential question
the visitors could have at once.
So, if we go back to the dashboard, I can show you
how Power BI's bookmarking capability helped me capture
all the views my business partners requested on one page.
If you look here, you can see that I have a ton
of visuals overlaid on top of each other
and it looks very chaotic.
However, if I enable the bookmarks pane in the toolbar
and the selection pane, I can hide and unhide visual objects
on the page and then save those settings as bookmarks.
So note that when I click the screenings over time bookmark,
several visuals vanish and I'm left only
with the ones that I want as indicated by the eye icon.
If I switch to a new bookmark, new visuals are hidden
and others reappear.
So bookmarks allowed me to quickly create
an executive summary within the report,
without bogging down the tools, performance,
or overwhelming visitors with too many visuals at once.
However, as users ask for new views,
one of the most important things that I found with Power BI
is the ability to do ad-hoc querying.
Because at the end of the day, the real value for us
in Power BI is in the flexibility that it gives us
to ask questions and conduct ad-hoc analysis.
In the past, a pain point for our BI tools has been
that it could take weeks to update a report
with new material.
By contrast, as you can see here, over the past
eight to 10 months, we've iterated 20 versions of this tool
and the second tab of
this report represents a more ad-hoc exercise.
As you can imagine, just knowing
where our members are being screened isn't enough.
In a perfect world, every positive screening
for an SDOH need should result in some sort
of intervention being taken to help meet that need,
but in the sample data, it looks like we're only meeting 87%
of those needs.
So to dig into this, we conducted an exercise
on the fly using business logic developed
in face-to-face meetings.
And with the query editor, I was able to build in
that business logic in seconds in the form
of a custom column that mapped each screenings provider
to a specific intervention.
Like a community resource referral or a toolkit.
So when we go back to the dashboard, what we determined from
that exercise however, was that not every source
of SDOH screenings could be mapped to a known intervention.
So selecting the 'no known interventions' bar
filters the information on the left and tells us
that source one does not appear to have a known outcome
for members who screened positive.
Exercises like this have helped spur action to reach out
to those vendors and identify their mode of outreach
in order to close a potential gap
in securing the health needs of our members.
- All of this you did in such a short amount of time
from a complex starting point, but what's next then
for the data team at Humana?
- So for social determinants of health, we have all sorts
of cool things planned.
Mapping that SDOH data to other master data sets
from across our organization
will help us get more robust insights about the members
we identify with social needs and also,
what other chronic health conditions they may have,
so that we can be more proactive
in meeting their unique needs.
And as the report grows and becomes more complex,
I'm also looking forward to considering
how something like Power BI's Q&A feature
can help users find what they need
just using natural language.
Of course, our longer-term goal is bringing
our data into Azure.
That way we won't have to worry so much about data exports
and imports and data refreshes will be almost instant.
So we'd still apply the exact same ETL and business logic
to our orchestration pipeline via Azure Databricks,
but from here, we'll be able to standardize reports
and data on Power BI and tie our legacy BI tools.
And also, we can tie our data to other common data sets
to be able to quickly isolate and target key populations
based on business critical data.
And we can also use AD groups to administer access
to Humana's citizen analysts, so that they can start
to self serve for their needs
and do their own proactive analysis.
- Thanks Sean and this is a fantastic implementation
and thanks again for joining us today,
really to share all the progress that you've made
with the Humana team and also your plans for the future.
Of course, if you want to learn more about Power BI,
be sure to check out powerbi.com and you can also check out
our Power Platform Series at aka.ms/MechanicsPowerPlatform.
And by the way, don't forget to subscribe
if you haven't already to Microsoft Mechanics.
Thanks for watching and we'll see you next time.
(electronic music)