Technology: Time to Reconsider the Data Warehouse

Traditional processes do not address the new complexities of risk management

Tuesday, June 25, 2013 , By Jonas Olsson

printPrint   |  Order  |  Email this Story  | 

Since the financial crises of 2008, the need for better risk control for financial institutions has become acute. However, the data required for a truly complete picture of financial risk is typically siloed in both internal and external systems, horizontally across business units and vertically by function. The difficulty of accessing the underlying data makes it very challenging to get a real-time, 360-degree view of risk exposure. Getting access to a complete set of data is therefore of great importance for any organization that wants to take risk management seriously.

This is a particular problem for financial services firms because they have so many different systems and such complex data. They have disparate internal and external OMS/PMS portfolio data, client data, accounting data, market data in countless formats (and with incredible volumes and message rates), organizational data, reference data and static data. To be able to access all of this data in such a way that analysis may be easily performed is an intense, difficult and costly task. Many financial companies in this situation have turned to data warehouses to solve this problem.

However, the traditional approach to implementing a data warehouse, which may have been the best available solution for financial firms, limits a company's ability to perform true 360-degree risk management, especially when dealing with complex and constantly changing data. The reason for this is the traditional architectural design of a data warehouse, known as ETL.

062513_DataWarehouse_JonasOlsson
Graz CEO Jonas Olsson.

Drawbacks in Design

ETL refers to the step-by-step process by which organizations take data from their operational system and put it into a data warehouse. They extract data from various sources; transform that data into something usable and consistent; and then loadthe data into the warehouse.

For businesses that don't have very complex data to manage or whose business environment is not very dynamic, the ETL approach to data warehousing can work very well. For a more dynamic and complex industry, such as financial services, I would argue that the traditional ETL-based approach has a number of critical flaws that make it prohibitively difficult to get all business data in one place, in turn making it impossible to measure risk appropriately.

One of the major limitations is that it implicitly assumes an organization has a perfect understanding of what the users want to do with the data and how new or changing data elements will relate to the existing data, not just now, but also in the future. Currently there are no technical solutions that offer prognostication as a feature. And I'm pretty sure there never will be.

Imagine you are building a house with the construction-planning equivalent of ETL. You have a basic idea of what you want -- something contemporary, some basic design and size concepts -- but no plan. Based on the vague idea of what you want, you go out and buy all of the materials you need: lumber, flooring, drywall, electric and plumbing. Once you have spent a great deal of money buying and storing those materials, you then take the time to figure out what to do with them by creating a blueprint. This approach is patently absurd, but very much what companies are forced to do with any data warehouse project.

No Turning Back

The other problem with ETL is that it is not reversible. Let's say a company wants to summarize certain data at the transformation phase. This prevents it from later going back and creating reports and analysis for critical functions like risk management, based on that data prior to summarization. In effect, this means taking the great potential of a data warehouse and greatly eliminating it.

The third, and perhaps most significant, challenge with ETL is that it historically sucks huge amounts of resources available for a given project, often as much as 75%. That burden leaves precious little time and resources available for an effective data model -- which is the effective fundamental building block of coping with data complexity and a changing business environment,

For all of these reasons, ETL is forcing financial services organizations to spend too much and to be satisfied with too little. A new approach is required.

Extract, Load, Transform

What if we reordered ETL, making it insteadextract, load and transform? It may seem like a minor change, but, with ELT, one's ability to change as business changes is much, much greater.

Suppose, working under the traditional ETL approach, a portfolio manager decides to start using derivatives to limit transaction costs, enhance return or protect his holdings. An ETL-based data warehouse, by definition constructed based on earlier assumptions of how portfolios were managed, will have difficulty integrating the new data and therefore conducting risk exposure analysis on the derivatives. It will likely require a long-term project over months or years for the data warehouse to effectively capture the derivatives data. The result? A long period of time with poor risk management.

In short, ETL means risk is always playing catch-up, whereas ELT allows for continuous change and adaptation without significant time and expense.

ELT removes the requirement that an organization predict exactly how it will use information into the future. It extracts the data from various points, loads it into the data warehouse -- in its true form -- and then makes that data available for any type of transformation required into the future.

ETL, first introduced for a centralized, batch-processing financial industry data world in the 1990s, is the wrong solution for today's highly distributed environments with vast amounts of complex data. It is even worse for risk management, as complicated data and dynamic business conditions can turn a small, unseen risk exposure into a business-crushing calamity.

Sometimes a paradigm shift can come with a revolutionary idea, but often it requires just a reorganization. By changing focus from ETL to ELT, financial institutions will find that they are better able to analyse and manage risk. After all, if you don't have the underlying data available on an ongoing basis, how can you expect to be able to monitor your risk?

Jonas Olsson is founder and CEO of Graz, a Sweden-based provider of data warehouse and business intelligence software for the financial services industry (www.graz.se).

 

Risk Management e-Journal
cover
The Risk Management e-Journal publishes paper abstracts on the topics that matter most to risk professionals. See what your risk manager colleagues are reading about today.

 

 

 

Get Free Updates on the Dodd-Frank Act
DoddFrank
Register for Morrison & Foerster's FrankNDodd service to receive Daily News Alerts on the Dodd-Frank Act, gain access to regulatory highlights and commentary, and use the exclusive FrankNDodd Tracker tool.

 

Banner Picture