PensionDCisions (PDC) is a young pension-analysis firm for which business intelligence is 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.