Strategic Data Integration Platform
National Prescription Service (NPS)
Project Synopsis
Delivering a fit for purpose platform to enable and sustain data driven decision making.
About this project
National Prescribing Service (‘NPS’) was a federal government funded research organization – which came to an end in December.
NPS was primarily running on-premises SQL server database for its warehouse load. DW loads were resource heavy and long running. Loads were initiated monthly, but in case of failed, there was no process in place to resume/restart the failed process.
The intent of the project was to review existing DW process flow and build a cost-effective solution that is industry standard.
A solution was provided that migrated data from on premise DB to snowflake and replaced the native ETL tool with Talend.
This enabled the DW load to run on a weekly basis with room for troubleshooting any failure and resume/restart jobs. Compared to a monthly run for 10-14 days involving some manual steps, the new solution enabled team to run the same jobs in 10-14 hours with no manual steps involved. As a result, the frequency of the monthly job was changed to weekly.
synogize established an outsourced support service to ensure high system availability, issue resolution, and provide guidance and advice on strategic solution direction.
Client Success Story
About the client
NPS MedicineWise was established in 1998 as the National Prescribing Service (NPS) Limited. Through two decades of continuous national leadership and services provided in the health sector, NPS MedicineWise focus is on supporting quality use of medicines to improve health decisions and health and economic outcomes in Australia.
After 24 years championing the quality use of medicines in Australia, the NPS MedicineWise Board made the difficult decision at the end of 2022 to cease the company’s operations.
Business Challenge
NPS acquired and ingested medicine prescription data from General Practitioner (GP) practices across Australia to support a number government and research initiatives to provide better medicine choices for all Australians.
To support these initiatives a repository was created of primary care data to assist the GPs with understanding their clinical activity, how conditions are diagnosed, and how they are treated.
A key challenge was the large volumes of data that need to be ingested in a timely manner -whilst identifying and resolving data quality issues. A further challenge was that the patient data is managed in a highly distributed manner by over 7,000 independent GP surgeries.
There was also strong demand to achieve a deeper level of insight, with privacy, security and consent being critical considerations.
Approach
synogize’ approach to addressing these challenges was to establish a new – strategic – data integration platform, designed to enable NPS to manage large and complex data sets, transparently implement data cleansing and data interpretation rules, and to comprehensively expose data quality issues.
Outcomes / Benefits
- Retained the organisation’s IP.
- Replace the outdated on-premises solution with a modern cloud-based solution using Talend and Snowflake technologies and based on Data Vault methodology.
- The new solution enabled reduced time and effort to acquire and analyse data.
- Enabled the business to query data across various subject areas using one dashboard.
- Previous load frequency rate was once per month, this was reduced to daily or weekly.
- Previous solution failures could not be resolved and rerun until the following month – the new solution enables immediate resolution/rerun.
- As the new solution was fully automated it became more cost-effective with overheads reduced by almost 70% per annum
- Establish an outsourced support service to ensure continued high system availability, issue management, and optimization.
Business Issues Snapshot
Removing In House Hardware Bottleneck
- NPS was using an in house on-premises solution with the overall Monthly ETL process taking up to 14 days to run.
- In the event of any failure, it was simply overlooked as another run would not be possible within the month. The resolution would therefore not be possible until the following month.
Reducing Manual Work
- The 14-day ETL process also involved manual steps being undertaken by the support team. There was no automation or process improvement in place.
Control on Scalability of DW Load
- The DW Load was frequently processing a higher volume of data due to the previous months’ failure, or if practices were sending additional data volumes.
- There was no process to avoid this or predict the impact on the load times.
Streamline/Standardize Overall Dataflow
- The solution design of the warehouse and batch processes were outdated and hadn’t been reviewed or optimized for a long period of time.
- This impacted the overall performance and batch load times.
Reporting Solution
- NPS had been using SSRS for generating their reports.
- As these reports were prebuilt there was no provision for business users to have any self-service capability such as slicing and dicing or drag and drop reporting.
National Prescription Service (NPS)
Project Synopsis
Delivering a fit for purpose platform to enable and sustain data driven decision making.
About this project
National Prescribing Service (‘NPS’) was a federal government funded research organization – which came to an end in December.
NPS was primarily running on-premises SQL server database for its warehouse load. DW loads were resource heavy and long running. Loads were initiated monthly, but in case of failed, there was no process in place to resume/restart the failed process.
The intent of the project was to review existing DW process flow and build a cost-effective solution that is industry standard.
A solution was provided that migrated data from on premise DB to snowflake and replaced the native ETL tool with Talend.
This enabled the DW load to run on a weekly basis with room for troubleshooting any failure and resume/restart jobs. Compared to a monthly run for 10-14 days involving some manual steps, the new solution enabled team to run the same jobs in 10-14 hours with no manual steps involved. As a result, the frequency of the monthly job was changed to weekly.
synogize established an outsourced support service to ensure high system availability, issue resolution, and provide guidance and advice on strategic solution direction.
Client Success Story
About the client
NPS MedicineWise was established in 1998 as the National Prescribing Service (NPS) Limited. Through two decades of continuous national leadership and services provided in the health sector, NPS MedicineWise focus is on supporting quality use of medicines to improve health decisions and health and economic outcomes in Australia.
After 24 years championing the quality use of medicines in Australia, the NPS MedicineWise Board made the difficult decision at the end of 2022 to cease the company’s operations.
Business Challenge
NPS acquired and ingested medicine prescription data from General Practitioner (GP) practices across Australia to support a number government and research initiatives to provide better medicine choices for all Australians.
To support these initiatives a repository was created of primary care data to assist the GPs with understanding their clinical activity, how conditions are diagnosed, and how they are treated.
A key challenge was the large volumes of data that need to be ingested in a timely manner -whilst identifying and resolving data quality issues. A further challenge was that the patient data is managed in a highly distributed manner by over 7,000 independent GP surgeries.
There was also strong demand to achieve a deeper level of insight, with privacy, security and consent being critical considerations.
Approach
synogize’ approach to addressing these challenges was to establish a new – strategic – data integration platform, designed to enable NPS to manage large and complex data sets, transparently implement data cleansing and data interpretation rules, and to comprehensively expose data quality issues.
Outcomes / Benefits
- Retained the organisation’s IP.
- Replace the outdated on-premises solution with a modern cloud-based solution using Talend and Snowflake technologies and based on Data Vault methodology.
- The new solution enabled reduced time and effort to acquire and analyse data.
- Enabled the business to query data across various subject areas using one dashboard.
- Previous load frequency rate was once per month, this was reduced to daily or weekly.
- Previous solution failures could not be resolved and rerun until the following month – the new solution enables immediate resolution/rerun.
- As the new solution was fully automated it became more cost-effective with overheads reduced by almost 70% per annum
- Establish an outsourced support service to ensure continued high system availability, issue management, and optimization.
Business Issues Snapshot
Removing In House Hardware Bottleneck
- NPS was using an in house on-premises solution with the overall Monthly ETL process taking up to 14 days to run.
- In the event of any failure, it was simply overlooked as another run would not be possible within the month. The resolution would therefore not be possible until the following month.
Reducing Manual Work
- The 14-day ETL process also involved manual steps being undertaken by the support team. There was no automation or process improvement in place.
Control on Scalability of DW Load
- The DW Load was frequently processing a higher volume of data due to the previous months’ failure, or if practices were sending additional data volumes.
- There was no process to avoid this or predict the impact on the load times.
Streamline/Standardize Overall Dataflow
- The solution design of the warehouse and batch processes were outdated and hadn’t been reviewed or optimized for a long period of time.
- This impacted the overall performance and batch load times.
Reporting Solution
- NPS had been using SSRS for generating their reports.
- As these reports were prebuilt there was no provision for business users to have any self-service capability such as slicing and dicing or drag and drop reporting.