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 of claims and, in turn, help healthcare institutions reduce these claims.

Rearchitecting HIROC’s enterprise data warehouse system using Microsoft SQL Server

HIROC’s preexisting data warehousing system was a hindrance to operational efficiency and limited their capacity to leverage their vast amounts of data its utmost potential.  Every time an end-user in Insurance Operations, Risk Management and Claims needed to create a business intelligence/analytics report to drill in to various metrics – an almost daily occurrence – a tedious manual process required significant input and assistance from the IT department.  Members of HIROC’s IT team were constantly being pulled from their tasks to create ad hoc queries and tables on the backend as requested and for each specific use case. This meant that end-users weren’t able to create their own reports on the frontend and that, therefore, they weren’t able to create and access these reports nearly as quickly as they needed them. With a strategic plan to build a culture of innovation and leverage data far more effectively, this broken, inefficient process for data analytics reporting needed to be addressed with virtually a complete overhaul of the organization’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 – ultimately allowing them to create custom reports from the frontend and, in turn, freeing up their IT department to focus on core tasks.

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 is absolutely crucial for the organization 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 highly 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.