What is Data Warehousing and How Do Financial Firms Use It?

February 25, 2020 - Samrat Malakar

What is data warehousing?

A data warehouse is a central repository that collects information from a variety of independent sources. Sometimes it is called an enterprise data warehouse. 

Data warehousing, then, is the process of aggregating data from disparate sources into one centrally located place and using that historical data to make business decisions. 

Data warehousing is part of business intelligence, which CIO.com defines as “leveraging software and services to transform data into actionable insights that inform an organization’s business decisions.”

Difference Between Database and Data Warehouse

Investopedia says a database is a transactional system that monitors and updates real-time data in order to have only the most recent data available. 

For example, your entire roster of active clients is a database that’s comprised of names, addresses, birthdates, account balances, investment portfolios, net worths, etc. This client database will tell you things like:

You’d use this information to measure how well your clients’ accounts are performing based on their investment goals. 

A data warehouse, on the other hand, might consist of every client you’ve worked with in the past 10 years, along with a full history of their information, the investment decisions you’ve made on their behalf and historical benchmarks. You’d use the data warehouse to look for things like seasonal trends and how they affect your clients’ portfolios. 

Investment decisions can be made with a combination of information from your client database along with historical data from the data warehouse.   

How Data Warehousing Works

Data collection 

Data comes from three types of sources — operational systems, external data sources and flat files. 

Operational systems are those that are used to run your business — financial planning, accounting, project management, client relationship management (CRM), enterprise resource planning (ERP) and transactional systems software, for example.     

External data sources are those where information is not collected by your organization, such as the Dow Jones Industrial Average, market data and the SEC’s databases.

A flat file database contains records that follow a uniform format but there are no relationships between the records. For example, a flat file database could be a spreadsheet or a plain text file. 

Data extraction 

This step involves gathering large amounts of information from the multiple heterogeneous (different and independent) sources from which the data was collected. For example, TAMP1 pulls data from your clients’ custodian(s) and marries that with a portfolio accounting system, CRM, performance system and a risk system. 

You can also plug other things into this platform as a service, such as your own trading system, financial planning tools, etc. That’s the biggest advantage of a data warehouse and TAMP — they’re highly scalable, and as they grow, your operations become more efficient. 

Data cleansing, conversion and sorting

During this automated process, data is cleansed of things like duplicates, incompletes and errors, so it can be converted into your data warehouse’s format and then sorted and summarized. Cleansing could involve removing bad records or identifying them so you can decide whether to remove or correct them.

Records can also be segmented and sorted, especially in an e-commerce setting, where customers might be segmented based on geography, buying habits, or any number of factors.  For investment firms using TAMP1, data is taken from multiple sources, then cleansed, converted, and sorted accordingly into the platform. For example, client assets can be segmented by asset class, region, currency, etc.

Data-driven decisions

In our illustration above, you can see that there are three basic outputs from a data warehouse: data mining, reports and analytics.  

Data mining is the process of turning raw data into useful information, which can then be used in client-facing reports and internal analytics that your advisors can use to make investment decisions on behalf of your clients. 

Reports are delivered to your end users and can include real-time information about how investments are performing. Your advisors can also use reports and analytics to monitor their clients’ portfolios and make investment decisions.

See a Data Warehouse for Yourself

The best way to understand what a data warehouse is and how financial advisors, wealth managers and family offices use them is to see one in action.

Our team is ready to show you how TAMP1’s data warehouse – and the platform’s  integration with more than 1,600 custodians, banks, and systems – makes it possible to view clients’ investment details in a single place, as well as automate the analytics and reporting, which will help get your team out of legacy software systems and spreadsheets. 

Book a demo for your firm now, or download our free ebook, “Why Move from Client Server Software to TAMP,” to learn more about this cloud-based, highly secure system.