By Peter Muldoon, Product Strategy Director, FundGuard
When performing crucial investment accounting tasks, generating accurate, up-to-date information is paramount. Maybe not a very bold opening statement, but it has to be said. Afterall, if it’s not accurate then it is really not accounting data. The more challenging part of this statement is the up-to-date part, because current investment accounting operations are under heavy levels of stress due to our changing definitions for what “up-to-date” means.
Maintaining accuracy while meeting real-time expectations of the evolving modern industry is the current investment accounting dilemma. However, as this article will explain, changing the type of database used by your investment accounting software can be an impactful step toward addressing this so-called dilemma. This article is intended for the business user and will put the functional spotlight on a few core SQL system weaknesses that are solved by several notable NoSQL strengths.
Most legacy accounting systems rely on Structured Query Language (SQL) databases. These databases are table-based with predefined schemas that determine how data is organized and the relationships between different pieces of data. For those of us who have been on the software side of industry long enough, it would not be uncommon for many of us to think in terms of relational data table structures as a “given” for proper data organization.
As expectations of investment management operations have become ever more digitally oriented (the data must be fit for purpose: ready to be consumed and fully accurate), the trusted, decades-old SQL database systems are proving to be a hindrance to the industry’s growth and efficiency — and can unintentionally create data silos that make it exceedingly difficult to contextualize accounting activities across to real-time information channels.
Fortunately, better data capabilities now exist.
NoSQL databases— also known as Big Data databases — can greatly boost the efficiency of your investment accounting operations and enable real-time processing – meaning the kind of processing that does not require more processing to reach the ultimate outputs.
Unlike SQL relational databases, NoSQL databases are designed to handle large volumes of data and do not require a structure or what’s technically called a ‘schema.” Also, NoSQL databases are often well-suited for big data systems because of their flexibility and ‘distributed’ framework. As big data can be, well…big, a distributed file system is designed to store large amounts of data across multiple storage devices. This has a whole other set of advantages that will help decrease costs and operational resilience.
So What Does this Mean From a Business Perspective?
Due to the distinct design of NoSQL databases, today’s cloud-native software systems that are being built from the ground-up with a big data approach can offer many practical advantages in support of cross-enterprise digitalization.
NoSQL databases are designed for immutability, meaning that information stored within the database can be continuously versioned so that no balance value is ever overridden. Being immutable means that a database can maintain an entire historical account of information such as portfolio balances, for each day and even more frequently, such as after each trade or corporate action event, etc. This gives users full investment accounting visibility for any point in time, without any processing overhead.
In contrast, SQL databases are designed as mutable and store the most current balance values by overriding the previous balance values (saves disk space, but not so helpful to the user). Further, historical balances are typically saved in an SQL based system by adding the extra step of batch processing to make copies of key balances once a day and must be carefully timed before new activity is applied and modifies those core table balance values. Tedious.
Inefficient and heavy overhead to source the configuration settings needed for key processing calculations is the norm for SQL databases. This required time and processing power can hinder the delivery of investment accounting outputs against modern digital expectations.
When using an SQL database, it’s essential to layer several different processes to reach the goal of the pinnacle calculations like total net assets (TNA) or a mutual fund’s net asset value (NAV). In fact, it is often necessary to order the entry of investment events and activities, and to add layers of controls to double-check those calculations for accuracy. This often leaves the business user waiting for numbers to be “good,” which tends to only happen at certain points in time during the course of a day after all layers have been run and validated.
Comparatively, systems built from the ground up using NoSQL databases are always solving for the end-point – the pinnacle calculated values. To be digital in many ways simply means an entry-to-endpoint design, aka real-time.
Let’s consider a common price correction made to an investment portfolio. Business users need the ability to easily view initial and corrected prices, see the differences between them (in percent and amount), be alerted if they are too high; understand how that price change impacts the market value of the holding, as well as the value of the fund or whole portfolio; and see alerts if those changed values would be above prescribed tolerances. To be truly digital means the user has all this information without doing anything other than entering the corrected price. NoSQL enables this.
NoSQL databases are always calculating for the endpoint, enabling real-time capabilities to perform all of the necessary calculations for any given scenario in one fell swoop. This is vastly more efficient than having to carry out a multitude of different processes for generating calculated accounting data, and then verifying its accuracy. In a modern NoSQL based system the entry of that price correction creates a new version of the portfolio holding’s market value, new versions of the funds or accounts NAV or TNA, as well new alerts due to any breaches from evaluation results of data quality controls.
In a traditional SQL-based system, none of the above is possible. Instead there tends to be a process to enter a price, another to value portfolio holdings, another to value a fund, and other control processes to validate the changes. At best you might be able to chain a few processes together, but it is a multi-step approach, each with sourcing overhead, and a generally accepted waiting game by business users until the data is good.
It is often said that NoSQL databases are designed for use by cloud-native systems and as such, they can tap into a tremendous amount of cloud processing power (aka ‘elasticity’). While that is true, it does not tell the whole story. The fact is that NoSQL databases are designed differently and as a result they don’t need the same levels of processing power as their SQL counterparts.
This is due to the capability strengths of NoSQL databases that are optimized to add records very quickly, and to not have limits in record and table sizes. From a system design perspective, this changes everything.
Rather than traditional processing that first sources configuration settings needed to calculate values from many different tables, a NoSQL database can carry all of the necessary configuration settings within the record that is being calculated. Carrying that information on the record means there is minimal time and effort to fetch the data needed to do big accounting engine things like accrue interest and amortization values.
Secondly, establishing the correct starting balances on which to accrue things like interest and amortization is also of minimal time and effect because the needed balance values, like portfolio quantity and cost, are also sitting in the same table as different versions of the same calculated record.
NoSQL databases are known to be designed specifically to expect high processing and data volumes, and the above-defined design difference is a main reason behind why that is true. In contrast, SQL databases are optimized for records edits (not adds); for minimal data storage where they must rely on complex table joins to source the data needed for calculations (lots of processing overhead); and where end-point calculations only execute when they are called, and when they are able to go.
Just as modern cloud-native software designers using NoSQL databases learn to think very differently, so too must investment accounting teams re-think the status quo in order to create change. When deciding whether or not to switch to a NoSQL database for investment accounting, some of the biggest advantages can sound technical and may not translate into practical benefits. Buzzwords like “immutable,” “real-time” and “high volume processing” can easily distract, but what they ultimately mean to the business side of investment accounting operations is:
Put another way, using modern core accounting software that is designed around a NoSQL database is really the only way to achieve true digitalization.
FundGuard’s cloud-native investment accounting solution enables you to gain real-time data insights.
FundGuard leverages a reliable NoSQL database that feeds all other components of your investment management system with accurate and up-to-date information, enabling more efficient, flexible, and scalable investment accounting processes for asset managers and their service providers.
Contact our team today to learn more.
In ten minutes you’ll learn:
☑ The difference between FundGuard data storage and traditional relational databases
☑ The flexibility and extensibility of FundGuard data records
☑ Immutable record-keeping and audit trails
Register here to stream on demand.