Joshua Arnold (Apex NZ)
An upgrade to modern data platforms has supercharged investment administrator Apex NZ's ability to handle complex tax reporting for a million investors.
What was a painful process a year ago is now both more accurate and 50 times faster, with room to scale another 10-times, Apex said.
“In recent years, we’ve grown at a phenomenal rate, placing higher demand on our in-house developed platform," said Joshua Arnold, Apex NZ’s chief product and technology officer.
Things that worked well when Apex had 30,000 investors were starting to creak when the firm reached 300,000. By the time it got to a million investors, things were really starting to hurt.
"We were straining to keep up with reporting and data extracts; and client demands for data that is both accurate and more real-time," Arnold said.
One of the biggest "crunch" processes for the business was producing year-end tax reports, a complex affair pulling in vast amounts of data and performing a massive number of complex calculations, each of which needed to be checked.
Apex NZ produces reports on behalf of more than 50 clients that involves delivering tax certificates to over a million investors.
That process relied on older SQL Server Reporting Services (SSRS) tooling, with the operational data store as the source. This meant analytical needs such as reporting and other data extracts were clashing with lots of daily operational processes.
“To make this work in April-May of 2022 required a lot of manual work-arounds; cutting the batch size into small enough chunks to get the reports to run," Arnold said.
The team worked long, tedious hours to get reports out in time, causing concern about staff burn-out.
The Apex NZ product and technology team had less than 12 months until the next reporting season to migrate the hugely complex process from SSRS to something more scalable and efficient.
The company turned to Qrious, Spark business group’s AI and data innovation unit, which was already a trusted partner, for help to chart a way forward. Qrious proposed an entirely modern data platform using a Snowflake data warehouse solution, with DBT for modelling and transformation with FiveTran for data ingestion.
Tax reporting provided the best opportunity to demonstrate the immediate benefit of the new technology.
“The team built confidence by testing that it was possible to transition from T-SQL to ANSI-SQL," Arnold said.
This could have been a blocker if Apex had been using a lot of proprietary T-SQL functionality, however, from testing a few of its more complex reports, the team felt this risk was manageable.
"A partial rewrite was an opportunity to do some optimisation and improvements as part of the migration,” Arnold said.
The other key risk was around privacy and security. The Apex NZ team adopted a “shift-left” approach, requiring security to be built into the new system from the get-go.
Over several intense months, Qrious worked with Apex NZ’s in-house data squad to procure and stand up the platform, including working to ensure privacy and security was considered and built in 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.
With the old system, it would take seven hours to run a tax year-end report for just one of Apex NZ’s bigger clients. Half the time it would fall over three quarters of the way through and have to be rerun, Arnold said.
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 team is now keen to start using data sharing with clients to reduce the number of reports that are produced and sent daily. Instead, clients should just be able to connect directly and read the data they need when they need it.
Apex NZ now has dozens more reports and other analytical needs lined up to migrate over to Snowflake and DBT.
“The mission now is to keep the momentum going, delivering value early and often for clients," Arnold said.
"Our clients have an insatiable appetite for data; they want more of it, more quickly, more real-time, while still being accurate and secure. Data is hugely valuable to their decision-making."
