HIROC: Enterprise Data Warehouse Architecture with SQL Server, Active Directory and Power BI
The Healthcare Insurance Reciprocal of Canada (HIROC) is a not-for-profit organization that provides insurance and risk management solutions for hospitals, medical facilities and other healthcare institutions in Canada. The organization has its headquarters in Toronto and offices across the country.
For HIROC, their vision is to work with Canadian healthcare institutions to create the best patient safety record in the world by helping them manage risk appropriately and reduce claims. With a bold new three-year strategic plan starting in 2020, HIROC aims to realize this vision by leveraging their unique insights into what causes harm in healthcare, responding to the risk management needs of customers and building a culture of innovation. A key aspect of this strategy was implementing the right data management and data analytics practice – and they turned to MRS for help.
Leveraging data to help create the safest healthcare system in the world
HIROC is uniquely positioned to be able to better understand healthcare risk and potential harm for Canadian patients. By dealing directly with hospitals and other medical facilities for insurance claims, the organization has accumulated the largest medical malpractice and other medical insurance claims database in Canada and, using this data, they are able to help their clients identify and mitigate risk and, ultimately, create a safer healthcare system for Canadians.
With the right data management and data analytics practice in place, HIROC can leverage their data to deeply understand the various factors that contribute to different types claims, and, in turn, help healthcare institutions reduce these claims.
Rearchitecting the enterprise data warehouse system using Microsoft SQL Server
Prior to the engagement, HIROC’s data warehousing system was architected in a way that every time an end-user in Insurance Operations, Risk Management and Claims needed to create a report with their data, a tedious manual process required members of their IT team to create queries and tables on the backend. This meant that, almost daily, members of their IT team were being pulled from their tasks to create business intelligence reports for different departments and, conversely, that the end-users weren’t able to create and access reports nearly as quickly as they needed them. The broken, inefficient process for data reporting needed to be addressed with virtually a complete overhaul of HIROC’s enterprise data warehouse system.
The MRS Business Intelligence (BI) team managed and executed the rearchitecting of HIROC’S data warehousing system from project ideation to completion. Using the conformed dimensional model in Microsoft SQL Server and a tabular model in SQL Server Analysis Services, the new data warehouse disentangled data coming from the source system and makes it readable and comprehensible for end-users.
Ensuring maximum data security using Microsoft Active Directory (AD)
Of paramount importance for HIROC is data security. Dealing with highly sensitive data from Canadian healthcare institutions, it was absolutely crucial to safeguard information and only grant access, strictly, to users with the right permissions. Ultimately, most individuals in the organization are not able to see the most sensitive information and it was critical to architect a system that adhered to HIROC’s strict security and compliance policies.
Incorporating identity and access provisioning into the tabular data model, with both column and row level security, the new enterprise data warehouse system strictly denies or allows access to specific groups created in HIROC’s Microsoft Active Directory (AD) service. Furthermore, the data is only accessible from HIROC networks and employees working remotely must sign into the VPN.
The organization now feels extremely confident that they are taking the necessary measures to safeguard sensitive and confidential information.
Empowering end-users with reports on Microsoft Power BI
The success of any data analytics solution ultimately lies in how it empowers end-users to translate data into useful insights. For HIROC employees in Insurance Operations, Risk Management and Claims, they are now able to use their data – in the largest medical malpractice and other medical insurance claims database in Canada – to create graphical reports, charts, time-series visualizations, etc. using the Microsoft Power BI desktop app.
Instead of requiring an IT team to write unique queries and create tables for every report, end-users are able to leverage the tabular model interface built using SQL Server Analysis Services to quickly and easily construct their own reports – using all the incredible features of Microsoft Power BI’s best in class data visualization platform – as they need them. Furthermore, for increased security and safeguarding, as well as to enforce adherence to organizational best practices, Power BI reports can only be published and distributed once approved.
Set up for success
With a completely rearchitected data warehouse system, HIROC feels confident that they are set up for success and that, with the ability to leverage their unique data and insights, they are now better equipped to mitigate risk for Canadian patients and healthcare institutions alike. The organization feels that this is a significant step towards helping them realize their vision of creating the best patient safety record and the safest healthcare system in the world.