Introduction

This technical overview presents a comprehensive discussion on the system integration architecture for SAP Financial Product Subledger (FPSL) in a single integrated environment, specifically focusing on the S/4 HANA platform. The paper provides an in-depth exploration of the architectural direction, emphasizing the need to handle Extract, Transform, Load (ETL) outside of the S/4 HANA platform to minimize customizations and reduce the overall Total Cost of Ownership (TCO).

Executive Summary

The paper explores the complexities of integrating with the SAP subledger solution, recommending a hybrid approach where part of the integration is handled in S/4 HANA, while generic functions are performed through external integration tools. It delves into the critical design decision of where validations and error handling should occur, and the implications of this choice on the integration build, toolset, and overall design.

The paper further discusses the concept of a common financial language, emphasizing the importance of data validation before ingestion into the subledger. It also explores the challenges of handling complex transformations outside of the SAP S/4 HANA platform and the need for external ETL tools to transform data to FPSL specific objects.

The paper also addresses the decision between packaged solutions and custom builds, highlighting factors such as integration with external data sources, validation and substitution capabilities, error handling, and tool selection. It also discusses the need for a control framework to manage the data load process and a reconciliation framework for financial system integration.

In conclusion, this white paper provides a detailed guide on the system integration architecture for FS-CD, FPSL, and G/L, offering valuable insights and recommendations for professionals navigating this complex technical landscape.

Outside or on top of S/4 HANA platform

The below image outlines msg global inc. recommended system integration architecture for FS-CD, FPSL and G/L in a single integrated environment.

General Architectural direction is to handle ETL outside of S/4 HANA platform as much as possible to minimize customizations required within SAP, to minimize the amount of work required when upgrading to future releases or possible future strategies, such as public cloud offerings, where the number of customizations possible may be limited and to minimize the overall TCO of S/4 HANA platform.

In practice it is often cost prohibitive to handle all integration work outside the SAP platform, also considering the in-depth knowledge required from an experienced SI, such as msg global solutions, to effectively integrate with the SAP subledger solution. We recommend a hybrid approach, where part of the integration is handled in S/4 HANA, while generic functions can be performed in a more cost-effective manner through external integration tools.

Validations and Error Handling

Probably the most significant design decision is where validations and error handling should occur, since it will add significant effort to the integration build, as well as influence the toolset and the overall integration design. While validations are easily added the challenges becomes more how to deal with the bad data.

The 3 most common options for SAP when it comes to Validations and Error Handling

• No Validations: The data quality is ensured in upstream systems.
• Hard Error: The ability to partially reject or rejecting entire data package (batch).
• Data Correction: The ability to manually correct and reprocess data.

It is our view that the external integration tool should be responsible for Validation and Error Handling, since it requires significant custom build effort that should be avoided if possible. This may be a scary thought at first, but if you really think about it, usually clients that walk down the path of building this in SAP are usually also building the exact same functionality in upstream systems / external integration tool, which means you are just building redundant functionality and increasing your TCO.

However, at the same time there are certain validations that may be better validated in the subledger. If for your integration scenario data is received from multiple sources, which must tie together in the subledger, then at least an “ability to fully/partially reject data” approach is recommend. For example, if your data source for master and transactional data is different, or if you receive actuals from admin systems and estimates from actuarial systems, we recommend these validations to take place in the subledger integration.

The subledger is the only system of truth where this data comes together and being able to validate this in an external tool, would mean that you first must integrate this data back into your external tool and that the team must have extensive knowledge of the subledgers internal data model and processes, which during an implementation are still under development and may change at moment’s notice.

Any of these integration approaches is viable, it is most important that you consciously decide and that this decision is clear to all parties involved, so they can plan their efforts accordingly.

Common Financial Language

Under the term common financial language, we are describing a client’s set of common attributes that are used for financial accounting and reporting and their relationship with each other. Attributes such as business direction (direct, assumed, ceded), line of business, profit center, etc.

Validation of the data before ingestion into the subledger is of utmost importance since failure to properly validate the data would prohibit the data to be loaded into the subledger in the first place, partial data loads or worst-case scenario the data would be ingested into the subledger but would fail when sending to the general ledger. Since the subledger is closely integrated with the general ledger this would then require highly manual and slow workarounds and must be avoided at all cost.

Neither an external tool nor SAP Financial Product Subledger can directly leverage G/L substitutions and validations, thus all necessary validations must be rebuilt. It is also important to note that a lot of these validations would probably have to be redesigned from the ground up, since G/L account determination is performed inside the subledger, while the validations must be performed prior to ingesting the data into the subledger. Due to this added complexity a complete lift of all G/L validation and substitution rules is most likely cost prohibitive and it is critical to evaluate and redefine only the rules that are required in context of the subledger implementation.

Typically, in implementations we follow an 80/20% approach. 80% of validations are performed by external ETL, only if integration of SAP data sources is required and cost prohibitive validations are then implemented on top of SAP S/4 HANA platform during the transformation process, before ingesting data into FPSL. Generally, the rationale is that it is more strategic and more cost effective to handle validations in an external ETL tool, where the investment may also be shared with integrations to other platforms and they are easier to set up. This scale may tip however, if it requires to integrate additional data sources, which may already be available within the S/4 HANA platform and would not require additional integration effort.

Common Source Templates

Under the term common source template (CST), we are describing the layout in which the data will be provided to the subledger. From an integration perspective the key design decision is if these CSTs should be aligned with the source data or with the target data model in the subledger.

Complex transformations should be handled outside of SAP S/4 HANA platform within the external integration tool. At a bare minimum we recommend that external ETL tools are utilized to transform the data to FPSL specific objects, such as contract, coverage, portfolio, business transactions and preliminary subledger document. 

Generally, the guiding principle is that external ETL tools should perform all transformations into the target format and validations and substitutions that can be performed solely based on the source data provided, which do not require in-depth knowledge of the source data or the target data model itself or are based on value sets unknown to SAP (custom code block dimensions). In case the data does not fulfill data validation requirements, the ETL solution must either have automated rules to correct the data, offer the ability to hold and correct incorrect data (error correction) or the ability to reject the data and it’s the source systems responsibility to provide corrected data. 

Subledger specific validations include, but are not limited to:

• Contract/Coverage Versioning: FPSL cannot accept multiple contract versions for the same effective timestamp. Multiple version (same contract, different attributes) must be sequenced by assigning an appropriate timestamp and FPSL will only load the final version for a given data load
• Document Grouping / Splitting: A document in FPSL is comprised by document number within its respective document header attributes such as contract, profit and cost center. Documents are limited to 999 line items per document.
• Balanced Documents: A preliminary subledger document can only be accepted if debits and credits balance to 0 and conditions for document header criteria are met (e.g.: a document may only belong to one posting date, company code, profit center and contract).
• Business Transaction ID: A BT ID must be unique.
• Date: Dates must be valid (e.g.: 2/30/2023 is not a valid date) and certain dates are related to each other (e.g.: expiration date cannot be smaller than effective date)
• Special / Hidden / Invalid Character Removal: ETL tool should protect the application from all special / hidden / invalid characters, which may negatively impact reporting or even program logic within FPSL
• Valid Values: ETL solution should protect FPSL from invalid values, especially for custom / non-SAP specific elements.

Packaged Solution vs. Custom Build

While software development cycles can be greatly shortened with modern development tools, custom build solution often still lack in sophistication compared to packaged solutions and often the overall effort is underestimated or final solutions lack in capabilities that would be available with very little effort when leveraging packed solutions for building integration frameworks

Factors to consider are:

Integration with External Data Sources

In many cases, integration with SAP S/4 HANA platform requires the integration of data from various external sources. ETL tools excel in this aspect, offering connectors and adapters for various data formats and sources. Custom solutions may require additional development to accommodate these external data sources, making ETL tools a preferable choice for quicker and more efficient integration. This is not only limited to integrate source systems that originate the financial information to be transformed, but also reference systems, such as MDG, which are the source for data validations or mapping tables that are required to enrich the financial data with additional financial code block dimensions.

Validations and Substitutions

ETL tools offer robust validation capabilities, enabling the extraction of data from multiple sources and validating it against predefined rules. These tools often provide built-in functions for data cleansing, transformation, and substitution. Custom built solutions require significant development effort to implement similar validation and substitution functionalities.

Error Handling and Error Correction Capabilities

ETL tools typically provide comprehensive error handling mechanisms, allowing for easy identification, logging, and tracking of errors during data integration process. Additionally, they often support error correction capabilities, enabling automated or manual corrections of data issues. Custom solutions require extensive coding efforts to implement similar error handling and correction functionalities.

Tool Selection

As vendor specialized on SAP family of products msg global solutions inc. is most experienced with and recommends SAP’s set of products such as SAP Integration Suite (PAAS), SAP Data Intelligence and SAP Data Services (BODS), however we often find ourselves integrating with non-SAP based external solutions and generally recommend to leverage what is the preferred and well established with the customer to maximize the benefits of leveraging existing and available skills, knowledge and pre-existing rules, validations and substitutions. While FPSL may require data for its interfaces to be provided in very specific formats, especially custom financial code block validations and substitutions are likely to be very similar between different financial system interfaces.

For a list of popular integration services you may also refer to 2023 Gartner’s Magic Quadrant on Integration Platforms As A Service (https://www.gartner.com/reviews/market/integration-platform-as-a-service-worldwide)

Data Transfer (FTP, HTTP, ODBC)

While packed solutions typically include the ability to publish the data in the target application, we often find ourselves in the position having to define the target solution for data transfer as well. This means that the financial information required for FPSL is available in a fully transformed manner (e.g., contract, coverage, portfolio, business transaction, preliminary subledger document, etc.), but still must be transferred from a file to the SAP S/4 HANA Platform.

The recommended approach is to make the data available to SAP Financial Products Subledger (FPSL) in staging tables on the S/4 HANA Platform. While data load from a file is possible with additional development effort it is not recommended, since external applications are perfectly capable of writing to staging tables directly and additionally handling files on the S/4 HANA Platform would further increase the effort regarding data governance, but also possibly require more complex load balancing procedures to handle large data volumes.

To handle data transfer to the S/4 HANA Platform, SAP offers Smart Data Integration (SDI), Smart Data Access (SDA), SAP BW Data Sources/Open Hub Connections and SAP Data Services (BODS) as a data transfer mechanism with which we have extensive experience with many of our clients. BODS runs on additional hardware however, so it is really an application external to SAP S/4 HANA in the context of this document.

It is also important to note that a client’s ability to write directly to the S/4 HANA database maybe limited by your licensing agreement and must be carefully evaluated during the implementation.

Master Data

Subledger systems come with their own proprietary set of master data at which level processes in the subledger are configured. They are key when implementing a subledger solution and ensuring that processes can be executed in an effective manor and usually control the system behavior when processing transactions for a master data object. SAP Financial Products Subledger systems requires master data to be provided through a separate API before loading transactions for the corresponding object. Master data in an insurance subledger usually represent policies, treaties, claims or actuarial cohorts.

Master Data On-the-Fly

There are 2 challenges when trying to create master data from a separate data source:

• The higher the granularity of the master data, the less likely it is that there is a comparable data source within the client’s financial system landscape that can reliably be used as a source for the data.
• Although master data may be closely aligned with a client’s known objects, such as policies and cohorts. Often the subledger requires the master data to be created at a slightly different granularity to best leverage the subledger functionality in alignment with the requirements.

One way to minimize processing issues due to missing master data is to create master data on-the-fly out of transactional data. Depending on the complexity of the solution however, this may also drive additional significant effort.

For example: To calculate IBNR, the subledger requires net premium earned (NPE), paid claims and case reserves, as well as loss ratio. From a subledger perspective all this information is expected to be available at the cohort, however all this data may be coming at different granularities. While NPE may be calculated at policy level and rolled up to a cohort, loss ratios may be provided at a profit center / year level and must be pushed down to all its respective cohorts.

We do recommend the creation of master data on-the-fly, to minimize integration issues and processing delays due to missing master data.

Master Data ID Assignment

The process of master ID assignment describes the key attributes that together form the key for a master data object, such as company + policy number + profit center.

As mentioned in the previous section, the granularity of master data in the subledger is likely to be different from anywhere else in the client’s system landscape to best leverage subledger processing in accordance with the requirements. This also means that the granularity may change over time, especially during an initial implementation, to match a change in requirements.

In our view it is best to handle master data ID assignment in the subledger, to be able to flexibly control the granularity of subledger master data objects. If the subledger provides granular data back to an external system (e.g.: actuarial system for calculation), this interface should be built based on external attributes and not the subledger internal ID.

Master Data Delta Detection

If master data is created on the fly and master data ID assignment is handled within the subledger this also means most likely the subledger will have to be able to handle multiple redundant data loads for the same subledger object. The process of delta detection describes the ability to identify multiple duplicate and redundant data loads and discard unnecessary loads accordingly.

At a minimum one must consider the grouping and sorting of master data objects within a given data load (batch), however depending on the processing requirements it may even be necessary to avoid unnecessary loads across data loads, since it may have performance implications on the subledger processes.

Integration Layers

The integration layers describe the steps the data must go through in S/4 HANA before it can be ingested by the native subledger API.

• One Layer: If all transformation, substitutions, and validations occur in the external integration tool, then a one layered staging approach may be chosen. The data is delivered in exactly the format and granularity required by the system and the staging layer is solely leveraged to support the load balancing when loading transaction in the system.
• Three Layers: If any form of transformation, validation or enrichment is performed in the subledger a three-layered approach should be chosen to support reconciliation, but also optimal performance and avoid complicated modifications.

Acquisition Layer

The acquisition layer is where the data from external systems is delivered. For reconciliation and traceability, it is important that this data is retained as is and not modified in any way. We generally recommend that this data is delivered in a staging table, since the subledger application can directly work with tables and it reduces administrational overhead for managing staging data. If the data is delivered via files, a custom process must be built, to load the data into an acquisition table, but also additional processes are required to manage the lifecycle of the file (e.g.: moving from inbound to archive folder and final deletion of the files, after reaching a specified retention period).

Transformation Layer

Key for the transformation layer is that processing of the data must occur in parallel to maximize the throughput of the data ingestion process. In the transformation the system will perform validations, substitutions and the granularity of the data maybe changes, for example if the data is stored at a more granular level in the subledger. The transformation layer should handle all processing intensive operations.

As discussed in section Validation & Error Handling, Common Financial Language and Common Source Templates, the goal is to minimize the amount of transformations required in the integration layer of the subledger, however also in our experience this can usually not be completely avoided. The key is that we avoid building redundant validations and substitutions and that there is a clear rationale for logic to be handled in the subledger integration compared to an external integration tool, to minimize the overall implementation cost.

Data Load Layer

Finally, the data must be provided to the subledger API through a data load layer. In the data load layer, all data must be validated and conform to all subledger specifications. Only operations which cannot be performed in parallel processing should be performed during the transfer from the transformation to the data load layer.

For example, when assigning document numbers in the subledger you may have to consider the entire batch and properly group the transactions by document header attributes, such as company, policy number and profit center.

The data load layer also usually integrates with the control framework to only make fully enriched and validated packages available to the subledger application.

If possible, the data load layer should be expressed through a HANA view on top of the transformation layer, which can reduce complexity and efforts for the integration build. It is important to note however, that HANA views require a HANA enterprise license and may not always be available to the SI.

Table Types & Views

When building the integration, one must choose from various available technologies in SAP, such as HANA Tables, Dictionary Tables and BW ADSO’s.

• HANA Tables: The key issue with native HANA tables, is that these tables are not available through most SAP standard applications, such as SE16 (Data Browser). This is a major disadvantage. Most resources on a project will not be able to access these tables themselves and reconciling and validating between staging tables and the subledger application will take up a significant amount of time during integration testing and it is critical that project team has access to this information.
• Dictionary Tables: Dictionary tables are defined in the SAP Data Dictionary and most people a very familiar with these tables. They are easily created and maintained, and standard tools are available to access these tables.
• BW ADSO’s: Advanced Data Storage Object are designed in BW and are specifically designed for supporting data ingestion process. They come with basic, standard functionality to support data load, data deletion, parallel processing of transformation and error handling. Given the additional functionality, there is also some more administrational effort to configure ADSOs and standard API’s must be used to read from and write to ADSOs.
• HANA Views: HANA views provide a powerful toolset to expose the data to the subledger application layer without the need to physically replicate the data. This makes HANA views an ideal tool to replace the data load layer, however as mentioned in the previous section the ability to use HANA views may depend on your licensing agreement.

Generally, we recommend the use of BW ADSOs due to the significant amount of out of the box functionality available, to support the data integration process.

Control Framework

Control Framework refers to a custom data model that operates on a package (batch) level. Once a package is delivered the external integration tool makes the package available to the subledger by placing a package control record into the framework.

The subledger integration uses this control record to manage the data load and updates the status to indicate the different stages of the integration process, as well as successful / unsuccessful load.

Establishing a control framework is highly recommended to effectively manage the data load process.

Reconciliation Framework

As part of any integration of financial systems, a technical reconciliation procedure must be established that enables the reconciliation between sending and receiving system. This includes reconciliation from source to ETL and from ETL to source. Since there are typically complex transformations involved between source and FPSL, a direct reconciliation between source and FPSL is usually not possible.

We recommend this reconciliation be handled by the ETL or Data Transfer solution, which means for the data transfer between ETL and FPSL, the solution would receive control totals (record count, total debit, total credit) from ETL, write the data to staging tables, establish the control totals from staging after the data has been written, and make a reconciliation report available.

As part of the reconciliation framework, we recommend that either the source system or ETL also makes reconciliation IDs available to better support reconciliation.

• Batch ID: An ID that identifies a single data load/package from ETL / source. This could be 10s of millions of transactions. The batch ID ties all records received for a single data load together across all interfaces (e.g., contracts, coverage, portfolio, business transactions, preliminary subledger document, etc.). For transformation process, the batch ID allows the system to reject an entire batch, if a single record fails validation. Depending on the interface, the Batch ID may be traced all the way to the general ledger.
• Transaction / Business Case ID: A group of transactions that belongs together (e.g. external document number). Depending on the interface, a Transaction ID may be traced all the way to the FPSL subledger document.
• Record ID: A single record identifier within a given batch. Depending on the interface Transaction ID may be traced all the way to the FPSL subledger document.

While we do recommend that the end-to-end reconciliation is handled by the ETL/data transfer application, we also recommend that a batch control table is established and populated by the ETL/data transfer tool once a batch has been successfully transferred and is ready for consumption within FPSL. This batch control table can then be used to automate the handover and trigger transformation and data load into FPSL. During transformation additional status information may then be added to the control table to handle acceptance or rejection in case of data validation issues and prohibit the load of partial and incomplete data into FPSL.

Data Load Layer (DLL, LSMW, etc.)

Once the data is available and ready to be consumed (validated) the FPSL Data Load Layer (DLL) is used to load the data from staging tables into FPSL via pre-defined interfaces. The DLL framework is extremely powerful when it comes to managing even the largest data loads and loading the data quickly into the system. However, while FPSL handles some validations out-of-the-box for FPSL internally required characteristics, and some capabilities exist to enhance validations within DLL depending on the interface, we generally do not find the validations sufficient or effective to protect the application from bad data, which would lead to reporting inconsistencies or downstream (G/L) issues. It is of utmost importance that at the time of DLL, the data is clean and correct. As previously mentioned, once the data is accepted into FPSL correction capabilities are limited or non-existent, depending on the interface and data can only be pushed downstream or excluded from further processing through manual data manipulation.