NHS Scotland sees performance improve by up to 500 times with new real-time reporting solution
“The implementation of the PDA (Netezza) solution alongside eFinancials, addresses a long-term issue around the frustrations and concerns regarding the capabilities of BI."
by Published on 18 April 2024 5 minute read
About NHS Scotland
NHS Scotland is the publicly funded healthcare system that provides safe, effective, and person-centred health and care services to the people of Scotland. Established in 1948 as part of the UK’s National Health Service, it now employs more than 180,000 staff across hospitals, community services, and specialist care, making it one of Scotland’s largest employers. NHS Scotland’s mission is to improve health and wellbeing, reduce inequalities, and deliver high-quality care free at the point of need, while continually adapting through innovation, research, and digital transformation to meet the changing needs of patients and communities.
The Problem
NHS Scotland’s finance reporting was hampered by an overloaded Oracle database. At peak times, reports took hours to run, slowing down operations and forcing staff to schedule overnight reporting, which limited access to real-time data and created widespread inefficiencies.
The Result
OneAdvanced's Financials Software.
The Solution
By moving reporting to IBM’s Pure Data for Analytics (Netezza) alongside eFinancials, NHS Scotland achieved dramatic improvements—reports now run 10–50 times faster and up to 500 times faster in some cases. This freed up staff time for analysis, improved day-to-day system performance, and unlocked access to real-time insights.
The Challenge
We have one database, eFinancials from OneAdvanced, with around 2,000-3,000 accounts which results in a system concurrency of around 800 users at any one time. On a typical day, we would run between 2,000-3,000 reports, a lot of those being at peak times - this results in around 55,000 reports per month.
Our National Finance Systems Manager Colin White explains the challenges: “Our underlying Oracle database was struggling to cope; we were getting many problems with it just hanging, especially during peak times of the reporting period. Not only did it affect our report performance using Business Objects, but we also had operational issues around this database - people were finding difficulties transacting against it. We felt we needed to get the reporting off of that database.”
The BI queries/reports were being run against the live system and this was affecting the operational performance. At peak periods, individual reports were taking hours to run, and users were resorting to scheduling reports overnight, thus limiting access to real-time BI information. We found that the Boards were using the BI tools to do ad-hoc repetitive enquiries, which were overloading the system. We needed quicker access to real-time data against the full reporting dataset, without unduly affecting the performance of the main operational database.
Finding a solution
When we approached OneAdvanced looking at possible solutions they suggested they should first examine what our real requirements were, so they came on site and conducted an Optimisation Study. This allowed us to look at the issues in a holistic way so we could review options that would truly meet our needs within the agreed budget.
One of the key issues identified was confirmation that we needed to move towards a solution that allowed us to have a separate reporting database – and it needed to be realtime. OneAdvanced evaluated the possible optionsin the market and recommended IBM’s Pure Data for Analytics (PDA), formerly Netezza, as a potential solution.
Colin elaborates, “We weren’t too sure about this at first as we hadn’t heard very much about PDA (Netezza), but we did some research online and found there were a lot of case studies where people were reporting they were getting 100-500 times better performance. So we became very interested, and asked OneAdvanced for a proof of concept so we could have a closer look and ensure it would meet our requirements. OneAdvanced approached IBM and we received agreement for a loan of the appliance for a one-month proof of concept. The PDA (Netezza) server spreads the load of the query over multiple processing units and multiple disks. Once you get the appliance set up and the database has been replicated across, you can actually start using it straight away, you have no complex design of a reporting database or anything like that.”
Key criteria for success
Together with OneAdvanced, we came up with some key criteria for the success of the proof of concept:
- Firstly, that the existing BI reports could be run against the PDA database with minimal changes to the existing universes
- The solution should also deliver notable improvements on return times for BI reports - a minimum of 10 times faster
- It needed to be stable and not require significant resources to maintain services
- There should be no detrimental impact on the operational database due to the running of data replication routines
- The PDA (Netezza) database needed to be as near real-time as possible – with the expectation of under 5 minutes Extract Transform Load (ETL)
- We wanted to see an improvement in the normal day-to-day operational performance of the eFinancials service
- Finally, there should be no detrimental impact on the BI server performance or network performance
The Testing Plan
OneAdvanced created a Testing Plan with key users from all 22 Health Boards, these were our BI Champions at Board level, in some cases first line support. Recurring, resource intensive reports were selected that supported the monthly reporting cycle. Boards were running reports from both the Oracle and PDA (Netezza) databases. Outputs were reconciled and run-times compared. We produced an anticipated outcome, which said we would find the PDA (Netezza) solution resilient and requiring minimal support. We also believed PDA (Netezza) reports would run in a fraction of the previous run-times and without any errors. We felt we would see no change to the BI functionality or BI server performance as a result. We anticipated the data would reconcile back to the Oracle supported universes, and that data replication would continue to operate within our accepted three-minute window from the operational database.
The benefits we have seen
Colin White explains the results of our testing: “One of our provisos was that this had to be as near real-time as it could possibly be, so the ETL to the PDA (Netezza) database from the Oracle database had to be within a few minutes. When we had that in place and started running reports, we were amazed at how quickly the actual reports were running, we were getting performance improvements on average of around 10 to 50 times better and in some instances 500 times better - which far exceeded our expectations. This in turn was an enabler to allow further development of reports providing more informed analysis - previously we would not have attempted this due to the length of time taken to run reports.”
One of the benefits our users are now seeing is they no longer have to spend all their time running reports, and waiting for them. This frees employees up to spend time on more value-added activities, especially looking at the actual results of the reports and analysing the information produced. Any user can go in, design and run any report that they like, it will finish – we don’t have to watch it and wait for it any more. We are now looking at what additional reports we can do, and how we can get more information out of the system that we could never have envisaged getting before.
Moving the reporting off the operational database onto the PDA (Netezza) appliance has allowed us to lift a massive load off the actual operational database, which is now freed up for normal transactional processing, so we are also getting many benefits from improved performance on the day-to-day usage of our eFinancials system. Colin concludes, “The implementation of the PDA (Netezza) solution alongside eFinancials, addresses a long-term issue around the frustrations and concerns regarding the capabilities of BI. It also alleviates the need to design a more efficient reporting database, and helps with day-to-day usage, as there is not the same requirement to monitor and enforce best practice report design. It has made us more efficient and is helping us add value to our processes.”