IDEXX Laboratories’ mission is to develop and manufacture innovative diagnostics and information-sharing products and services that keep animals healthy, and keep milk and water safe. Its solutions help people and animals thrive, whether that’s by detecting and eliminating water-borne diseases, diagnosing early-stage kidney disease in household pets, optimizing milk yield for dairy farmers, or providing other practical uses of technology, education, and knowledge.
IDEXX customers, including small-practice veterinarians, multinational agriculture companies, and public sector organizations, place a high value on the information that IDEXX produces for them. Many customers require a quick turnaround to receive diagnostic test results within the 30-minute window of a veterinarian appointment, which emphasizes the importance of returning timely information.
A Need for Better Business Planning
This rapid customer service demand contrasted with IDEXX’s business planning environment, where analysts were accustomed to waiting longer than half an hour for complex reports. “Users were experiencing widespread performance issues; some reports would run for 30 minutes and then time out uncompleted,” says Prem Kurumoju, IDEXX Laboratories Application Analyst. “We needed the reports to be fast — rather than have users wait for information that, in some cases, was never returned.”
Today, IDEXX runs a host of SAP solutions. In addition to SAP Business Planning and Consolidation (SAP BPC), its current SAP applications include SAP ERP and SAP Business Warehouse (SAP BW) running on SAP HANA. IDEXX originally implemented SAP BPC 10.0 back in 2012, and subsequently took several steps to improve reporting and system performance. These steps included migrating a non-SAP database, which was integrated with SAP BW and SAP BPC, to a scale-out server architecture. While this step delivered moderate performance gains, the improvements were soon offset by IDEXX’s desire to run more reports and extend the planning and consolidation functionality.
In the next step, IDEXX took measures such as changing the coding language used in back-end scripts and remodeling of hierarchies, but performance roadblocks and lags continued in SAP BPC. For example, application users were accustomed to retrieving and storing transactional data for later analysis, which entailed time-consuming calculations, assessments, and data preparation.
Longer and longer wait times soon became the norm, especially for complex reports written on the multidimensional expressions (MDX) query language for online analytical processing (OLAP). When reports would crash, financial analysts and accounting teams had to query smaller, more manageable data sets and then cobble reports together using spreadsheets. “It was especially frustrating for users when they ran a lot of queries and needed to verify a report’s validity to close the books for the month or quarter,” says Prakash Sethia, IDEXX Laboratories Application Analyst.
A Multi-Pronged Business Case
In 2014, IT presented a business case for an upgrade to senior management that impressed upon them the need for enhanced system performance and functionality, an improved user experience, and increased user adoption with the promise to implement single sign-on capabilities.
The main reason IDEXX decided that SAP HANA would end its performance roadblock is that the in-memory processing power of SAP HANA moves OLAP logic to the database layer rather than the application layer, removing the complexity and redundancy from having to manage consistency between multiple aggregate tables and source data.
“However, we couldn’t sell this project to the business based on performance alone. Performance improvements were important, but the system was running and despite how increasingly frustrating it might be for users at times, they had become accustomed to it,” Kurumoju says. “An improved user experience was a key additional selling point; a lot of reports are run by cost center managers and other infrequent users who often complained that it was difficult to log into the system.”
With the promise of an improved user experience as the finishing touch, senior management approved the upgrade project, and IDEXX set out to migrate SAP BPC off its non-SAP database to the existing SAP HANA database and upgrade the application from version 10.0 to 10.1.
Removing complexity was easier said than done for IDEXX, however, as two key challenges became clear early on. First, because IDEXX had aggregated a huge amount of data (with close to 280GB of data to migrate), the company realized that a standard migration method (using the UJBR export/import functionality) wasn’t going to work — even after a meticulous data cleansing and archival of all audit data older than one year. Large data volumes further complicated the second challenge: the project team guaranteed that a cutover to SAP BPC 10.1 running on SAP HANA could be done without any business disruption or downtime.
With finance users, downtime is a critical factor. They are hesitant to bring the system down for any lengthy period, so they were appreciative that we could do this without any downtime considering the huge amount of data involved.
— Prem Kurumoju, Application Analyst, IDEXX Laboratories
Adopting a Non-Standard Approach
After a few stops and starts, IDEXX found an alternative migration method that stratified the export data and used the UJBR transaction to move only master data and metadata. For transactional data, IDEXX used a data transfer process (DTP) for migrating data from one business warehouse to another. For exporting 180GB of audit data, the company used SAP Landscape Transformation Replication Server, which automatically moved data from source tables to destination tables. “This strategy reduced the cutover time significantly, but it still would have meant about a 12-hour downtime for the business, and we had promised zero downtime.” Kurumoju says.
To keep the zero-downtime promise, the project team adopted a phased migration approach. It froze changes for any data beyond the current year — the project began in mid-2016 — and migrated this data during the weekend before the May 15 go-live. When it came time for the midnight cutover, IDEXX only had to transfer current-year data and was up and running within a couple hours.
“With finance users, downtime is a critical factor,” Kurumoju says. “They are hesitant to bring the system down for any lengthy period, so they were appreciative that we could do this without any downtime, considering the huge amount of data involved.”
We were expecting performance to come down at roughly a five-fold improvement, but what we saw blew our expectations out of the water. A report that used to run for 20 minutes was now finishing within 10 seconds.
— Prakash Sethia, Application Analyst, IDEXX Laboratories
Being Proactive Pays Dividends
Achieving zero downtime, however, did not mean that IDEXX neglected putting SAP HANA through its paces. The project team completed rigorous schedules of performance testing prior to go-live, running reports with 20 concurrent users, for example.
“A lot of companies assume that once they upgrade to SAP HANA, everything is going to be fast,” Kurumoju says. “We set out with the same expectations, and the reports we tested were indeed fast. But we then spent a lot of time on performance testing to really show us what SAP HANA could do and what it couldn’t do. That’s a key learning point because you never know what’s going to work — performance testing is always a crucial step.”
Having done performance testing, it didn’t take end users long to discover that SAP HANA does deliver on promises to eliminate performance bottlenecks. “We were expecting performance to come down at roughly a five-fold improvement, but what we saw blew our expectations out of the water,” Sethia says. “A report that used to run for 20 minutes was now finishing within 10 seconds. People who used to take long coffee breaks while waiting for reports to complete were now astounded at how quickly they were finished.”
The upgrade to SAP BPC running on SAP HANA also facilitates IDEXX’s goal of extending planning and consolidation functionality by implementing balance sheet and cash flow statements directly in the application. “Moving to SAP HANA fulfills the business requirement of having balance sheet and cash flow statements on SAP BPC,” Kurumoju says. “Those are complex models, but the processing power of SAP HANA takes away the complexity by removing the need to write a lot of business rules around the data calculations. This upgrade also made the business case and helped construct the plan for the ongoing SAP ERP migration to the SAP HANA database.”
Because SAP HANA enables real-time calculations and direct reporting at the database layer, the previously time-consuming data retrieval and storage steps go away, saving not only time and resources, but valuable database storage requirements as well. “Storage is money, and with the volume of data at IDEXX, bringing it all on the SAP HANA database would have been counterproductive to our migration strategy,” Sethia says. “Data assessment was important to achieve zero downtime, and also to know what to archive and compress so we’re only using the size we need.”
IDEXX partnered with a third party to arrive at a single sign-on solution and relieve the bottleneck from multiple users attempting simultaneous access of the SAP BPC application during peak demand times. “There wasn’t a standard solution for that single sign-on," Kurumoju says. “Our partner helped us design a custom intermediary service that can run reports from Microsoft Excel without having to enter a password into SAP BPC.”
This enhancement, according to Kurumoju, was icing on the cake for the overall successful upgrade. “We pretty much hit all the right notes for this project,” he says. “The business was very happy with the upgrade and gave us some great reviews, especially about the performance and the impact that single sign-on had on their day-to-day lives.”
