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
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
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
|Graz CEO Jonas
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
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
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
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).