Apex NZ

From 7 hours to 7 mins: Transforming mission critical data processing for Apex NZ

Apex NZ is pioneering new heights of efficiency and data management with the transition to a cutting-edge modern data platform.

With Qrious alongside guiding the design and assisting with the migration, they are now producing complex tax reporting for over 1 million investors more than 50 times faster than a year ago. Apex NZ now has a data platform that will enable them to scale their service another 10x with speed, accuracy and confidence.

Apex NZ is New Zealand’s leading provider of investment administration services for dozens of investment product providers throughout the country. Established in 2001, the Kiwi company MMC (now part of Apex Group) built its own technology platforms from the ground up. Quietly innovative, it is characteristically forward-thinking and has long used technology to drive efficiencies and scale. Their driving force is to improve the experience for clients and their client’s customers – over a million Kiwis investing for the future.

In recent years, Apex NZ has grown phenomenally – the number of investors on their platforms has more than trebled – with no sign of slowing. As the business has grown, so have the demands on its in-house developed platforms, with reporting and data extracts straining to keep up. Like most B2B platform providers, Apex NZ’s clients have an insatiable appetite for data that is both accurate and more real-time than ever.

Qrious worked closely with the team at Apex NZ to migrate its reporting capability to a modern cloud data platform that can seamlessly and efficiently scale with the business as it continues to grow. The ultra-fast and efficient solution provides the capability for Apex NZ to continue growing with pace and confidence.

I’m really happy with Qrious’ contribution, their guidance and their expertise. They've definitely pushed us forward and our team is stronger for it.

– Josh Arnold, Chief Product & Technology Officer, Apex NZ

The challenge

Apex NZ (previously MMC) was a typical small-to-medium NZ business that achieved “overnight success” by investing strategically in software over more than a decade. It has progressively developed, improved and extended its platforms to support its operations, clients and their investors.

As with many organisations that do in-house software development, what was fit for purpose in the company’s early days eventually becomes a bottleneck for future growth. By 2022 Apex NZ had really outgrown its ‘heritage’ reporting solution and needed a modern data architecture that was better suited to the massive scalability that can be achieved with cloud services.

Josh Arnold, Chief Product & Technology Officer, Apex NZ

“It’s really the curse of success,” says Josh Arnold, Apex NZ’s Chief Product & Technology Officer. “Things that worked perfectly fine when we had 30,000 investors on the platform started to creak a bit when we reached 300,000. By the time we were approaching a million investors, things were really starting to hurt.”

Apex NZ’s reporting relied on outdated SQL Server Reporting Services (SSRS) tooling, with the Nexus Fund Administration platform’s operational data store as the source. This meant that analytical needs like reporting and other data extracts would sometimes clash with lots of daily operational processes. They moved to an Always On Enterprise Cluster in 2021, with reporting needs funnelled to a read-only secondary. This improvement got them through 2022, while they were busy onboarding BNZ and KiwiWealth as well as handling the growth of their existing clients. “We understood very well that what got us here won’t get us to where we want to be,” says Arnold.

One of the biggest ‘crunch’ processes for the whole business is the annual year-end tax reports. Tax reporting is complex. It pulls in vast amounts of data and performs a massive number of complex calculations with over 4000 lines of SQL and 21 associated objects, all of which needs to be checked and verified as accurate. Apex NZ produces reports on behalf of its more than 50 clients based here in Aotearoa and involves disseminating tax certificates to over a million investors.

To make it work in April-May of 2022 required manual workarounds, cutting the batch size into small enough chunks to get the reports to run. The Apex NZ team worked long hours to get reports out on time. Doing this was tedious and tricky, and Apex NZ was particularly concerned about burning out key people doing this manually.

The process put massive pressure on the whole Apex NZ team to deliver reports to clients accurately, securely and on time. “We got through the previous tax year end and everyone looked at each other and said, ‘Let's not do that again’; it was just too painful,” says Arnold.

The Product & Technology team that Arnold leads at Apex NZ had less than 12 months until the next reporting season to migrate the process from SSRS to something more scalable and efficient: they also wanted a solution that could enable safe and secure sharing of data with clients; could manage the sheer volume of data with ease; and could develop and grow alongside the business.

Apex NZ turned to Qrious, already a trusted partner, for advice and help to chart a way forward.

The solution

Apex NZ’s existing architecture consisted of its in-house developed Nexus Fund Administartion platform and a separate Wealth Administration platform (Artios).

While Qrious wasn’t familiar with the Fund Administration side of Apex NZ, it understood the Wealth Administration platform well, having advised one of the Big 4 banks on the technology some years prior. It was a simple data mart using Wherescape RED and on-premise SQL. It was lifted and shifted into Azure when Apex NZ acquired it, but in the years since it was first designed, technology has advanced considerably.

Apex NZ invited Qrious to recommend a solution for a Data Platform that would combine wealth and fund administration analytical and reporting needs.

“Qrious’ response was refreshing,” says Arnold. “Instead of sticking with the status quo and saying, ‘you're already using this technology, so just use more of it or tweak it’, they came at it completely fresh.”

Qrious proposed an entirely modern data platform using a Snowflake data warehouse solution, with DBT for modelling and transforms with FiveTran for ingesting data. Tax reporting provided the perfect opportunity to demonstrate the immediate benefit of the new technology to the business.

“Before we got too far into it, we did bottom out some potential showstoppers along the way,” says Arnold.

Before agreeing on the target and setting off, the team built confidence by testing that it was possible to transition from T-SQL to ANSI-SQL. This could have been a blocker if we had been using a lot of proprietary T-SQL functionality. From a couple of proof points with some more complex reports, we felt this risk was manageable. It wasn’t going to be a simple copy/paste though. Every report would need to be partially rewritten. In the main though, this was an opportunity to do some optimisation and improvements as part of the migration.

Security and privacy were also critical considerations. By its very nature, investment administration involves lots of highly sensitive data. The team completed a data and privacy impact assessment up front, to ensure that the Data Platform design, development and operation and improvement would address any security and privacy concerns.

Over several intense months, Qrious worked closely with the Apex NZ in-house Data squad to procure and stand up the platform, including the work to ensure privacy and security was thought of and built-in from day one, with modern Role-based Access Controls. Then they began the work of slicing up and translating the hugely complex SSRS tax report over to DBT Cloud, with Fivetran to extract and load the data into Snowflake. The finishing touch was PowerBI, which was used to analyse and validate the output, replacing a set of macros that the team used to use for this.

Apex NZ was ready to run its tax year-end reports.

In my role, I understand the cost of delay very well – and the value for us of moving faster is huge. Qrious provided a way of getting to where we want to be much faster. Sure, we could have probably gotten here ourselves eventually. Probably with a few dead-ends, and a bit of feeling our way in the dark. In business – and Product Development especially – time is money.

— Josh Arnold, Chief Product & Technology Officer, Apex NZ

The results

In time for the tax reporting season, Apex NZ and Qrious team put in place a modern data platform that effortlessly produces year-end tax reports and the foundations for Apex’s current and future data and analytics needs.

“Seven hours to seven minutes: that has been the impact of moving us across to the modern data platform,” says Arnold. “And it’s been massive.”

With the old system, it would take a staggering seven hours to run a tax year-end report for one of Apex NZ’s bigger clients. “And half the time it would fall over three-quarters of the way through and we'd have to rerun it,” says Arnold. “It was a super painful process.”

On the modern data platform, Apex NZ can run an entire report for as many as three big clients and a handful of smaller clients in seven minutes. The new pace is a game-changer.

The new system has relieved a significant amount of pressure on the business. The process can be run multiple times a day, reflecting changes in the source data with minimal lag. In fact, it has introduced a much-needed iterative ability; if a mistake is spotted, it can easily be corrected at the source, the change can be logged and the report can be rerun.

“We can go back to the start to fix the ingredients and then rebake the cake in minutes, rather than just adding a layer of icing at the end to cover over the holes,” says Arnold.

Data protection and privacy has been addressed by implementing a robust security model. The team are keen to start using Data Sharing with clients to reduce the number of reports that are produced with copies of data sent daily when clients could just connect directly and read the data they need when they need it.

With the modern data platform’s inbuilt governance and comprehensive security protocols, Apex can be confident in its reporting and in the protection of its data.

The tax year-end report proved the power of the platform. Now Apex has dozen more reports lined up to move across.

“Whilst this has been a huge success and a relief for the Product and Technology team, we're really just at the beginning of our data platform journey,” says Arnold. “This is a crucial building block for our future, and I’m excited to see what new capabilities we will build for clients on top of this.”

This whole process after fixing at source, updating the changes in Snowflake and running the reports again all of this can be done in 15 minutes.

Getting advice and assistance from Qrious helps you avoid poor choices and wasting money along the way. The data platform landscape is rough and there’s hundreds of ways to waste time and money. But Qrious have roamed all over that landscape. They can help you plot a direction that suits your business and can keep you away from the cliff edges and potholes along the way. When the terrain is complex, it helps to have experts guide you in the right direction.”

– Josh Arnold, Chief Product & Technology Officer, Apex NZ

Learn about our cloud solutions

If your business is looking at being more intelligence-led, let us know and one of our consultants will contact you to see how we can help.