Leading a data warehouse project
As a large Dutch pension fund service provider, PGGM had a lot of data flowing into and around their organization, but with no clear strategy (or solution) to manage all that data.
With multiple versions of the truth flowing into the reporting process to create client reports, it was a very sensitive and time consuming task to create all the reports. At the same time, ambiguous data definitions resulted in small inconsistencies in positions and risk and performance reports which the front office relies on for their investment decisions.
Additionally, while permitted timelines for delivery of regulatory reporting are getting shorter, PGGM struggled with their time-to-market of reports, caused in part by an unscalable reporting process that required additional resources in the reporting department with every new client. Finally, PGGM’s reporting process was very labor-intensive and therefore expensive.
In order to advance, PGGM realized their need to have more control over their data.
Five key data challenges that were holding PGGM back
- Multiple versions of the truth flowing into the reporting process
- Ambiguous data definitions resulting in small inconsistencies
- An overly long time-to-market for regulatory reporting
- Reporting process was not scalable
- A very labor-intensive (and therefore expensive) reporting process
Partnering up with SimCorp
Traditionally, PGGM has been reluctant to be first movers with new technology. In this case however, the company believed in the roadmap put forward by SimCorp and wanted to be a part of it from the beginning. PGGM became a pilot client for SimCorp’s Data Warehouse.
Sander Tegelaar, Manager of Project Management and Implementation was a member of the project board and had the executive role in the last phase of the project. As he puts it, “This gave us a seat at the table and the ability to influence many aspects of SimCorp’s Data Warehouse. For example, we pushed SimCorp to include the cost area. While there can be a few stop-and-go moments for any pilot client, we felt that the process went really well and that SimCorp was committed to the project and our input on it.”
While PGGM had tried in the past to build in-house data solutions, they quickly realized that it caused more issues than it solved. It made a lot more sense to go with a vendor using a standard data model, with expertise on how best to structure data.
Reflecting upon the importance of standardization, Sander Tegelaar says that the “implementation effort to populate this standard data model was limited due to the standard interface from our core SimCorp Dimension solution.” Additionally, the technical deliverables where limited to PGGM-specific structures (freecodes) and external data sources. While the learning curve of the data warehouse tooling was quite steep “we didn’t need to bring in additional permanent staffing in order to operate the data warehouse,” explains Sander Tegelaar.
Sander Tegelaar from PGGM Investments
Having the business organization own the data warehouse
PGGM considered several perspectives when looking at ownership of their data warehouse:
- The business users should be the designated owner of all the populated fields in the data warehouse
- The business users needed to make a switch from requesting fixed (Crystal) reports to a situation where they could either create reports and dashboards of their own or request data model extensions
- The IT department takes responsibility for the overall data model to avoid redundancy and to validate the expansion request for the data model
Data governance is key
One of Sander’s key takeaways from the project was that their decision for a strong data governance structure was really important and is vital for any successful data warehouse project. Sander Tegelaar explains that from a technical perspective, the data warehouse was very feasible. His greater challenge was getting business awareness and buy-in on the fact that data is important. This is a common challenge for data projects and shouldn’t be underestimated.
Sander Tegelaar took the battle however, and convinced the entire company of the importance of embedding a structure where the company-wide data definition, controls and ownership was clearly defined. “I’m proud we made that happen, because otherwise, you may have had a really nice box, but filled with bad data, and that doesn’t help anyone,” explains Sander.
PGGM introduced a data governance board, including c-level management. The board looks at data files with a solution analyzed by a preparation team consisting of business representatives from the full investment chain. In a data dictionary file the business owner of each data element is defined. The prep team also defines the source of the data, how is it controlled, how it is updated and defined. Once the data dictionary file has been approved by the Governance Board there is no discussion any more on how teams should interpret that data element. Sander sums up, “It also stops us from having a lot of parallel data elements in the system. So that was a big takeaway.”
One of the strengths of SimCorp’s Data Warehouse has been the off-the-shelf interface, transporting all transactions, static data and positions (based on portfolio calculation results) to the data warehouse.
Data warehouse as a single source of truth
Previously at PGGM, the unscalable reporting process had no audit trail of the data reported. It was very hard to reverse engineer where it came from. Here, a data warehouse really helps because the data can be historized within the data warehouse. PGGM keeps snapshots of each reporting period enabling them to always reproduce previously reported figures.
Time-to-market was another key driver of the project as there had previously been no easy way for end users to access reports quickly and easily. Sander Tegelaar explains, “By putting the data warehouse in place, and giving our end users Business Intelligence (BI) tooling, they are able to get basic reports themselves. Also, it is nice to know that they are using the single data set, and not just an excel sheet which is flowing around in the company, which helps in two ways. Firstly, the quality of the process for the business teams involved, secondly, there is less stress on PGGM’s report developers.”
Sander explains that one of the main reasons for selecting SimCorp’s Data Warehouse was its ability to “capture all backdated changes in data and keep your transaction, static data and positions datasets in sync.” He noted that, “This is a huge challenge, not even taking into account the effort to maintain these interfaces after each upgrade.”
Some takeaway metricsOne of PGGM’s main KPI’s was the ‘data coverage’ for their client, fund and mandate reports. According to Sander, before starting the project “we could only source around 40% of the report data directly from SimCorp Dimension. After finalizing our data warehouse project and once we have integrated performance attribution data, which is currently in progress, we are able to get 80% of all the report blocks directly out of the SimCorp’s Data Warehouse for all our public (equity / fixed income) funds and mandates.”
Some of our peers have very shiny iPad apps connected to their data warehouse, but I’m not sure what is underneath that, and how the engine room looks, but in terms of our engine room I believe we are well ahead of our peers
What the future holds
Up until now, PGGM has focused its efforts on making sure their engine room (the data warehouse) is in the best possible state. “Some of our peers have very shiny iPad apps connected to their data warehouse, but I’m not sure what is underneath that, and how the engine room looks, but in terms of our engine room I believe we are well ahead of our peers,” says Sander.
Having said that, now that they have the fundamentals in place, PGGM is planning to maximize their use of the Datawarehouse by implementing a Self Service BI layer on top of it. These are important elements, but what is clear from PGGM’s story is that firms should only focus on them once their data is truly under control.
PGGM is a pension fund service provider and manages the pensions for different pension funds, the affiliated employers and their employees. Currently, PGGM manages pension assets worth in excess of EUR 200.2 billion (December 2016).