Family Offices

Data transformation & storage: 4 key considerations for family offices


Today’s family offices want to deliver a strong risk-adjusted investment performance that is aligned with family members’ priorities. To prove the value they’re adding, they also want to be able to communicate that performance, and the value of holdings, to families.

This is where consolidated reporting comes in. Rich, consolidated reporting provides an aggregated picture of the family’s holdings, across investment allocations, performance and risks. It is not easily achieved, however, and is reliant on the successful execution of three key processes: data sourcing, data transformation and storage, and data enrichment.

First things first

Data can be sourced in different ways, but regardless of how it is sourced, the data used by family offices will inevitably have:

  • Dozens of possible file formats, such as text, CSV, Excel, PDF and XML.
  • Variations in the content – such as the data within it, the order the columns are in, and the identifiers used to recognise certain positions.

This disparate information needs to be transformed into a single standardised schema, which can be processed systematically for the family office’s downstream reporting. And once the data is harmonised, it has to be stored – in a database.

With that in mind, here are four key database considerations for family offices:

1. Vendor

Database solutions are available from a number of software vendors, including AWS, Microsoft Azure, Oracle and MongoDB. Compare the solutions from the different vendors to find the best fit for your family office. Then, having picked a vendor, create the database using an appropriate database architecture.

2. Architecture

The architecture determines how data will flow from the files into the database and what columns in each file need to be mapped to which database field. Data validations must be embedded throughout the process to avoid importing incorrect data by accident and polluting the database.

Unique keys are essential for maintaining unicity. For instance, where two banks have an identifier in common for an instrument, but use slightly different naming conventions, the identifier needs to be mapped into a single object. Foreign keys dictate the relationships between different tables in the database.

Positions, assets, liabilities and transactions all need to refer back to a single object. Without that relationship and unicity, any downstream calculations around concentration, performance and P&L will be impaired.

3. Design

Crucially, the database architecture must be designed to take account of what the family office wants to show, and what analytics and reporting it will need downstream. For example, it may want transparency around the funds it has invested in, or to replicate the ownership structure of the family office. Bear in mind that if you get the initial database design and structure wrong, there will be considerable costs involved with migrating and redoing it.

4. Skills

Developing a database demands a variety of skillsets, including a software engineer to actually build and implement the database. The family office’s IT engineer is unlikely to possess sufficient knowledge of the financial datasets they are working with. So, the software developer will need help from financial data experts to provide the specifications for what needs to be created. A data engineer may also be required downstream to enrich the raw data received from the different providers with ancillary data sources.

Time for enrichment

Once the data has been sourced, transformed and stored in an appropriate database, it is then ready for enrichment. Enrichment is the third step on the journey towards consolidated reporting – and a vital step as it brings additional colour and insight into the performance of the family’s holdings.

To find out more about how family offices can overcome data management challenges, read our whitepaper below.

New call-to-action

Related Content

17 Nov 2022 | Family Offices

5 ways technology can help family offices navigate volatile markets

After almost two years of record returns, market volatility and the looming threat of recession has left family offices anxious. Amid continued inflationary pressure and lower expected returns, some are looking to increasingly exotic portfolio...

26 Oct 2022 | Family Offices

Family Office Risk Management in Uncertain Times

In recent years, family office portfolios have benefited from robust economic growth, relative geopolitical stability, persistently low inflation and interest rates, and relatively low volatility. But family offices, just like other financial market...

6 Oct 2022 | Family Offices

Family Office Portfolio Management: Choosing the right platform

As a family office, deepening relationships and growing the business is your lifeblood. In today’s fast-moving environment, a robust technological backbone is critical to success. Uncertainty in the financial markets is increasing attention on...

Related content


17 Nov 2022 | Blog

5 ways technology can help family offices navigate volatile markets

As family offices deliberate on how best to navigate the choppier waters ahead, here are five ways that data and technology can help you stay on the front foot in volatile markets.

Read blog arrow-read-more

26 Oct 2022 | Blog

Family Office Risk Management in Uncertain Times
If you're looking to enhance your family office's risk capability, explore everything you need to consider here.
Read blog arrow-read-more

6 Oct 2022 | Blog

Family Office Portfolio Management: Choosing the right platform
Discover what you should be looking out for when choosing a platform for your family office.
Read blog arrow-read-more

Discover the power of Sesame