From data silo to integrated database:

We were asked by our readership exceptionally often about the article "Liquidity planning in heterogeneous system landscapes" (Treasury Newsletter No. 140), in which we highlighted the challenges that treasury departments face in liquidity planning: rising interest rates, volatile markets and often a historically grown, heterogeneous IT landscape that makes precise control difficult. The use of modern BI, ETL and advanced analytics platforms to integrate and process data from different sources was presented as a flexible and pragmatic solution. Our readers are interested in what this crucial first step looks like in practice.

As a result, this article delves deeper and uses specific examples to show how the data connection of different sources works and how well thought-out data modelling creates the foundation for reliable and analytical liquidity planning - even without a fully integrated system solution.

The goal: a central, structured and trustworthy database as a starting point for all further analyses and planning.

The first step in overcoming data silos is the technical connection of the relevant source systems to the central data analysis and automation platform (hereinafter referred to as the "platform"). These platforms typically offer a variety of connectors and methods for accessing different data formats and systems. Flexibility is key here.

• ERP systems (e.g. SAP S/4HANA, SAP ECC, Oracle Financials): The centrepiece of much financial data.
  • Concrete example (SAP): Instead of complex in-house developments, many platforms (such as Alteryx, Power BI, Tableau Prep) use certified connectors. One common method is access via ODP (Operational Data Provisioning), which allows standardised extractors (DataSources) for financial accounting (e.g. FI-GL, FI-AP, FI-AR) or even specific treasury modules (SAP TRM) to be tapped. Alternatively, remote function calls (RFCs) can be used to call so-called Business Application Programming Interfaces (BAPIs). Direct database queries to tables are technically possible, but often not recommended for performance and governance reasons. A pragmatic, albeit less elegant, way remains the automated export of reports or tables in formats such as CSV or XLSX and their subsequent import into the platform.
  • Important: Clarify access rights and use dedicated technical users with the minimum necessary authorisations.
• Bank data (account information): Indispensable for the ACTUAL cash flow analysis.
  • Concrete example: Electronic account statements in standard formats such as MT940 or camt.053 are the standard. Many platforms offer modules or allow scripts (e.g. Python) to automatically retrieve these files from an SFTP server of the bank or a service provider (e.g. TIS, Serrala). The platform then processes the XML or MT940 structure and extracts relevant fields such as value date, booking date, amount, currency, purpose and counterparty data (if available). Direct bank APIs (as part of PSD2/XS2A or specific corporate APIs) are also becoming increasingly important, as they enable prompt, often transaction-based retrieval and can be accessed directly via the platform.
• Treasury management systems (TMS, e.g. Coupa Treasury, Kyriba, ION Treasury): Source for financial transactions, bank account management, guarantees, etc.
  • Specific example: Many modern TMSs offer REST APIs that can be used to retrieve data such as due FX transactions, money market investments or loan disbursements/repayments. Alternatively, direct database access (SQL) to the TMS database is often possible or the classic export of data to CSV files, which are then processed by the platform. The aim is, for example, to integrate future cash flows from financial transactions that have already been concluded into the planning.
• Excel files and other flat files: Often used for manual planning data or data from niche systems.
  • Concrete example: Controlling delivers a monthly Excel file with the sales planning for each company on a defined network drive. The platform is configured to monitor this folder, recognise new files, validate their structure (e.g. column names, data formats) and import the planning data. The same applies to HR data (planned salary payments) or specific project data.
  • Challenge: Ensuring a consistent structure and naming of files and worksheets.

The strength of the platforms lies in combining these different connection methods and automating and monitoring the data loading process. If a source fails or a new one is added, this can often be adapted with manageable effort.

The raw data from the various sources can rarely be used directly for coherent liquidity planning. They are available in different structures, levels of detail and qualities. Data modelling creates a uniform, logical structure that serves as a "single source of truth" for all analyses and reports. A proven approach for this is the so-called star scheme.

• Concept: At the centre is one or more fact tables that contain the actual measured variables (here: cash flows). Dimension tables that provide descriptive attributes (Who, What, When, Where?) are grouped around these fact tables.
• Fact table(s): The centrepiece
  Concrete example: You need at least one fact table for the ACTUAL cash flows (from bank data/ERP) and often a separate one for PLAN cash flows (from planning tools, Excel, TMS due dates).
  • Fact table ActualCashflows
    The key figure fields for the amounts in local and group-wide currency are important in this table. In addition, a link to the dimension tables is established. Essentially to the time, account, company and currency dimensions. Not to be forgotten is the link to the dimension that contains the plan item information.
  • PlannedCashflows fact table
    This table has a similar structure to the ActualCashflows table, but it contains the respective planning amount as a key figure field. The planning scenario can be added as additional dimension information.
• Dimension tables: The context
  • Time dimension (DimTime): Essential for any time-based analysis.
    The time dimension should contain all trivial fields. Company-specific calendar information can also be stored here. For example, which date is used as the cut-off date for the month (last calendar day vs. last working day) or entity-specific public holidays. This table is generated once and covers a relevant period (e.g. 2010-2030).
  • Planning position dimension (DimPlanningPosition): The central structuring from a treasury perspective. The "clearly defined planning item" mentioned in the first article is mapped here.
    In addition to a primary key and the name of the planning items (and any other meta information), the hierarchy structure of the planning items is also mapped here.
    This dimension must be carefully defined with Treasury and forms the basis for the subsequent mapping of the raw data in the ETL process.
  • Company dimension (DimCompanyCode): Master data of your own company units.
  • Bank account dimension (DimBankAccount): Master data of your own bank accounts.
  • Currency dimension (DimCurrency): List of relevant currencies.
  • Business partner dimension (optional) (DimCounterparty): If debtor/creditor information can be extracted from the sources.
  • Scenario analysis dimension (optional) (DimScenario): If plan data is created for different scenarios, the required information is stored here. For example: Name of the scenario and parameter data.

This structured model is the target of the ETL process (Extract, Transform, Load), which extracts the raw data from the connected sources, cleans it up, transforms it (e.g. performs mappings to plan items, converts currencies) and finally loads it into this target model. The model itself then provides a clean, consistent basis for dashboards, reports and further analyses such as plan/actual comparisons or forecasts.

The systematic connection of all relevant data sources and the creation of a well thought-out, centralised data model are not trivial tasks, but they are the decisive cornerstone for successful and efficient liquidity planning, especially in complex system landscapes. They do not replace the need for clean data capture at the source, but they do enable treasury to gain control over the data and create a standardised view. BI/ETL platforms offer the necessary technical flexibility for this. To implement the data model, it makes sense to utilise platforms that already exist in the company. These could be databases in SAP BW, Oracle or DB2/IBM, for example. As the data model described above is not yet very complex, it is well suited as a pilot for the introduction of a cloud-based database solution, which - depending on the manufacturer - then comes with more extensive analysis tools.

The next, value-adding steps can then be based on this solid database: the implementation of the transformation and cleansing logic, the actual planning or forecast and the creation of meaningful analyses and reports. These aspects provide material for further consideration and show how operational and strategic added value can be generated for the treasury department from the database created. Ultimately, this approach enables more robust financial management and strengthens the company's resilience in dynamic times.

Source: KPMG Corporate Treasury News, Issue 154, May 2025

Authors:

Börries Többens, Partner, Finance and Treasury Management, Corporate Treasury Advisory, KPMG AG

Jakob Fisahn, Manager, Finance and Treasury Management, Corporate Treasury Advisory, KPMG AG