Health Agency Qlik Sense to Power BI
Project Synopsis
Migration “Spend Analysis” dashboard from Qlik Sense to Power BI
About this project
Our Health Agency client is an independent public sector and commercially oriented provider of supply chain, procurement, and corporate services to partner with an Australian state’s public health services and suppliers in delivering best-value health-related goods and services.
The client approached synogize with several issues they were experiencing with their current Qlik Sense implementation and idea of migrating their BI and reporting solution to Power BI.
The project included reviewing and collecting business rules scattered across different layers of Qlik Sense implementation, designing the best for PowerBI dimensional data model, and building new “Spend Analysis” Power BI report.
Client Success Story
About the client
The Health Agency was established on 1 January 2021 as an independent public sector and commercially oriented provider of supply chain, procurement and corporate services to partner with an Australian state’s public health services and suppliers in delivering best-value health-related goods and services.
The client brings together the experience and expertise of one of the state’s health purchasing organisations and one of the state’s health logistics organisations to provide significant benefits for the state’s public health system, including:
- Consistent access to goods: clearer accountabilities and improved coordination of sourcing and distribution medical supplies; reduced critical stock distribution inequities at hospitals.
- Substantial savings: using statewide purchasing and economies of scale to generate savings for health services.
- Improved quality of care: Improving patient outcomes through evidence-based product selection, reduced non-clinically justified variation in product utilisation, and enhanced availability of medical consumables.
Business Challenge
Our client procures materials and medicine on behalf of all public hospitals in their Australian state and must consolidate data in different formats from various systems into a single repository to enable analysis and reporting.
With rapidly growing data volumes and increased complexity from merging several business units, financial and procurement analysts were struggling to perform their analysis and reporting tasks in a timely manner. Furthermore, a lack of data quality assurance was diminishing trust in data and the client’s ability to become a data driven organisation. As a result of these challenges the usage of Qlik Sense was diminishing, and users were finding alternative ways to extract data and perform their analysis and reporting tasks.
From a technical point of view the Qlik Sense licensing model was seen as complex, expensive, and unable to accommodate the growing user base. Furthermore, the solution design was no longer fit for purpose and a significant amount of time would have to be invested to remediate the solution to bring it up to match new requirements and ongoing changes, which led the client to explore alternative solution options.
Approach
In collaboration with our client, synogize performed a systematic review of all the QlikSense applications including analysis of data flows, calculation of metrics, data lineage, and performance bottlenecks. Once documented it gave our client transparency and clarity of the complexity of the current solution design, furthermore synogize was able to draw out requirements and characteristics to guide our client on the best way forward.
Leveraging existing infrastructure and skills within the team, it was decided to utilise an existing Data Warehouse to perform most of the data extraction and transformation tasks as this would push both data volume and processing to a scalable database removing performance bottlenecks. Data models where then reverse engineered from QlikSense and deployed into the Data Warehouse ensuring like for like definitions of data attributes were carried over to the new solution.
However, one of the QlikSense applications were highly complex and involved several manual steps in the data pipeline to deal with data extraction, data quality, and calculation rules. Given the high level of manual intervention required this QlikSense application required a different approach that would allow the client’s Data Team a high degree of autonomy in the data processing pipeline. To meet these requirements synogize proposed Alteryx as a data workbench replacement for QlikSense as it would allow for requirements to be migrated and for data output to be stored in the common Data Warehouse.
With all data assets consolidated into a common Data Warehouse and performance bottlenecks removed the final phase of the migration was to refactor QlikSense Dashboards onto the new BI platform. For our client, the decision was to adopt PowerBI as the new enterprise BI platform. Taking a phased approach, our client was able to migrate the entire inventory of QlikSense applications to its new data platform within 6 months following a pragmatic, low risk, low-cost approach in partnership with synogize.
Outcomes / Benefits
Working with synogize enabled our client to migrate off QlikSense before license expiry and realise several technical and business benefits:
- Streamlined data feeds and reduced complexity.
- Removed performance bottlenecks.
- Updated and revised future-proof data model, which can cater data to different modern BI tools, including Power BI.
- Upgraded and enhanced dashboard and reports design with improved usability and performance enticing end-user away from their cottage industry solutions and back onto a fit for purpose enterprise platform
Project Synopsis
Migration “Spend Analysis” dashboard from Qlik Sense to Power BI
About this project
Our Health Agency client is an independent public sector and commercially oriented provider of supply chain, procurement, and corporate services to partner with an Australian state’s public health services and suppliers in delivering best-value health-related goods and services.
The client approached synogize with several issues they were experiencing with their current Qlik Sense implementation and idea of migrating their BI and reporting solution to Power BI.
The project included reviewing and collecting business rules scattered across different layers of Qlik Sense implementation, designing the best for PowerBI dimensional data model, and building new “Spend Analysis” Power BI report.
Client Success Story
About the client
The Health Agency was established on 1 January 2021 as an independent public sector and commercially oriented provider of supply chain, procurement and corporate services to partner with an Australian state’s public health services and suppliers in delivering best-value health-related goods and services.
The client brings together the experience and expertise of one of the state’s health purchasing organisations and one of the state’s health logistics organisations to provide significant benefits for the state’s public health system, including:
- Consistent access to goods: clearer accountabilities and improved coordination of sourcing and distribution medical supplies; reduced critical stock distribution inequities at hospitals.
- Substantial savings: using statewide purchasing and economies of scale to generate savings for health services.
- Improved quality of care: Improving patient outcomes through evidence-based product selection, reduced non-clinically justified variation in product utilisation, and enhanced availability of medical consumables.
Business Challenge
Our client procures materials and medicine on behalf of all public hospitals in their Australian state and must consolidate data in different formats from various systems into a single repository to enable analysis and reporting.
With rapidly growing data volumes and increased complexity from merging several business units, financial and procurement analysts were struggling to perform their analysis and reporting tasks in a timely manner. Furthermore, a lack of data quality assurance was diminishing trust in data and the client’s ability to become a data driven organisation. As a result of these challenges the usage of Qlik Sense was diminishing, and users were finding alternative ways to extract data and perform their analysis and reporting tasks.
From a technical point of view the Qlik Sense licensing model was seen as complex, expensive, and unable to accommodate the growing user base. Furthermore, the solution design was no longer fit for purpose and a significant amount of time would have to be invested to remediate the solution to bring it up to match new requirements and ongoing changes, which led the client to explore alternative solution options.
Approach
In collaboration with our client, synogize performed a systematic review of all the QlikSense applications including analysis of data flows, calculation of metrics, data lineage, and performance bottlenecks. Once documented it gave our client transparency and clarity of the complexity of the current solution design, furthermore synogize was able to draw out requirements and characteristics to guide our client on the best way forward.
Leveraging existing infrastructure and skills within the team, it was decided to utilise an existing Data Warehouse to perform most of the data extraction and transformation tasks as this would push both data volume and processing to a scalable database removing performance bottlenecks. Data models where then reverse engineered from QlikSense and deployed into the Data Warehouse ensuring like for like definitions of data attributes were carried over to the new solution.
However, one of the QlikSense applications were highly complex and involved several manual steps in the data pipeline to deal with data extraction, data quality, and calculation rules. Given the high level of manual intervention required this QlikSense application required a different approach that would allow the client’s Data Team a high degree of autonomy in the data processing pipeline. To meet these requirements synogize proposed Alteryx as a data workbench replacement for QlikSense as it would allow for requirements to be migrated and for data output to be stored in the common Data Warehouse.
With all data assets consolidated into a common Data Warehouse and performance bottlenecks removed the final phase of the migration was to refactor QlikSense Dashboards onto the new BI platform. For our client, the decision was to adopt PowerBI as the new enterprise BI platform. Taking a phased approach, our client was able to migrate the entire inventory of QlikSense applications to its new data platform within 6 months following a pragmatic, low risk, low-cost approach in partnership with synogize.
Outcomes / Benefits
Working with synogize enabled our client to migrate off QlikSense before license expiry and realise several technical and business benefits:
- Streamlined data feeds and reduced complexity.
- Removed performance bottlenecks.
- Updated and revised future-proof data model, which can cater data to different modern BI tools, including Power BI.
- Upgraded and enhanced dashboard and reports design with improved usability and performance enticing end-user away from their cottage industry solutions and back onto a fit for purpose enterprise platform