P&L for a large financial institution, from data to report

Problem

The finance department of a large financial institution needed to produce its P&L from the information of four external managers, which arrived as Excel and CSV files and in a few databases. That data had to be centralised, standardised and transformed with complex business logic before it could be used, and after each monthly close it had to be possible to correct errors without throwing anything off.

Approach

• Centralisation in a data warehouse: connectors to the shared locations where the Excel and CSV files arrived, and to a few databases.
• Standardising, cleansing and transforming hundreds of thousands of records with SAP Data Services, with complex calculations derived from the business logic.
• Modelling in SAP Information Design Tool in two layers: a data layer (physical tables, aliases, views, joins and calculated columns) and a business layer (dimensions, measures, hierarchies, filters and lists of values in business language). Mainly star schemas, with one fact table and dozens of dimensions around it.
• Web Intelligence reports: control reports for users, more formal ones for management, reports with alerts to anticipate corrections before the monthly closes, and the main P&L report with the institution’s own logic and journal entries.
• Manual adjustments after the close: an Excel process with macros, followed by processing and calculation in SAP Data Services, which made it possible to correct an already closed month with the guarantee that the change would not alter any other P&L calculation.

Result (qualitative)

Users had a reliable P&L, with its controls and alerts, and a manual-adjustment process that saved them a lot of time and effort without putting the close at risk. It was almost two years on the project, learning the business from scratch and earning the trust of the client and its users, who valued that very complex processes looked simple.

Skills demonstrated

ETL with SAP Data Services, dimensional modelling (star schema) in SAP IDT, Web Intelligence reporting, financial and closing logic, and a long-running relationship with the client and its users.