4       Regulatory Reporting Solution Data Flow

This chapter provides an understanding of the data flow. It explains what happens within data flow and how various processing aspects are integrated with the overall data flow.

Topics:

·        Data Preparation 

·        Overview of OFS REG REP US Treasury User Interface

·        Mapping of Results to Line Items in Reporting

·        AgileREPORTER: Submission

Data Preparation

This section explains the input data preparation from OFSAA.

Topics:

·        Assumptions for Data Preparation 

·        US Treasury Run Chart

·        RUser Specific Reclassification Rules or Mapper Maintenance

·        Configuring Setup Tables for Standard Set of Values

·        Run or Execution Expectations

·        Consolidation

·        Data Flow from Sources Systems to Staging Area

·        Data Flow from Staging to Results Area

·        Data flow from Staging to Processing Area

·        Data Flow from Processing to Results Area

·        Guidelines for Data Loading to Result Area Tables in Data Foundation for Regulatory Reporting Implementations

·        FSDF Entity Information

·        Fact Tables or Entities

Assumptions for Data Preparation

The following are the assumptions for data preparation:

1.     REG REP is a reporting solution, which uses data from underlying fact tables directly for reporting. You are expected to prepare the load for the required data in the reporting area accordingly. Although this has a thin processing layer to reclassify regulatory dimensions and bands, all the processing measures are expected to be from respective applications and provide as required.

2.     It is integrated with the results area of the respective processing application, and any change in the underlying processing can disturb the REG REP data sourcing.

3.     For usage of consolidation dimension (which has values like Actual, Budget, Forecast, and so on), all historical data is expected to be tagged as actual to report vintage data, as per report requirements. For projection data, for a given run and Projection Period (quarter/year), only one set of data is expected to be stored.

4.     All processing reporting requirements requiring cash flows, integration package expects bucketed cash flow as an input (meaning a time bucket for cash flow and cash flow amount is expected as input).

US Treasury RUN CHART

Oracle Financial Services Regulatory Reporting for US Treasury – Lombard Risk Integration Pack provides the US Treasury RUN Chart listing the tasks required for the population of data for US Treasury Reports. This covers the following tasks:

·        Set up table population

·        Stage Dimension Load

·        Seeded Dimension Data Population

Common data Population

·        Common Tasks like Exchange Rate Population

·        US Treasury Specific Data Population and Transformation

·        Derived Entity Refresh

Download the US Treasury 8.1.1.0.0 RUN Chart from the MOS.

User-specific Reclassification Rules / Mapper Maintenance

Within reclassification rules, few rules where the source is customer-specific values. In such cases, these rules must be validated and updated as required by you, because the Out-of-Box rule may differ from what you have. Such rules are very few and restricted to:

·        Standard Product Type Reclassification

·        Standard Party Type Reclassification

·        Standard Mitigant Type Reclassification

·        Regulatory Industry Reclassification

·        Regulatory Credit Status Reclassification

·        Regulatory Loan Purpose Reclassification

·        Regulatory Credit Score Model Mapper Maintenance

·        GL Account to Reporting Line Mapper Maintenance

Table 4: Standard Dimension Reclassification

Maintenance of Mappers for Reclassification of Standard Dimensions

The mapper can be maintained under OFSAAI.

1.     After logging into the OFSAAI applications page, navigate to Regulatory Reporting for US Treasury, select Administration, and then select Map Maintenance.

Figure 37: Map Maintenance page

2.     For illustration, we have selected Mapper for Mitigant Type to Standard Mitigant Type. Click Mapper Maintenance.

Figure 38: Mapper for Mitigant Type to Standard Mitigant Type

3.     OFS REG REP US Treasury maps OTH and MSG out-of-the-box for this mapper. The remaining mappings can be maintained by the user according to user-specific values.

Figure 39: Map Maintenance Search page

Prerequisites for Mapper Maintenance

1.     After logging into the OFSAAI applications page, navigate to Regulatory Reporting for US Treasury, select Administration, and then select Save Metadata. Load all the required user-specific dimensions using SCD.

2.     To Resave these hierarchies, select these hierarchies and click Save.

§       HCMDF001 - Hier - Map Common Product

§       HCMDF002 - Hier - Map Common Standard Product Type

§       HCMDF003 - Hier - Map Common Party Type

§       HCMDF004 - Hier - Map Common Standard Party Type

§       HCMDF005 - Hier - Map Common Interest Rate Curve

§       HCMDF006 - Hier - Map Common Standard Interest Rate Curve

§       HCMDF007 - Hier - Map Common Line of Business

§       HCMDF008 - Hier - Map Common Standard Line of Business

§       HCMDF009 - Hier - Map Common Credit Line Type

§       HCMDF010 - Hier - Map Common Standard Credit Line Type

§       HCMDF011 - Hier - Map Common Credit Line Purpose

§       HCMDF012 - Hier - Map Common Standard Credit Line Purpose

§       HCMDF013 - Hier - Map Common Mitigant Type

§       HCMDF014 - Hier - Map Common Standard Mitigant Type

§       HCMDF015 - Hier - Map Common Balance Category

§       HCMDF016 - Hier - Map Common Standard Balance Category

§       HCMDF017 - Hier - Map Common General Ledger Code

§       HCMDF018 - Hier - Map Common Standard General Ledger Type

§       HCMDF019 - Hier - Map Common Vehicle Type

§       HCMDF020 - Hier - Map Common Standard Vehicle Type 

§       HCMDF021 - Hier - Map Common Write Off Reasons

§       HCMDF022 - Hier - Map Common Standard Write Off Reasons

§       HCMDF023 - Hier - Map Common Recovery Type

§       HCMDF024 - Hier - Map Common Standard Recovery Type

§       HRLMP001 - HIR - RLMP Industry Codes

§       HRLMP002 - HIR - RLMP Regulatory Industry Codes

§       HRLMP003 - HIR - RLMP Application Status

§       HRLMP004 - HIR - RLMP Regulatory Application Status

§       HRLMP005 - HIR - RLMP Document Type

§       HRLMP006 - HIR - RLMP Regulatory Document Type

§       HRLMP007 - HIR - RLMP Account Status

§       HRLMP008 - HIR - RLMP Regulatory Account Status

§       HRLMP009 - HIR - RLMP Regulatory Account Purpose

§       HRLMP010 - HIR - RLMP Organization Unit Code

§       HRLMP011 - HIR - RLMP Line of Business Code

§       HRLMP012 - HIR - RLMP Std Secondary Line of Business

§       HRLMP013 - HIR - RLMP Underlying Type

§       HRLMP014 - HIR - RLMP Regulatory Underlying Type

§       HRLMP501 - HIR - RLMP Property Type

§       HRLMP502 - HIR - RLMP Regulatory Property Type

§       HRLMP503 - HIR - RLMP Account Purpose

§       HRLMP504 - HIR - RLMP Regulatory Loan Purpose

§       HRLMP505 - HIR - RLMP Account Status Code

§       HRLMP506 - HIR - RLMP Regulatory Credit Status

§       HRLMP507 - HIR - RLMP Sec Pool Type

§       HRLMP508 - HIR - RLMP Regulatory Sec Pool Type

Figure 40: Metadata Resave page

Possible Mapping Combinations

One Standard Dimension table in the source can be mapped only to one Standard Dimension table. One to Many or Many to Many mapping leads to an error in T2T as the records are duplicated. From the illustration, the possible combinations for Mitigant Type to Standard Mitigant Type mapping are One to One and Many to One mapping.

·        One to One Mapping: You can map one Mitigant Type data model to one Standard Mitigant Type data model using the Mapper Maintenance screen. Here, you must select one value in the Mitigant Type data model and one value in the Standard Mitigant Type data model.

·        Many to One Mapping: You can map many values in the Mitigant Type data model to one value in the Standard Mitigant Type data model using the Mapper Maintenance screen.

To conduct One to One or Many to One mapping:

1.     After logging into the OFSAAI applications page, navigate to Regulatory Reporting for US Treasury, select Administration, and then select Map Maintenance

Figure 41: Map Maintenance Page

2.     Click Create new Map icon to create a new map or select an existing Map. For illustration, Mapper for Party Type Code to Standard Party Type Code value is selected. Click the Mapper Maintenance icon.

3.     The Mapper Maintenance window opens (in this illustration, the Map - Mapper for Party Type Code to Standard Party Type Code window opens). To conduct One to One or Many to One mapping, in the Member Combinations section, click Add.

Figure 42: Map Maintenance Add page

4.     The Add Mappings pop-up window opens. In this illustration:

§       To map One to One, select one value in the Hier - Map Common Mitigant Type data model and one value in the Hier - Map Common Standard Mitigant Type data model, and click Go. Repeat this step for each One to One data model mapping, and then click Save.

In this illustration, MSG - Missing is mapped to AFC - Auto Financing Company.

Figure 43: One to One Mapping page

·        To map Many to One, select multiple (two in this illustration) values in the Hier - Map Common Mitigant Type data model and one value in the Hier - Map Common Standard Mitigant Type data model, and then click Go. Click Save.

In this illustration, MSG-Missing and OTH-Others are mapped to the AFC-Auto Financing Company.

Figure 44: One to Many Mapping windows

5.     An acknowledgment is displayed: Confirm Save? To confirm and save data, click Yes. In the Mapper Maintenance window, in the Mapped combinations and the Mapped member's sections, you can see the newly conducted mapping.

Configuring Setup Tables for Standard Set of Values

The following are the setup configurations that are required to be done before executing the US Treasury Regulatory Reporting Run.

SETUP_MASTER Table

The SETUP_MASTER table in an atomic schema must be modified with the required values for US Treasury.

Table 5: Setup Master

FSI_PARTY_STD_PARTY_MAP

In the US Treasury Regulatory Reporting, there is a reporting requirement for certain Party which is considered to be Regulatory Standard. As Party Dimension is an SCD table and the values of Party Identifier Code (V_PARTY_ID) can change bank to bank, the FSI_PARTY_STD_PARTY_MAP is used for mapping the bank-specific V_PARTY_ID to Regulatory-specific V_STD_PARTY_CODE. Here, you must modify the V_PARTY_ID column according to the bank-specific V_PARTY_ID of the corresponding Party, which is stored in Party Dimension (DIM_PARTY).

The following are the STD Party Codes that are getting used in US Treasury Regulatory Reporting.

Table 6: Standard Party Codes

Run or Execution Expectations

Run refers to execution. It is assumed that at different periods, different combinations of parameters, and different data require different executions. From a reporting perspective, as required by regulators, the RRDF application requires data for the following executions:

1.     Current Data / Execution

a.     Reporting month-end data

b.     Projection Data

2.     Historical (trend/vintage) Data

a.     Yearly

b.     Quarterly

Stressed Data

Consolidation

Consolidation is handled as part of the Financial Services Data Foundation (FSDF). Consolidation in FSDF refers to the elimination of intracompany transactions, that is, any kind of transactions between two parties or entities which are part of the reporting organizational hierarchy for a given execution. When there is only one legal entity involved in an execution it is called SOLO Entity vs the earlier one as CONSOLIDATED Entity.

It is expected that in the staging area, the customer loads the data from the source system and then uses consolidation logic to arrive at the consolidated output for results.

·        The scope of consolidation is about a list of Entities that participate in consolidation.

·        Legal Entity Structure is looked at through ORGANIZATION STRUCTURE DIMENSION.  This stores a parent-child relationship. This is stored only once.

·        While moving the data, Legal Entity can move related entities to the processing/reporting area.

·        The legal structure being finalized once, this structure only stores one parent-child relationship.

Figure 45: Consolidation workflow

·        Transaction/exposure between SUB 1 and SUB 2 should be eliminated while reporting for Parent A.

·        Transaction/exposure between SUB 1 and SUB 3 should not be eliminated while reporting for Parent A.

·        It is a customer for banking products and issuer for traded securities which are considered for the intracompany elimination.

Consider the following example:

Table 7: Consolidation

As shown in the preceding table, Account 1 is moved to the FSI INTRA COMPANY ACCOUNT and Account Summary tables. Run Enabled tables contain records specific to the selected legal entity and consolidation type.

Consolidation is also linked to multiple hierarchies banking organizations have. Multiple hierarchies refer to the different grouping of group entities under different parents for the given regulatory requirements.

Refer to the following representation where TIC BC and TIC BL1 are two regulatory reporting requirements.

Figure 46: Consolidation with Multiple Hierarchies

Consolidation percentage refers to the percentage of asset or liability of a child entity that is brought under the parent heading. Except for Joint ventures and similar organization structures, child entities are moved under the parent or they are not. This means the consolidation percentage is either 100% or 0%. For proportionate consolidation (Joint venture is an example for this), a given child is moved under two parents with all assets and liabilities divided as per the Joint venture agreement. Currently, in FSDF 804, proportionate consolidation is not handled.

The hierarchy structure is thus the primary input to the consolidation process. Depending on whether you have multiple hierarchies or not, there are two data flows.

Consolidation with Multiple Organization Structure Hierarchy:

·        You load Organization Structure Hierarchy to the STAGE ORG STRUCTURE MASTER table, which is moved to the ORG STRUCTURE DIMENSION using the SCD component.

·        Execution-specific organization structure hierarchies along with parent and child entity codes are populated in the STAGE LEGAL ENTITY HIERARCHY INTERFACE table, which is moved to the LEGAL ENTITY HIERARCHIES DIMENSION using the SCD component.

·        Execution-specific Consolidation percentage is loaded in the STAGE ENTITY CONSOLIDATION PERCENTAGE table, where the child entity code, parent entity code, and the consolidation percentage are populated. This is moved to the FACT ENTITY CONSOLIDATION PERCENTAGE table using Table-to-Table transformation. In FSDF 804 release, this feature is not supported yet.

The STAGE LEGAL ENTITY HIERARCHY is used for the Consolidation process and not the one from ORGANIZATION STRUCTURE DIMENSION.

Figure 47: Consolidation with Multiple Organization Structure Hierarchy

If you do not have Multiple Hierarchies, STAGE LEGAL ENTITY HIERARCHY which is used for the Consolidation process can be populated from ORG STRUCTURE DIMENSION instead of the STAGE LEGAL ENTITY HIERARCHY.

Figure 48: Consolidation without Multiple Organization Structure Hierarchy

A Solo Run does not require any type of consolidation of the elimination of accounts with other entities.

Additional Data Preparations to handle Consolidation

The entity FCT_REG_RUN_LEGAL_ENTITY_MAP is used once you select REPORTING ENTITY from AgileREPORTER. This table is populated as part of the US Treasury Run Execution.

Table 8: Reporting Entity in AgileREPORTER

For the solo run, only one reporting entity is expected to be included whereas a consolidated run includes all entities involved in execution. This entity provides flexibility to select one REPORTING ENTITY in AgileREPORTER and select relevant data for the particular execution based on if it is consolidated or solo.

Relationship between Run and Stress

The REG REP application for example in TIC BC picks up reporting data based on the Reporting Run that populates the underlying Fact Table(s). Reporting Run is a flag, which must be marked as 'Y' in a DIM_RUN table so that, the OBIEE reporting layer selects a particular run execution.

In this application, a Run comprises:

Baseline Run: The Bank Holding Company (BHC) may have multiple runs. The run used for reporting is marked with a Reporting Flag = Y. This is the Baseline run for a given reporting date. It is referred to as a Baseline because the values that it represents are not stressed and the BHC may use these base values for stressing them according to various scenarios. A history of such runs accumulated over some time provides historical runs. For more information on updating the reporting flag, refer to section Updating Reporting Flag.

NOTE:   

For retrieving multiple Runs in AgileREPORTER for the same date, you must refresh the Derived Entities for each Run separately by enabling and disabling the Reporting Flag in a sequence.

Stress Run: Stress runs hold data, which are stressed by a certain percentage/basis point over the Baseline figures. The BHC expects these figures to reflect the business/risk position under predetermined business scenarios/economic conditions.

Identification of Baseline and Stress run occurs from STRESS DIMENSION.

In this application, the required stress runs are tagged to a Baseline run. If the BHC performs several stress runs, the relevant runs which are intended for reporting are identified and tagged with a reporting Baseline run using the V_RUN_ID in the DIM_RUN.

DIM RUN stores n_run_skey / v_execution_id, which are execution specific for every run definition which is v_run_id. Therefore, the run definition can remain constant over some time and different executions provide different outputs due to underlying data changes.

DIM_STRESS conveys the stress definition. Additionally, it links the original run Definition (v_run_id) and Stressed run ID (v_stressed_run_id). You must refer to the DIM_RUN table to get the expected run execution of these runs definitions about a particular date / n_mis_date_skey.

The same fact table stores both the Baseline data and the Stressed data, uniquely identified through Scenario codes (and Run skeys).

Refer to the Business Metadata.xls present in the installer package for details on different Fact tables used for related reports.

Data Flow from Source Systems to Staging Area

The staging area is populated with data from various data sources, such as GL data, Account data, Customer data, Trading data, Currency data, and Master data. See the Data Integration Hub (DIH) User Guide in OHC Documentation Library for details. DIH enables to load of the data from the source systems to the OFSAA staging tables, through logical interfaces, known as Application Data Interfaces (ADI). DIH provides a set of User Interfaces (UI), which is used to define and maintain External Data Descriptor (EDD), Application Data Interfaces, and map the EDDs and ADIs through connectors.

Data Flow from Staging to Results Area

This section details the pass-through data, transformed data, and classification.

Topics:

Pass-Through Data 

Derived or Transformed Data and Reclassifications 

Reclassified to Regulatory Classifications 

Pass-Through Data

Pass through data refers to the static data that is pre-processed and flows to the results area directly. The Common Staging Area (CSA) model represents the data entry point into the FSDF. CSA provides a simplified, unified data sourcing area for inputs required by analytical applications and engines. It consists of over 400 tables and nearly 9000 columns organized into distinct subjects.

The staging area is a physical data model, which is deployed using the Analytical Application Infrastructure, which manages it. The design of the staging area data model is to allow efficient data loading for analytics. It thus has crucial differences from a general-purpose repository of operational or transactional data across a bank.

The staging area acts as the single source of data and contains unified data requirements for various banking areas such as Loans and Losses, Off-balance Sheet products, Securities, Derivatives, Capital Data, Management Ledger, and General Ledger. A common example of this category includes various monetary amounts, dates, and so on.

Derived or Transformed Data and Reclassifications

OFSDF Interface with Lombard Risk for OFS REG REP US Treasury requires specific hierarchies and data to be transformed and reclassified to regulator specific values.

Table 9: Data Transformation Example

For example, data from banks has attributes such as issuer type and bank instrument type. However, these values are bank-specific and must be converted or reclassified to a regulatory specific set of values such as DIM REG INSTR CLASSIFICATION as mentioned above.

Reporting derived entities use these reclassified dimensions. Some of the reclassifications are performed in the respective application area.

For example, DIM BASEL PRODUCT TYPE. This reclassification is performed in Basel application processing and is available for reporting directly.

Other transformations include various bands such as delinquency band, loan purpose, and so on.

Reclassified to Regulatory Classifications

After transformation, the regulatory data is reclassified as follows.

Table 10: Data Reclassification Example 1

Table 11: Data Reclassification Example 2

The sample reclassifications performed to transform the existing hierarchies to regulatory specific hierarchies are:

·        Regulatory Product Classification

·        Regulatory Instrument Classification

·        Regulatory Deposit Classification

·        Regulatory Treasury Foreign Official Indicator

·        Regulatory Treasury Claim Liability Identifier

·        Regulatory Treasury Claim Liability Type Classification

·        Regulatory Organization Regional Group Classification

·        Regulatory Treasury Country Classification

The additional transformations that are performed are:

·        Remaining Time to Maturity Band

·        Contractual Maturity Band

See Business Metadata for details of these reclassifications.

Data Flow from Staging to Processing Area

The staging area of the FSDF serves as a container for analytical processing from sourcing to consumption. Such processing is usually delivered in the form of discrete units called analytical applications, spanning different analytical use cases ranging from Finance to Risk to Compliance.

These applications include custom-built computational engines and numerical libraries and can execute processes on the data that range from simple aggregations to complex, multi-step stochastic processes such as Monte-Carlo simulation.

Hence, analytical applications place varying demands on the data infrastructure in terms of volumes and speed and hence place different demands on the data architecture. In practice, the normalized (3NF) design favored for enterprise data warehouses often fails to be efficient or performant when it comes to analytical processing across a wide range of use cases.

Therefore, the OFSDF recognizes the need for distinct application-specific working stores, separate from the staging and reporting area. For example, the OFSAA Asset and Liability Management (ALM) application have a distinct set of ALM-specific tables, as does the Market Risk solution.

NOTE:   

The structure of these processing area stores is decided by the actual analytical application and engine used. The OFSAA suite of applications is organized this way, with each application managing a specific set of tables or schemas within the processing area.

The processing area tables or schemas are not part of the OFSDF. This is because OFSDF is intended to be an open platform. Other analytical applications and engines can equally provision data out of OFSDF by mapping their input requirements appropriately to the OFSDF staging area model.

Data Flow from Processing to Results Area

This step is similar to Data Flow from Staging to Results Area. It involves either pass through data from processing to results or loading directly to results. This is mostly due to processing measures such as Fair Value, Risk-Weighted Assets, and so on.

Guidelines for Data Loading to Result Area Tables in Data Foundation for Regulatory Reporting Implementations

Regulatory reports make use of data available across several fact tables in the OFSAA data foundation model and these result tables are either loaded from the raw data sourced from source systems via out-of-the-box T2Ts or processed data output from various OFSAA applications.

For example, Fact LRM Account Summary (FCT_LRM_ACCOUNT_SUMMARY) which stores the liquidity risk-related attributes and metrics computed by OFSAA LRM application, Fact Loan Loss Forecasting and Provision Account Summary (FCT_LLFP_ACCOUNT_SUMMARY) which stores the attributes and measures computed by OFSAA LLFP application. However, there can be several implementations use cases in the regulatory reporting space where the customer cannot have licensed any of the OFSAA applications and hence must put additional custom effort to design an ETL process to load the required data elements into the respective fact tables referenced by the report. The following section highlights some of the guidelines that the customer can consider when designing a data flow for such a use case.

·        Consistent Usage of Run Identifier

Most of the fact tables used in regulatory reporting are run enabled and have a composite primary key inclusive of run identifier that enables the same snapshot of data to be loaded multiple times into the target fact table for any given execution date. All the out of the box processes that impact data used in regulatory reports are executed as part of an integrated run to ensure that run identifier is consistent across fact tables. Since the reporting is done on an integrated schema, the customs data flow design must keep this integrity intact. This essentially means that the custom ETL processes designed to load the data directly into the fact tables must be able to leverage the run identifier generated by the run engine during execution. Run Identifier information is available in the DIM_RUN table.

·        Correct Dimensional Lookup Configuration

Dimensional identifiers are typically part of referential integrity constraints with the fact table so the custom ETL processes must ensure that lookups retrieve a valid surrogate key for a given value of the business key. The intermediate staging structure must ensure all the business keys are persisted correctly and the lookup condition is designed on the correct dimension table.

For example, FCT_LRM_ACCOUNT_SUMMARY.n_asset_level_skey refers DIM_ASSET_LEVEL.n_asset_level_skey. The business key (v_asset_level_code) must be sourced and persisted to ensure correct values are populated in the target column, that is, FCT_LRM_ACCOUNT_SUMMARY.n_asset_level_skey.

·        Data Loading Guidelines for handling Negative or Credit Balances

·        To handle Negative Balances in Regulatory Reporting, there are two primary sources of the negative balances:

a.     Natural asset negative balances from the system of records

b.     Adjustment entries or Plug entries.

The reporting requirement is to show the genuine asset negative balances as liabilities where adjustment entries should be aggregated to the same heading assets or liabilities as they are loaded. US Treasury uses the Treasury Claim Liability Identifier Dimension. Primarily following two General Claim Liability codes are used for this purpose.

a.     CLAIM

b.     LIABILITY

Treasury Claim Liability Identifier is populated based on the claim liability identifier rule. Following conditions are considered for the treatment of negative balances based on product and instrument type:

a.     Loans and Cards

i.       Loans are reported under the Assets category in the Balance Sheet. There are cases when a customer makes an excess payment towards the loan account which makes the end of the period account balance becoming credit balance or negative balance.

ii.     When excess payment is made, then the account does not fall under the Asset category, but it becomes a liability for the financial institution and must be reported as non-interest-bearing demand deposits in respective line items.

iii.   To avoid reporting the excess payment as a Claim, you must assign a Claim Liability Identifier to a given account with V_TRS_CLAIM_LIAB_IDEN_CD= 'LIAB'.

iv.   When for any loan regulatory reclassification assigned with Claim Liability Identifier having V_TRS_CLAIM_LIAB_IDEN_CD= 'LIAB', it excludes the reporting for all claim line items and it is added to Liability in respective line items.

b.     Products

i.       All the products are identified as Claim or Liability based on the balance sheet category when the instrument code is null. If the balance sheet category is Asset, then the Claim Liability Identifier code is Claim, if the balance sheet category is Liability, then the Claim Liability Identifier code is Liability.

c.     Instruments

i.       If the instrument code is not null, then the buy-sell indicator is used to update the Claim Liability Identifier code. Any instrument with a buy flag is a Claim and a sell flag is a Liability.

ii.     If the buy-sell indicator is buying and the fair value is less than 0, then the Claim Liability Identifier code is Liability.

Currently, this feature is enabled for BC, BL-1, BL-2, BQ-1, BQ-2, and BQ-3 Reports only. Other reports to uptake this feature in subsequent releases.

From the OFSAA technical infrastructure standpoint, the mentioned options are available to the customer to design and implement the custom ETL process explained above. OFSAA strongly recommends the following options to maintain consistency in terms of data lineage in the Metadata browser as the configured metadata can be made available in the meta-model through MDB publish:

1.     Data Integration Hub (DIH) Connectors

2.     Data Mapping (T2T) option in Application Infrastructure

3.     Data File Mapping (F2T) option in Application Infrastructure

Topics:

DIH Connectors 

Data Mapping (T2T) 

Data File Mapping (Flat File to RDBMS Target - F2T) 

DIH Connectors

If you have a licensed DIH to source the data from the external systems into OFSAA, a DIH connector is the recommended approach to load the data into results. The Source data could either reside in a relational structure or a file structure. The mappings maintained in DIH are logical and they abstract the physical references including the Dimensional lookups seamlessly without the need for any additional join or configuration.

See the Data Integration Hub (DIH) User Guide, for more information about loading the data into a result area table.

Figure 49: DIH Connectors

Data Mapping (T2T)

Data Mapping refers to the process of retrieving unstructured data from data sources for further data processing, storage, or migration. This feature is commonly known as the RDBMS source to RDBMS target (T2T) framework in the OFSAA world and can be leveraged when source data is available in the Oracle database. Dimensional lookups must be handled via the T2T’s join condition and expressions. See the OFSAAI User Guide for more details on configuring a T2T.

Data File Mapping (Flat File to RDBMS Target - F2T)

If the source data is available in file structures, the OFSAA F2T component can be used to bring the data into the OFSAA ecosystem. As lookups cannot be configured in an F2T, this component must be used in conjunction with the T2T component, that is, data is first loaded from the file to an interim staging structure using the F2T component followed by data load to the target result area table using the T2T component.  This is the least recommended approach as there is a need for interim table structure in the data model and involves multiple data hops that add to the overhead.

See the OFSAAI User Guide, for more details on configuring an F2T.

FSDF Entity Information

The FSDF entity information is given in the Dimension Tables and Data Elements documents available on the MOS page.

OFS Regulatory Reporting for US Treasury - Dimension Tables <release version>

OFS Regulatory Reporting for US Treasury - Data Elements <release version>

Dimension Tables or Entities

Table 12: Dimension Tables or Entities

Fact Tables or Entities

For all tables with data flow type tagged as a Processing, it is recommended that you map data directly to the result area if the processing application is not part of the OFSAA product suite. For example, Basel computations, RWA Numbers, and Capital Ratio are taken from the processing area which is populated by OFSAA or other Basel applications.

For processed tables, you can look for the following options:

·        OFSAA Data Integration Hub (DIH) product

·        Flat File

·        Table-to-Table Transformation with the source being processing application

The list of processing output tables is available in the OFS Regulatory Reporting for US Treasury - Data Elements <release version> document on the MOS page.

Table 13: Fact Tables/Entities

Overview of OFS REG REP User Interface

This section provides details to log in to the OFSAA application, view report summary, view schedule summary, view cells, and map data schedules. It includes:

·        Logging in to OFS REG REP UI

·        Viewing Report Summary

·        Viewing Schedule Summary 

·        Viewing Data Elements

·        Viewing Cell Summary

Logging in to OFS REG REP UI

After the applications are installed and configured, to access the OFS REG REP UI you must log in to the OFSAAI environment using the OFSAAI login page.

NOTE:   

The built-in security system ensures that you are permitted to access the window and actions based on the authorization only.

To access the OFS REG REP UI, follow these steps:

1.     Enter the OFSAAI URL in your browser. The OFSAAI login page is displayed.

Figure 50: OFSAAI Log In

2.     Select the desired language from the Language drop-down list.

3.     Enter your User ID and Password. When you log into OFSAAI, the OFSAA Applications page is displayed.

Figure 51: OFSAA Applications Screen

4.     Select Financial Services Data Foundation. The FSDF landing page is displayed.

Figure 52: Financial Services Data Foundation Landing Page

5.     Or select the Regulatory Reporting for US Treasury. The Regulatory Reporting for Reporting for US Treasury landing page is displayed.

Figure 53: Regulatory Reporting for US Treasury Page

Viewing Report Summary

The Report Summary data comes pre-seeded based on the applications that are installed. The Report Summary enables you to view all the configured reports for the jurisdiction.

After logging into the OFS REG REP UI, navigate to Regulatory Reporting Metadata and select Reports to view the Reports Summary window.

Figure 54: Report Summary Screen

NOTE:   

You can view the summary of all the configured reports in the Tile view  or List view.

The Search Bar helps you to find the required information from the database. You can enter the nearest matching keywords to search and filter the results by entering information in the search box. You can search for a Report using either the name or description.

Figure 55: Report Summary Search Bar

The Paging option at the bottom right corner allows you to see more reports than the ones currently displayed on the window.

Figure 56: Report Summary Paging Option

Report Information

Each tile or list on the Report Summary window corresponds to one report. For each report, you can view the report code, report description, number of schedules within the report, the number of configured non-derived cells, and count of utilized derived entities.

For example, the CRSA report in the tile or list view is displayed as follows:

Figure 57: Report in Tile View

Figure 58: Report in List View

Select the Report Code to navigate to the Schedule Summary window.

Figure 59: Report Information

Viewing Schedule Summary

The Schedule Summary window provides the component schedules for the corresponding report. Select the Report Code in the Report Summary window to navigate to the Schedule Summary window (as shown in Figure 66).

Figure 60: Schedule Summary Screen

NOTE:   

You can view the summary of all the configured reports in the Tile view  or List view.

The Search Bar helps you to find the required information from the database. You can enter the nearest matching keywords to search and filter the results by entering information in the search box. You can search for a Schedule using either the name or description.

The Paging option (Figure 61) at the bottom right corner allows you to see more reports than the ones currently displayed on the window.

NOTE:   

Select the  icon on the top right corner to return to the Report Summary window.

Schedule Information

Each tile or list on the Schedule Summary window corresponds to one schedule under the report. For each schedule, you can view the schedule code and the description, the number of configured non-derived cells for the schedule, and the count of utilized derived entities.

For example, the Schedule Page3 tile is displayed as follows. Select the Schedule Code to navigate to the Cell Information window.

Figure 61: Schedule Information

Viewing Data Elements

Each tile or list on the Report Summary window corresponds to one report. For each report, you can view the report code, report description, number of schedules within the report, the number of configured non-derived cells, and count of utilized derived entities.

Figure 62: Report Information

1.     Click the chain icon on the right top corner to display the data elements for the respective item. The data elements view option is available at the report schedule and cell level.

Figure 63: Data Elements Screen

2.     Select Filter to apply filters on the selected date. The filter pane allows filtering data on specific columns.

Figure 64: Filters

3.     Select Apply Filter to apply the required filters on the selected date.

4.     Select Clear Filter to clear the applied filters and display all records for the component.

5.     Select Export to CSV to export the data displayed in the window.

Viewing Cell Summary

The Cell Summary window provides the non-derived cells or MDRMs configured as a part of the solution for the corresponding schedule under a report. Select the Schedule Code in the Schedule Summary window to navigate to the Cell Summary window (as shown in Figure 65).

For example, the Cells for Schedule Page3 summary window under the CRSA report is displayed as follows.

Figure 65: Cell Summary Window

NOTE:   

You can view the summary of all the configured reports in the Tile view  or List view.

The Search Bar helps you to find the required information from the database. You can enter the nearest matching keywords to search and filter the results by entering information in the search box. You can search for a Cell using either the name or description.

The Paging option (Figure 65) at the bottom right corner allows you to see more reports than the ones currently displayed on the window.

NOTE:   

Select the  icon on the top right corner to return to the Report Summary window.

Topics:

Cell Information 

Derived Entity 

Measure 

Filters 

Cell Information

Each tile or list on the Cell Summary window corresponds to one cell or MDRM under the schedule. For each cell, you can view the MDRM name, count of utilized derived entities, count of utilized OFSAA hierarchies, and measures for that cell.

For example, the cell CRSAR040C180 tile is displayed as follows. Select the cell or MDRM Code to navigate to the Cell Information window.

Figure 66: Cell Information

The Cell Information window is displayed as follows.

Figure 67: Cell Information Window

Each section in the Cell Information window displays the relevant OFSAA Metadata and filters used for the cell.

Derived Entity

This displays the name of the OFSAA Materialized View or View that contributes to the Cell.

Measure

This displays the name of the OFSAA Measure that is reported for the particular Cell.

Filters

The Filter conditions are as follows:

1.     All filters that are applied to the cell are displayed under the filter section. It displays all the applied filters as their OFSAA description.

2.     On selection, the filter is marked by a  sign on the top left corner of the selected filter.

3.     The section that follows displays the entity or table on top of which the filter is based and the OFSAA Level Description for the selected filter.

4.     All filter values that apply to the particular MDRM are available as a ribbon. Each filter value is in a separate box.

For example, in the previous case for MDRM CRSAR040C180, the applied filters are Consolidation Code and Reporting Line Code. Currently, the Consolidation Code filter is selected and the required filter values for the same are 100.

In the case of multiple values, the filters are displayed as follows with an arrow mark.

Figure 68: Multiple Filter Values

The filters in case of not in condition are highlighted in red are displayed as follows.

Figure 69: Not in Condition Filters

Mapping of Results to Reporting Requirements of Lombard Risk

Figure 70 explains the flow of data between OFSAA and AgileREPORTER.

Figure 70: Data Flow between OFSAA and AgileREPORTER

OFSAA provides the data to AgileREPORTER in the form of derived entities. The derived entity is an existing OFSAA higher-order metadata object and can be physicalized as a materialized view in the database. Derived entities store aggregated data from base fact entities specified in the dataset and have the necessary dimensions and measures.

Dimensional and measure combination stored within the derived entity is mapped to cells within the report. This mapping is maintained within the Dimensional Mapping template.

Decision Process within AgileREPORTER reads the derived entities and dimension mapping information to derive the data for reporting. Derived entities are created based on measures, hierarchies, and datasets.

Figure 71: Decision Process in AgileREPORTER

Some cells in the schedule can be derived as per the logic provided by the regulator. Derivation can be an expression built using values from other cells. Examples of derivation are ratio, node-level rollup, a direct reference to cells in other schedules within the report. These derivations are performed within the AgileREPORTER. OFSAA provides data only for the cells that are not derived.

NOTE:   

Metadata for data transformation is available as part of the data warehouse configuration pack provided ready-to-use or preconfigured from OFSAA. You need not perform any mapping for the reports. However, this information can be useful for maintenance or extensions when a ready-to-use pack is not available.

AgileREPORTER: Submission

The AgileREPORTER is a web-based regulatory reporting tool provided by Lombard Risk. It provides necessary features to address e-filing workflow, validation, and submission process and supports reports (called forms or returns) for various jurisdictions. AgileREPORTER provides a reliable and efficient infrastructure to compile, generate, and submit regulatory reports.