PensionDCisions (PDC) is a young pension-analysis firm
for whichbusiness intelligenceis
crucial.
The firm calculates and benchmarks performance for individuals
and corporations that use
defined-contribution pensions - a type of pension that invests
contributions in individually allocated accounts.
As a result, each individual contributor has access to their
investment information, and this means the IT system must be robust
enough to deal with large quantities of data and queries.
The firm itself does not offer financial advice, but rather
takes data from pension providers, manipulates it, and presents it
in a way that is useful to individuals, company managers, financial
advisers and pension-industry players.
Richard Burrill, PDC's technical director, says, "We use
business intelligence to provide things like a single meaningful
performance number, a view on risk, a simple view on asset
allocation, or a savings rate."
Since it was started in 2005, the founders of PDC have been
determined to build their own database and business intelligence
infrastructure using established products. The firm partnered with
Microsoft collaborator
IMGroup to design and build a system based on Microsoft tools
and technologies.
The system uses several Microsoft server products, most notably
Microsoft SQL Server 2005, SQL Server 2005 Analysis Services
and SQL Server 2005 Reporting Services.
The pension data itself comes from a number of administrators of
different pension schemes, and this data originates from their own
relational database systems, whether they are using
Oracle, IBM, Microsoft or
Sybase.
PDC requires those administrators to provide data that adheres
to data standards and definitions set by PDC. Data extraction is
therefore left to its partners. "We go to the administrator and ask
for the information on a fairly basic level on a regular basis:
generally quarterly. During the set-up period, they code their
applications and prepare to provide the data," says Burrill.
The pensions data itself changes over time as assets grow or
decline, the value of the holdings alters, and members and
employers contribute additional funds or switch money from one
asset to another.
The extraction, translation and loading process runs under
Microsoft SQL Server 2005 Integration Services, a platform for
building and running high-performance data-integration systems. The
platform allows PDC to ensure the data is of a very high quality
after it has been submitted by the partner organisations.
"It is important to get that right so you can have data that can
be compared, and that there can be clear interpretation of it. We
are producing key measures at the end of it, so it is important
that the data we use and aggregate is top quality and has
integrity. Otherwise there is a danger that you will pollute the
numbers you produce," says Burrill.
As part of the ETL process, the firm runs a series of clearly
defined checks on the data, which examine its format and adherence
to business rules. This operation runs under SQL Server 2005
Integration Services.
Integration Services is able to flag up any errors and feed
these back to the pension partners, a proceedure that Burrill
describes as "an expected part of the process" and one that the
partners find beneficial.
The data itself sits on PDC's Microsoft SQL Server 2005 database
system, which can deal with terabytes of data. It can also create
multiple instances of certain parts of the data, scaling up so that
PDC can process more data simultaneously when required.
The core data sits in a number of tables in the SQL Server 2005
database, and is aggregated in various ways to produce the metrics
that PDC offers its end-users.
Burrill says that an SQL Server management layer orchestrates
the process from end to end, pulling together multiple pension
schemes, multiple feeds - including an audit trail that records
what data is received and when - as well as what errors were
detected and how they were dealt with.
In a proven business intelligence fashion, PDC takes the data
and builds "cubes", using SQL Server 2005 Analysis Services. This
enables it to manipulate the data and present it with reports and
"dashboards".
These reports are created using SQL Server 2005 Reporting
Services and presented using an application called Dundas Charts.
The tools can produce simple "ladders", which display information
such as risk and return or asset allocation.
"It is designed to present information in a way that is simple,
though it is not simple to produce technically. There are certain
challenges with presentation - parameterisation being the main one
- you do not want to have to hard-code everything," says
Burrill.
The main way in which PDC measures the returns on its business
intelligence system is through the engagement of its end-users.
Burrill says, "We have looked at activity pre and post delivery of
reports, to measure user engagement, and though it is sometimes
difficult to measure, we have seen a very clear improvement in
activity."
He says that in the future the firm plans to integrate the
Microsoft Business Intelligence suite into Microsoft Office 2003/7,
and particularly Sharepoint, as a way to deliver information more
seamlessly to the end-user's desktop, and offer more
interactivity.