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:
· Basel Processing to EBA Results Integration
· LRM Processing to EBA Results Integration
· Overview of OFS AGILE RP User Interface
· Adjustment Feature for Template-based Reports
· Direct Upload for Data Schedules
· Mapping of Results to Line Items in Reporting
This section explains the input data preparation from OFSAA.
Topics:
· Assumptions for Data Preparation
· Run or Execution Expectations
· Data Flow from Source 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
The following are the assumptions for data preparation:
1. RRS is a reporting solution, which uses data from underlying fact tables directly for reporting. The end-user is expected to prepare the load for the required data in the reporting area accordingly. Although this has a thin processing layer to reclassify to 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 RRS data sourcing.
3. Baseline and stress data must be populated with appropriate codes. Inaccurate mappings may lead to inaccurate results.
4. 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 or year), only one set of data is expected to be stored.
5. 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).
6. For AnaCredit reporting, default status, if any, is expected at a counterparty level and not at the instrument level.
7. For AnaCredit reporting, the interest rate curve is expected as a concatenation of interest rate curve plus currency plus tenor. For instance, LIBOR in USD corresponding to 1 Month is expected as LIBOR USD 1M.
8. Data for the Attribute Type of Identifier is a direct download from the reporting institution (@stg_party_identification_doc. v_doc_type_code) and it is not seeded by FSDF. So the reporting institution has to provide the download in sync with the regulatory requirements and periodic updates, as applicable.
9. PK for FCT_OREC_UOM_CAPITAL_SUMMARY is N_UOM_SKEY and it can be either entity or the LOB. Hence, there is no entity Skey separately that can result in Cartesian for the C16 report.
Oracle Financial Services Regulatory Reporting for EBA (OFS AGILE RP EBA) provides the EBA RUN Chart listing the tasks required for the population of data for EBA 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
· EBA Specific Data Population and Transformation
· Derived Entity Refresh
· Download the OFS AGILE RP EBA 8.1.0.0.0 Run Chart from the MOS.
Run refers to execution. It is assumed that at different periods, a different combination of parameters, and different data require different executions. From a reporting perspective, as required by regulators, data is required for the following executions:
1. Current Data or Execution
a. Reporting month-end data
b. Projection Data
2. Historical (trend or vintage) Data
a. Yearly
b. Quarterly
3. Stressed Data
NOTE:
· For Movement measures data is not carried from one reporting period to another. For example, Profit or Loss. Where General ledger balances such as loan outstanding are carried forward from one year to another, profit and loss are period specific.
· Therefore, unlike End of Period (EoP) balance, movement values for quarter actuals must be derived for reporting. For historical data, net sales for quarter 3 is the difference between the sales figure as of the end of quarters 2 and 3. You are not required to provide this difference as a download. Movement data for actual is identified through different runs and the respective values are summed up.
· Only those records, whose corresponding runs fall between the fiscal month start date and end date of the reporting quarter are selected for summation. Each Run has an associated date and runs can be performed daily. Assuming that runs are performed daily in a given quarter (90 days), RRS sums up data points across all 90 days to arrive at a quarter-end movement figure.
· However, when the projection of net sales for quarter 2 next year is to be performed, no derivation is required. Projections data for the said quarter can be directly downloaded in the respective Fact table (or tables) for reporting.
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 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.
This section details the pass-through data, transformed data, and classification.
Topics:
· Derived or Transformed Data and Reclassifications
· Reclassified to Regulatory Classifications
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.
OFSDF Interface with Lombard Risk for OFS AGILE RP EBA requires specific hierarchies and data to be transformed and reclassified to regulator specific values.
Source Hierarchy |
Target Hierarchy |
|
|---|---|---|
Maturity Date |
As-of-Date |
DIM EBA Remaining Maturity |
This rule uses the bank-specific data coming at the account level, such as the maturity date. The difference between the maturity date and the as-of-date is computed to arrive at the remaining maturity band. However, these values are bank-specific and must be converted or reclassified to a regulatory specific set of values such as DIM EBA Remaining Maturity Band as mentioned earlier.
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 available for reporting directly.
Other transformations include various bands such as delinquency band, loan purpose, and so on.
After transformation, the regulatory data is reclassified as follows.
Source |
Attribute |
Interim Target |
Target |
|---|---|---|---|
DIM PRODUCT |
Withdrawable Reserve |
DIM STANDARD PRODUCT |
DIM REG PRODUCT |
Checking Accounts |
= N |
CASA |
Current Accounts |
FCT REG ACCOUNT SUMMARY |
|||
|---|---|---|---|
Account Number |
REG PROD Classification |
Remaining Maturity Band |
Delinquency Band |
1 |
OTHER TERM LOAN |
1 |
3 |
The sample reclassifications performed to transform the existing hierarchies to regulatory specific hierarchies are as follows:
· Regulatory Product Classification
· Regulatory Instrument Classification
· Trading Account Book Type Classification
The additional transformations that are performed are:
· Remaining Time to Maturity Band
· Regulatory Delinquency Band
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 ready-to-use rule can differ from what you have. Such rules are very few and restricted to the following:
1. Standard Product Type Reclassification
2. Standard Party Type Reclassification
3. Standard Mitigant Type Reclassification
4. Regulatory Industry Reclassification
5. Regulatory Credit Status Reclassification
6. Regulatory Loan Purpose Reclassification
AnaCredit Reclassification Rules are as follows:
Sl. No. |
Rule Name |
Rule Type |
Source Table |
Source Column |
Target Table |
Target Column |
|---|---|---|---|---|---|---|
1 |
Standard Product Reclass DIM_PRODUCT_TYPE to DIM_STANDARD_PRODUCT_TYPE |
CUSTOM |
FCT_REG_ACCOUNT_SUMMARY |
N_PROD_TYPE_SKEY |
FCT_REG_ACCOUNT_SUMMARY |
N_STANDARD_PRODUCT_TYPE_SKEY |
2 |
Standard Party Reclass DIM_PARTY_TYPE to DIM_STANDARD_PARTY_TYPE |
CUSTOM |
FCT_REG_ACCOUNT_SUMMARY |
N_PARTY_TYPE_SKEY |
FCT_REG_ACCOUNT_SUMMARY |
N_STANDARD_PARTY_TYPE_SKEY |
[Party granule] DIM_PARTY |
[Party granule] V_PARTY_TYPE |
FCT_REG_ACCT_JOINT_LIABILITY |
||||
[Joint Liability granule] DIM_PARTY |
[Joint Liability granule] V_PARTY_TYPE |
FCT_REG_PARTY_DETAILS |
||||
3 |
Standard Mitigant Reclass DIM_MITIGANT_TYPE to DIM_STD_MITIGANT_TYPE |
CUSTOM |
FCT_MITIGANTS |
N_MITIGANT_TYPE_SKEY |
FCT_MITIGANTS |
N_STD_MITIGANT_ TYPE_SKEY |
4 |
Encumbrance Source DIM_REG_ENCUMB_SOURCES DIM_ENCUMBRANCE_SOURCES |
CUSTOM |
FCT_COMMON_ACCOUNT_SUMMARY |
N_ENCUMBRANCE_SOURCE_SKEY |
FCT_REG_ANAC_INST_ACCT_FIN_DS |
N_REG_ENCUMB_ SRC_SKEY |
5 |
Legal Proceeding Status DIM_REG_LEGAL_PROCEDING_ STATUS - DIM_LEGAL_PROCEDING_STATUS |
CUSTOM |
FCT_REG_PARTY_DETAILS |
N_LEGAL_PROCEEDING_STS_SKEY |
FCT_REG_ANAC_CNTRPRTY_REF_DS |
N_REG_LEGAL_PROCEDING_STS_SKEY |
6 |
Type of Protection Value DIM_REG_PROTECTION_VALUE_ TYPE - FCT_MITIGANTS |
CUSTOM |
FCT_MITIGANTS |
V_TYPE_OF_PROTECTION_VALUE |
FCT_REG_ANC_PRTN_RCD_DS |
N_REG_PROTECT_ VALUE_TYPE_SKEY |
7 |
Forbearance Status FCT_REG_ACCOUNT_SUMMARY - FCT_COMMON_ACCOUNT_SUMMARY |
CUSTOM |
FCT_COMMON_ACCOUNT_SUMMARY |
N_FORBORNE_STATUS_SKEY |
FCT_REG_ANAC_INST_ACCT_FIN_DS |
N_REG_FORBORNE_STATUS_SKEY |
8 |
Account Purpose DIM_REG_ACCOUNT_PURPOSE - DIM_ACCOUNT_PURPOSE |
CUSTOM |
FCT_COMMON_ACCOUNT_SUMMARY |
N_ACCOUNT_PURPOSE_SKEY |
FCT_REG_ANAC_INST_ACCT_FIN_DS |
N_REG_ACCT_PURPOSE_SKEY |
9 |
Industry Type DIM_REG_INDUSTRY - DIM_INDUSTRY |
CUSTOM |
DIM_PARTY |
V_INDUSTRY_CODE |
FCT_REG_ANAC_CNTRPRTY_REF_DS |
N_REG_INDUSTRY_SKEY |
10 |
Organization Constituent Type DIM_REG_ORG_CONSTITUTENT_TYPE DIM_PARTY |
CUSTOM |
DIM_PARTY |
V_ORG_CONSTITUENT_TYPE |
FCT_REG_ANAC_CNTRPRTY_REF_DS |
N_REG_ORG_CONST_TYPE_SKEY |
11 |
Securitization Type- DIM_REG_SECURITIZATION_TYPE (CUSTOM) |
CUSTOM |
FCT_COMMON_ACCOUNT_SUMMARY |
N_SECURITIZATION_TYPE_SKEY |
FCT_REG_ANAC_INST_ACCT_FIN_DS |
N_REG_SECURITIZATION_TYPE_SKEY |
12 |
Accounting Classification DIM_REG_ACCT_CLASSIFICATION - DIM_HOLDING_TYPE |
OUT-OF-BOX |
||||
13 |
Default Status DIM_REG_DEFAULT_STATUS - FCT_PARTY_DETAILS and FCT_COMMON_ACCOUNT_SUMMARY |
OUT-OF-BOX |
||||
14 |
Enterprise Classification DIM_REG_ENTPRISE_CLASSIFICTION - DIM_PARTY and FCT_PARTY_FINANCIAL_DETAIL |
OUT-OF-BOX |
||||
15 |
Interest Rate Reset Frequency DIM_REG_FREQUENCY_TYPE - FCT_COMMON_ACCOUNT_SUMMARY |
OUT-OF-BOX |
||||
16 |
Payment Frequency FCT_REG_ACCOUNT_SUMMARY - FCT_COMMON_ACCOUNT_SUMMARY |
OUT-OF-BOX |
||||
17 |
Amortization Type DIM_REG_AMORTIZATION_TYPE - DIM_AMORTIZATION_TYPE |
OUT-OF-BOX |
||||
18 |
Regulatory Product type FCT_REG_ACCOUNT_SUMMARY DIM_STANDARD_PRODUCT_TYPE |
OUT-OF-BOX |
||||
19 |
Protection Valuation Method - DIM_REG_VALUATION_METHOD - DIM_VALUATION_METHOD |
OUT-OF-BOX |
||||
20 |
Institutional Sector DIM_REG_PARTY_TYPE - DIM_STANDARD_PARTY_TYPE |
OUT-OF-BOX |
||||
21 |
Credit Status DIM_REG_CREDIT_STATUS - DIM_CREDIT_STATUS |
OUT-OF-BOX |
||||
22 |
Mitigant Type DIM_REG_MITIGANT_TYPE - DIM_STD_MITIGANT_TYPE |
OUT-OF-BOX |
||||
23 |
GL Account to Reporting Line Mapper Maintenance |
OUT-OF-BOX |
Sl. No. |
Rule Name |
Rule Type |
Source Table |
Source Column |
Target Table |
Target Column |
|---|---|---|---|---|---|---|
1 |
Standard Product Type Reclass DIM_PRODUCT_TYPE to DIM_STANDARD_PRODUCT_TYPE |
CUSTOM |
FCT_REG_ACCOUNT_SUMMARY |
N_PROD_TYPE_SKEY |
FCT_REG_ACCOUNT_SUMMARY |
N_STANDARD_PRODUCT_TYPE_SKEY |
2 |
Standard Party type Reclass DIM_PARTY_TYPE to DIM_STANDARD_PARTY_TYPE |
CUSTOM |
FCT_REG_ACCOUNT_SUMMARY |
N_PARTY_TYPE_SKEY |
FCT_REG_ACCOUNT_SUMMARY |
N_STANDARD_PARTY_TYPE_SKEY |
3 |
Standard Mitigant Reclass DIM_MITIGANT_TYPE to DIM_STD_MITIGANT_TYPE |
CUSTOM |
FCT_MITIGANTS |
N_MITIGANT_TYPE_SKEY |
FCT_MITIGANTS |
N_STD_MITIGANT_ TYPE_SKEY |
4 |
Encumbrance Source DIM_REG_ENCUMB_SOURCES DIM_ENCUMBRANCE_SOURCES |
CUSTOM |
FCT_COMMON_ACCOUNT_SUMMARY |
N_ENCUMBRANCE_SOURCE_SKEY |
FCT_REG_ACCOUNT_SUMMARY |
N_REG_ENCUMB_SRC_SKEY |
5 |
Issuer Type DIM_ISSUER_TYPE to DIM_STANDARD_PARTY_TYPE |
CUSTOM |
FCT_COMMON_ACCOUNT_SUMMARY |
N_ISSUER_TYPE_SKEY |
FCT_COMMON_ACCOUNT_SUMMARY |
N_STANDARD_ISSUER_TYPE_SKEY |
6 |
Valuation Method - DIM_VALUATION_METHOD to DIM_REG_VALUATION_METHOD |
CUSTOM |
FCT_FIXED_ASSETS |
N_VALUATION_METHOD_SKEY |
FCT_REG_FIXED_ASSETS |
N_REG_VALUATION_METHOD_SKEY |
7 |
Fixed Asset Code - DIM_FIXED_ASSETS to DIM_REG_FIXED_ASSETS |
CUSTOM |
FCT_FIXED_ASSETS |
N_FIXED_ASSET_SKEY |
FCT_REG_FIXED_ASSETS |
N_REG_FIXED_ASSET_SKEY |
8 |
Hedge Type DIM_HEDGE_TYPE to DIM_REG_HEDGE_TYPE |
CUSTOM |
FCT_COMMON_ACCOUNT_SUMMARY |
N_HEDGE_TYPE_SKEY |
FCT_REG_HEDGE_SUMMARY |
N_REG_HEDGE_TYPE_SKEY |
9 |
Industry Type DIM_REG_INDUSTRY - DIM_INDUSTRY |
CUSTOM |
FCT_COMMON_ACCOUNT_SUMMARY |
N_D_CUST_INDUSTRY_SKEY |
FCT_REG_ACCOUNT_SUMMARY |
N_CUST_REG_INDUSTRY_SKEY |
10 |
Party Relationship Type DIM_PARTY_RELATIONSHIP_TYPE to DIM_REG_PARTY_RELATIONSHIP_TYPE |
CUSTOM |
FCT_ENTITY_PARENT_INFO |
N_RELATIONSHIP_TYPE_SKEY |
FCT_ PARTY _ PARTY_RELATIONSHIP |
N_REG_RELATIONSHIP_TYPE_SKEY |
11 |
Consolidation Approach DIM_CONSOLIDATION_APPROACH to DIM__REG_CONSOLIDATION_APPROACH |
CUSTOM |
FCT_LEGAL_ENTITY_CNSLDTN_MAP |
N_CONSL_APPROACH_SKEY |
FCT_LEGAL_ENTITY_CNSLDTN_MAP |
N_REG_CONSL_APPROACH_SKEY |
12 |
Accounting Classification DIM_REG_ACCT_CLASSIFICATION - DIM_HOLDING_TYPE |
OUT-OF-BOX |
|
|||
13 |
Default Status DIM_REG_DEFAULT_STATUS - FCT_PARTY_DETAILS and FCT_COMMON_ACCOUNT_SUMMARY |
OUT-OF-BOX |
|
|||
14 |
Regulatory Product type FCT_REG_ACCOUNT_SUMMARY DIM_STANDARD_PRODUCT_TYPE |
OUT-OF-BOX |
|
|||
15 |
Credit Status DIM_REG_CREDIT_STATUS - DIM_CREDIT_STATUS |
OUT-OF-BOX |
|
|||
16 |
Mitigant Type DIM_REG_MITIGANT_TYPE - DIM_STD_MITIGANT_TYPE |
OUT-OF-BOX |
|
|||
17 |
GL Account to Reporting Line Mapper Maintenance |
OUT-OF-BOX |
|
|||
18 |
Instrument Standard Party Type DIM_PARTY_TYPE to DIM_STANDARD_PARTY_TYPE |
Custom |
DIM_INSTRUMENT_CONTRACT |
v_issuer_code |
Fct_Reg_Issued_Instr_Positions |
n_standard_party_type_skey |
19 |
Immediate Obligor FCT_REG_ACCOUNT_SIMMARY.N_IMMEDIATE_OBLIGOR_SKEY |
OUT-OF-BOX |
|
|||
20 |
Immediate Obligor Country FCT_REG_CAP_ACCT_ASSET_CLASS. N_CP_COUNTRY_SKEY |
OUT-OF-BOX |
|
|||
21 |
Ultimate Obligor Country FCT_REG_CAP_ACCT_ASSET_CLASS. N_ULTIMAT_PARENT_COUNTRY_SKEY |
OUT-OF-BOX |
|
|||
22 |
Securities Guarantor Reclass DIM_REG_PARTY_TYPE.V_REG_PARTY_CD |
CUSTOM |
FCT_COMMON_ACCOUNT_SUMMARY |
N_SECURITIES_GUARANTOR_SKEY |
FCT_REG_ACCOUNT_SUMMARY |
N_REG_SEC_GUARANTOR_TYPE_SKEY |
23 |
Constituted Form Reclass DIM_REG_CONSTITUTED_FORM. V_REG_CONSTITUTED_FORM_CODE |
CUSTOM |
FCT_LEGAL_ENTITY_DETAILS |
N_CONSTITUED_FORM_SKEY |
FCT_LEGAL_ENTITY_DETAILS |
N_REG_CONSTITUTED_FORM_SKEY |
24 |
Entity Type Reclass DIM_REG_ENTITY_TYPE.V_REG_ENTITY_TYPE_CODE |
CUSTOM |
FCT_LEGAL_ENTITY_DETAILS |
N_ENTITY_TYPE_SKEY |
FCT_LEGAL_ENTITY_DETAILS |
N_REG_ENTITY_TYPE_SKEY |
25 |
Standard Issuer Type Reclass DIM_STANDARD_PARTY_TYPE.V_STANDARD_PARTY_TYPE_CODE |
CUSTOM |
DIM_INSTRUMENT_CONTRACT |
V_ISSUER_CODE |
FCT_REG_ISSUED_INSTR_POSITIONS |
N_STANDARD_PARTY_TYPE_SKEY |
See Business Metadata for details on these reclassifications.
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 has 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.
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.
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 application 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.
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 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:
§ Data File Mapping (Flat File to RDBMS Target - F2T)
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 53: DIH Connectors
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 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.
If the source data is available in file structures, the OFSAA F2T component can be used to bring the data in 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.
The FSDF entity information is given in the Dimension Tables and Data Elements documents available on the MOS page.
OFS Regulatory Reporting for European Banking Authority - Dimension Tables <release version>
OFS Regulatory Reporting for European Banking Authority - Data Elements <release version>
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 are available in the OFS Regulatory Reporting for European Banking Authority - Data Elements <release version> document in the MOS page.
You must provide inputs to the following tables based on the data tables available in the OFS AGILE RP EBA.
Sl. No. |
Seeded Table |
Table/Entity Logical Name |
Table/Entity Description |
|---|---|---|---|
1 |
FCT_IFRS_ACCOUNT_SUMMARY |
Fact IFRS Account Summary |
This table holds the measures related to the account that are computed by IFRS application. |
2 |
FCT_MGMT_REPORTING |
Fact Management Reporting |
This table stores management reporting data related to organization and product profitability/income statement/balance sheet. |
3 |
FCT_REG_MARKET_RISK_EXPOSURES |
Fact Regulatory Market Risk Exposures |
This table stores Basel Processing output for Market Risk Exposures for Regulatory Reporting. |
4 |
FCT_HEDGED_ACCT_MAP |
Fact Hedged Account Map |
This table stores the mapping between the account which is the hedged item and the Hedge ID. |
5 |
FCT_REG_HEDGE_SUMMARY |
Fact Regulatory Hedge Summary |
This table stores the summary of hedged portfolio set which includes an effective and ineffective portion of gain and loss, hedged notion amount, IRC used, and so on. |
You must complete the following setup configuration settings before executing the AGILE RP EBA Run. There are certain configuration values that you must enter in the following setup table, which is used for certain aspects of the report generation.
Enter the data in the FSI_REGREPORTING_PARAM table and complete the setup for the reporting value of the parameters mentioned in the following table:
Report |
V_REG_REPORTING_PARAM |
V_REG_REPORTING_PARAM_VAL |
Requirement |
|---|---|---|---|
C09 |
CRGB_THRESHOLDLIMIT |
Either Y or N |
The threshold condition referred to in Article 5 (a) (4) is met if 10% or more of a firm's original exposures are in "non-domestic" countries. If the threshold is met, the firm is required to submit a "Total" sheet as well as a sheet per country. If the threshold is not met, the firm is now only required to submit a "Total" sheet. Therefore, based on the situation the client is expected to either give Y or N. |
F19 |
FINREP_REPORTNG_DATE |
Date in 'DD-MON-YYYY'. For example: 30-JUN-2019 |
The reporting date for which the FINREP reporting is currently being processed has to be given so that performing exposures, which exited from NPE in the last 12 months, can be calculated (C050 of the report). |
C02 |
RES_HIGHER_RW |
Percentage. For e.g. if its 150% then 1.5 should be given |
R242 - Due to stricter prudential requirement of Art. 124, if for IRB exposures belonging to Residential immovable property a higher risk weight has been used, the percentage above which it would become eligible for this row has to be given. |
C02 |
COM_HIGHER_RW |
Percentage. For e.g. if its 150% then 1.5 should be given |
R242 - Due to stricter prudential requirement of Art. 124, if for IRB exposures belonging to Commercial immovable property a higher risk weight has been used, the percentage above which it would become eligible for this row has to be given. |
C34.1 |
EXP_APP_METHOD_FLG |
Either Y or N |
Institutions that exceed the thresholds to use a simplified approach for counter-party credit risk but which are still using one of them on the basis of Article 273a(4) CRR indicates (with Yes/No) whether they meet all the conditions of that article. |
C40 |
CB_EXP_EXCL_DATE_FLG |
Either Y or N |
The daily average total value of the institution's exposures to its central bank, calculated over the full reserve maintenance period of the central bank just preceding the date referred to in point (c) of Article 429a(5) CRR, that are eligible to be excluded in accordance with point (n) of Article 429a(1) CRR. The institutions, which get the approval for such exclusions, indicates as Y for the quarter end reporting date. |
C48 |
LR_REPORTNG_DATE |
01-JAN-1000 |
Reporting date has to be given in mentioned format, which is used to calculate a 90-day average for SFT exposures. |
C34.01 |
CCR_REPORTING_DATE |
01-JAN-1000 |
Reporting date must be given in the specified format. |
C34.01 |
EXP_APP_METHOD_FLG |
Y |
Institutions that exceed the thresholds to use a simplified approach for counterparty credit risk, but which are still using one of them on the basis of Article 273a(4) CRR, shall indicate (with Yes/No) whether they meet all the conditions of that article. |
Enter the data in the FSI_SETUP_ASSETCLASS_APPROACH table and complete the setup for the reporting value of the parameters mentioned in the following table:
Report |
Column |
Requirement |
|---|---|---|
C07 |
FIC_MIS_DATE |
Reporting Date |
C07 |
V_JURISDICTION_CODE |
EBA |
C07 |
V_BASEL_ASSET_CLASS_CODE |
The asset class for which the permanent partial standardized approach or sequential IRB approach has been used. The asset class codes are referred from DIM_BASEL_ASSET_CLASS table |
C07 |
V_APPROACH |
The approach for which the permanent partial standardized approach or sequential IRB approach has been used. The methodology codes are referred from DIM_BASEL_METHODOLOGY table |
C07 |
F_PERMANENT_PARTIAL_STD_APP |
Y/N |
C07 |
F_SEQUENTIAL_IRB_APPROACH |
Y/N |
This section provides information about Basel Processing to EBA Results Integration in the Oracle Financial Services Data Foundation application and step-by-step instructions to use this section.
Topics:
· Overview of Basel Processing to EBA Results Integration Tables
· Overview of Basel Processing to EBA Results Integration
· Executing the BASEL Processing to EBA Results Integration T2Ts
· BASEL Processing to EBA Results Integration Results T2Ts
As part of Basel processing to EBA results integration, EBA tables are loaded from Basel Processing tables using Table to Table (T2T) component of Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) framework. Following are the Results Tables that stores integrated results:
· FCT_REG_CP_CAPITAL_SUMMARY
· FCT_REG_CAP_ACCOUNT_SUMMARY
· FCT_REG_ACCT_MITIGANT_MAPPING
· FCT_MITIGANT_REG_CAPITAL
· FCT_REG_SEC_POOL_SUMMARY
· FCT_REG_OR_CAPITAL_SUMMARY
· FCT_REG_MARKET_RISK_EXPOSURES
· FCT_REG_LE_CAPITAL_SUMMARY
· FCT_REG_LARGE_EXP_CP_LIMITS
· FCT_REG_CAP_POOL_SUMMARY
· FCT_REG_CAP_PLCD_COLL_SUMMARY
· FCT_REG_CAP_CREDIT_LINE_SUMMRY
· FCT_REG_CAP_ASSET_SOLD_SUMMARY
· FCT_MR_VAR_SUMMARY
· FCT_MR_VAR_PORTFOLIO_SUMMARY
· FCT_MR_CAPITAL_SUMMARY
· FCT_FORECAST_REG_CAP_SUMMARY
· FCT_MARKET_RISK_REPORTING
· FCT_REG_POOL_MITIGANT_MAP
· FCT_REG_CAP_ACCOUNT_SUMMARY
· FCT_MR_CAPITAL_SUMMARY
· FCT_REG_ACCT_MITIGANT_MAPPING
· FCT_MITIGANT_REG_CAPITAL
· FCT_REG_POOL_MITIGANT_MAP
· FCT_MARKET_RISK_REPORTING
As part of Basel processing results to EBA integration, EBA is packaging the aforementioned T2Ts. These are optional T2Ts that are deployed only when OFS_CAP_ADQ_PACK is installed.
Table-to-Table seeded definitions are provided for loading data into the target tables:
Sl. No. |
Source Table Name |
Target Table Name |
T2T Definition Name |
|---|---|---|---|
1 |
FCT_CCP_DETAILS |
FCT_REG_CP_CAPITAL_SUMMARY |
T2T_FRCCS_FCT_CCP_DETAILS |
2 |
FSI_CAP_SFT_EXPOSURES |
FCT_REG_CAP_ACCOUNT_SUMMARY |
T2T_FRCAS_FSI_CAP_SFT_EXPOSURES |
3 |
FSI_CAP_INVESTMENT_EXPOSURES |
FCT_REG_CAP_ACCOUNT_SUMMARY |
T2T_FRCAS_FSI_CAP_INVESTMENT_EXPOSURES |
4 |
FSI_CAP_DERIVATIVES |
FCT_REG_CAP_ACCOUNT_SUMMARY |
T2T_FRCAS_FSI_CAP_DERIVATIVES |
5 |
FSI_CAP_BANKING_EXPOSURES |
FCT_REG_CAP_ACCOUNT_SUMMARY |
T2T_FRCAS_FSI_CAP_BANKING_EXPOSURES |
6 |
FSI_CAP_EXP_MITIGANT_MAPPING |
FCT_REG_ACCT_MITIGANT_MAPPING |
T2T_FRAMM_NET_POOL_EXP_MITIGANT_MAP |
7 |
FSI_CAP_SUB_EXPOSURES |
FCT_REG_ACCT_MITIGANT_MAPPING |
T2T_FRAMM_FSI_CAP_SUB_EXPOSURES |
8 |
FSI_CAP_MITIGANTS |
FCT_MITIGANT_REG_CAPITAL |
T2T_FMRC_FSI_CAP_MITIGANTS |
9 |
FCT_SECURITIZATION_POOL |
FCT_REG_SEC_POOL_SUMMARY |
T2T_FCT_REG_SEC_POOL_SUMMARY |
10 |
FCT_OPS_RISK_DATA |
FCT_REG_OR_CAPITAL_SUMMARY |
T2T_FCT_REG_OR_CAPITAL_SUMMARY |
11 |
FCT_MARKET_RISK_EXPOSURES |
FCT_REG_MARKET_RISK_EXPOSURES |
T2T_FCT_REG_MARKET_RISK_EXPOSURES |
12 |
FCT_STANDARD_ACCT_HEAD |
FCT_REG_LE_CAPITAL_SUMMARY |
T2T_FCT_REG_LE_CAPITAL_SUMMARY |
13 |
FCT_PARTY_GROUP_LARGE_EXPOSURE |
FCT_REG_LARGE_EXP_CP_LIMITS |
T2T_FCT_REG_LARGE_EXP_CP_LIMITS |
14 |
FCT_COUNTERPARTY_EXPOSURE |
FCT_REG_CP_CAPITAL_SUMMARY |
T2T_FCT_REG_CP_CAPITAL_SUMMARY |
15 |
FSI_CAP_NETTABLE_POOL |
FCT_REG_CAP_POOL_SUMMARY |
T2T_FCT_REG_CAP_POOL_SUMMARY |
16 |
FSI_PLACED_COLLATERAL |
FCT_REG_CAP_PLCD_COLL_SUMMARY |
T2T_FCT_REG_CAP_PLCD_COLL_SUMMARY |
17 |
FSI_CAP_INVESTMENT_EXPOSURES |
FCT_REG_CAP_ACCOUNT_SUMMARY |
T2T_FCT_REG_CAP_FIXED_ASST_SUMMARY |
18 |
FSI_CAP_BANKING_EXPOSURES |
FCT_REG_CAP_CREDIT_LINE_SUMMRY |
T2T_FCT_REG_CAP_CREDIT_LINE_SUMMRY |
19 |
FSI_CAP_BANKING_EXPOSURES |
FCT_REG_CAP_ASSET_SOLD_SUMMARY |
T2T_FCT_REG_CAP_ASSET_SOLD_SUMMARY |
20 |
FCT_MR_VAR_SUMMARY_DATA |
FCT_MR_VAR_SUMMARY |
T2T_FCT_MR_VAR_SUMMARY |
21 |
FCT_MR_VAR_SUMMARY_DATA |
FCT_MR_VAR_PORTFOLIO_SUMMARY |
T2T_FCT_MR_VAR_PORTFOLIO_SUMMARY |
22 |
FCT_MARKET_RISK_IR_CAPITAL |
FCT_MR_CAPITAL_SUMMARY |
T2T_FCT_MR_CAPITAL_SUMMARY_FMRIRC |
23 |
FCT_MARKET_RISK_FOREX_CAPITAL |
FCT_MR_CAPITAL_SUMMARY |
T2T_FCT_MR_CAPITAL_SUMMARY_FMRFRXC |
24 |
FCT_MARKET_RISK_EQ_CAPITAL |
FCT_MR_CAPITAL_SUMMARY |
T2T_FCT_MR_CAPITAL_SUMMARY_FMREQC |
25 |
FCT_MARKET_RISK_COM_CAPITAL |
FCT_MR_CAPITAL_SUMMARY |
T2T_FCT_MR_CAPITAL_SUMMARY_FMRCC |
26 |
FSI_FORECAST_RWA |
FCT_FORECAST_REG_CAP_SUMMARY |
T2T_FCT_FORECAST_REG_CAP_SUMMARY |
27 |
FCT_MARKET_RISK_CAPITAL |
FCT_MARKET_RISK_REPORTING |
MKT_RISK_REPORTING_POP_IR |
28 |
FSI_CAP_SUB_EXPOSURES |
FCT_REG_POOL_MITIGANT_MAP |
T2T_FRPMM_FSI_CAP_SUB_EXPOSURES |
29 |
FCT_NON_SEC_EXPOSURES |
FSI_CAP_EXPOSURES_POST_CRM |
T2T_FSI_CAP_EXPOSURES_POST_CRM |
30 |
FCT_NON_SEC_EXPOSURES |
FCT_REG_CAP_ACCOUNT_SUMMARY |
T2T_FMRC_UNCOV_FSI_CAP_SUB_EXPOSURES |
31 |
FCT_MARKET_RISK_EXPOSURES |
FCT_REG_CAP_ACCOUNT_SUMMARY |
T2T_FSI_CAP_ACCT_ASSET_CLASS_FCT_REG_CAP_ASSET_CLASS |
For Basel - EBA integration, you must have EBA and Basel installed on the same INFODOM. Also, you must ensure that EBA and Basel are running the same version.
There are two ways to integrate Basel and EBA:
1. Creating Integrated Run at Implementation Site: During implementation, you can merge the tasks of both BASEL and EBA and create an integrated Run to execute each time. The processes inside Run should be ordered as Basel first, then EBA, and finally the Basel - EBA Integration process. In this Run, the Basel processing area and the EBA results area tables must have the same Run SKEY across all tables.
For BASEL - EBA Integration Run, please use the Process Execution Summary screen as the Request Report Flag, Override Report Flag, and Approve Report Flag options are not available in the Basel Run Management Screen to enable the Reporting Flag.
2. Using approved Basel Run Execution ID in EBA Run: In this case, you can use the ready-to-use Basel Run as-is for execution. After the execution, if the values are correct, you can execute the ready-to-use EBA Run by selecting the required Basel Run SKEY from the Run Execution Parameter screen. In this case, the Basel processing area has one RUN SKEY and for the same data, EBA has a different RUN SKEY in EBA results area tables, where the data is getting reported. Sample report generation is as follows:
a. Log in to Oracle Financial Services Analytical Applications interface with your credentials.
b. Navigate to Regulatory Reporting for European Banking Authority, select Operations, and then select Process Modeller.
c. Select a Run and click Execute Run.
d. The Run Details and Run Execution Parameters window is displayed.
e. Enter the Run Name and Run Execution Description. The Basel Run Execution Identifier and FIC MIS Date is auto-populated from the Basel Run report used.
f. Click OK.
Resave Hierarchy HFSDF004 (EBA - Basel Run Execution Identifier for Run) after Basel execution for getting values in this Basel Run Execution Identifier.
Figure 54: Basel Metadata Resave Screen
3. Select only one Basel Run from the Available Hierarchies for the execution and click Save. The Run Management Summary window is displayed.
T2T definitions can be retrieved as an excel document for reference from the metadata browser of the Unified Metadata Manager (UMM) component of OFSAAI.
This section provides information about LRM Processing to EBA Results Integration in the Oracle Financial Services Data Foundation application and step-by-step instructions to use this section.
Topics:
· Overview of LRM Processing to EBA Results Integration Tables
· Overview of LRM Processing to EBA Results Integration
· Executing the LRM Processing to EBA Results Integration T2Ts
· LRM Processing to EBA Results Integration Results T2Ts
As part of LRM processing to EBA results integration, EBA tables are loaded from LRM Processing tables using Table to Table (T2T) component of Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) framework. Following are the Results Tables that stores integrated results:
· FCT_DEP_INS_ACCT_PARTY_DETL
· FCT_DEP_INS_BENEFICIARY_DTL
As part of LRM processing results to EBA integration, EBA is packaging the aforementioned T2Ts. These are optional T2Ts that are deployed only when OFS_TR_PACK is installed.
Table-to-Table seeded definitions are provided for loading data into the target tables.
Sl. No. |
Source Table Name |
Target Table Name |
T2T Definition Name |
|---|---|---|---|
1 |
FCT_DEPOSIT_INSURANCE_DETAILS |
FCT_DEP_INS_ACCT_PARTY_DETL |
T2T_FCT_DEP_INS_ACCT_PARTY_DETL |
2 |
FCT_DEPOSIT_INSURANCE_DETAILS |
FCT_DEP_INS_BENEFICIARY_DTL |
T2T_FCT_DEP_INS_BENEFICIARY_DTL |
For LRM - EBA integration, you must have EBA and LRM installed on the same INFODOM. There are two ways to integrate LRM and EBA:
1. Creating Integrated Run at Implementation Site: During implementation, you can merge the tasks of both LRM and EBA and create an integrated Run to execute each time. The processes inside Run should be ordered as LRM first, then EBA, and finally the LRM - EBA Integration process. In this Run, the LRM processing area and the FSDF results from area tables must have the same Run SKEY across all tables.
For LRM - EBA Integration Run, please use the Process Execution Summary screen as the Request Report Flag, Override Report Flag, and Approve Report Flag options are not available in the LRM Run Management Screen to enable the Reporting Flag.
2. Using approved LRM Run Execution ID in EBA Run: In this case, you can use the ready-to-use LRM Run as-is for execution. After the execution, if the values are correct, you can execute the ready-to-use EBA Run by selecting the required LRM Run SKEY from the Run Execution Parameters screen. In this case, the LRM processing area has one RUN SKEY and for the same data, EBA has a different RUN SKEY in FSDF results area tables, where the data is getting reported. Sample report generation is as follows:
a. Log in to Oracle Financial Services Analytical Applications interface with your credentials.
b. Navigate to Regulatory Reporting for European Banking Authority, select Operations, and then select Process Modeller.
c. Select a Run and click Execute Run.
d. The Run Details and Run Execution Parameters window is displayed.
e. Enter the Run Name and Run Execution Description. The LRM Run Execution Identifier and FIC MIS Date is auto-populated from the LRM Run report used.
f. Click OK.
Resave Hierarchy HFSDF006 (EBA - LRM Run Execution Identifier for Run) after LRM execution for getting values in this LRM Run Execution Identifier.
Figure 55: LRM Metadata Resave Screen
3. Select only one LRM Run from the Available Hierarchies for the execution and click Save. The Run Management Summary window is displayed.
T2T definitions can be retrieved as an excel document for reference from the metadata browser of the Unified Metadata Manager (UMM) component of OFSAAI.
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 AGILE RP UI
· Viewing Data Elements Summary
After the applications are installed and configured, to access the OFS AGILE RP 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 AGILE RP UI, follow these steps:
1. Enter the OFSAAI URL in your browser. The OFSAAI login page is displayed.
Figure 56: 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 57: OFSAA Applications Screen
4. Select the Financial Services Data Foundation. The FSDF landing page is displayed.
Figure 58: Financial Services Data Foundation Landing Page
5. Or select the Regulatory Reporting for European Banking Authority. The Regulatory Reporting for European Banking Authority landing page is displayed.
Figure 59: Regulatory Reporting for European Banking Authority Page
6. Select the
Navigation Menu 
in the
OFS AGILE RP UI to access the following windows:
a. Home
b. Data Elements
c. Metadata Management
i. Dataset
ii. Build Hierarchy
iii. Measure
iv. Business Processor
v. Derived Entity
vi. Reports
d. Metadata Browser
e. Process
i. Process Modeller
ii. Process Monitor
f. Process Execution 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 AGILE RP UI, navigate to Metadata Management and select Reports to view the Reports Summary window.
Figure 60: 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 on the search box. You can search for a Report using either the name or description.
Figure 61: 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 62: Report Summary Paging Option
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 63: Report in Tile View
Figure 64: Report in List View
Select the Report Code to navigate to the Schedule Summary window.
Figure 65: Report Information
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).
For example, the Schedule Summary window for the CRSA report is displayed as follows.
Figure 66: 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 on the search box. You can search for a Schedule using either the name or description.
The Paging option (Figure 67) 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.
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 67: Schedule 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.
Figure 68: 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 69: Data Elements Screen
2. Select Filter to apply filters on the selected data. The filter pane allows filtering data on specific columns.
Figure 70: Filters
3. Select Apply Filter to apply the required filters on the selected data.
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.
The following are the steps to perform to view the data elements summary.
1. After logging into OFS AGILE RP UI, from the main navigation menu select Data Elements to view all the Data Elements.
Figure 71: Data Elements Summary
By default, the page displays all the data elements.
Figure 72: Selection Panel
2. Click a row and the selection panel displays the selected entity and attribute.
Figure 73: Selected Entity
The tabs on the right can be used to view reports, schedules, and cells as shown in Figure 73, which are utilized for the selected data element.
Figure 74: Report or Schedule or Cell View
NOTE:
For Viewing Data Elements and Viewing Data Elements Summary, Data Elements batch execution is required for the screen to function.
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 75).
For example, the Cells for Schedule Page3 summary window under the CRSA report is displayed as follows.
Figure 75: 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 on the search box. You can search for a Cell using either the name or description.
The Paging option (Figure 75) 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:
· Measure
· Filters
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 76: Cell Information
The Cell Information window is displayed as follows.
Figure 77: Cell Information Window
Each section in the Cell Information window displays the relevant OFSAA Metadata and filters used for the cell.
This displays the name of the OFSAA Materialized View or View that contributes to the Cell.
This displays the name of the OFSAA Measure that is reported for the particular Cell.
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 78: Multiple Filter Values
The filters in case of not in condition are highlighted in red are displayed as follows.
Figure 79: Not in Condition Filters
Data Schedule based reports utilize wrapper views to report data. For Adjustments & for addition on newer granularity not provided by OFSAA solutions for data schedule-based reports, this feature allows mapping new derived entity columns to the corresponding wrapper view columns. The topics in this section are taken as an example and organized as follows:
· Navigating to Mapping Window
· Saving Mapping Configuration
The prerequisites for Data Schedule Mapping are as follows:
· All Derived Entities and the Wrapper Views should be resaved through resave batch pages and by the execution of scripts packaged as Post Scripts with installer respectively.
· Execute the batch <INFODOM>_DS_POP_UNION_METADATA_EBA available in the batch execution page post the step above.
Select the Navigation Menu in
the Regulatory Reporting for European Banking Authority home page to navigate
to the Report Summary window. Navigate to the data schedule-based report
for which mappings are to be done.
For example: to map schedules under the CRSA report, select the CRSA report.
Figure 80: Report Information
Select the report code (Figure 81) to navigate to the schedules. All schedules under the report are available in this window.
Figure 81: Schedules Information
Schedules for which mapping feature is available can be
clearly distinguished by the Edit 
icon
available in the schedule tile. Schedules for which the feature is not
available do not have the edit icon present in the corresponding tile.
Select Edit to navigate to the
mapping window.
The Mapping window displays the wrapper view utilized for the data schedule and the contributing OFSAA derived entities to the wrapper view. The window also displays the line items of the data schedule-based report along with the internal derived entity columns mapped to it.
Figure 82: Mapping Window
The Mapping window components are as follows.
· Schedule Name
The Schedule Name is displayed on the top left corner of the window.
· Wrapper View
The Wrapper view utilized for the schedule is mentioned with square brackets in the top pane along with the schedule name.
· Contributing Derived Entities
The left section of the report lists down the OFSAA derived Entities that contribute to the Wrapper View. The list contains derived entities that are by default provided by the OFSAA solution and the ones added by the user.
Figure 83: Derived Entities
· Mapping Table
The mapping table shows all contributing components to the line item of the data schedule. The columns of the mapping table in Table 13.
Table Column |
Description |
|---|---|
Column Name |
This defines the line item of the data schedule for which mapping is to be done. |
Data Type |
This column defines the data type of the line item as per OFS AGILE RP EBA instructions. |
Internal Entity |
This column defines the contributing derived entity. |
Child Column |
This column defines the derived entity metadata which maps to the line item of the data schedule. |
To add a new derived entity, follow these steps:
1. Select Add Derived Entity.
Figure 84: Add Derived Entity
2. This lists the available Derived Entities that are present in the current infodom. The Derived Entities can be searched by either code or name in the search box.
Figure 85: Derived Entities List
3. Select the desired Derived Entity that must be added for adjustments and click Add.
Figure 86: Selected Derived Entity
NOTE:
The same Derived Entity cannot be added twice for Data Schedule mapping.
4. On adding the new Derived Entity, the mapping window is displayed as follows.
Figure 87: Mapping Window with New Derived Entity
5. Derived entity
added through the above method can be distinguished from OFSAA based derived
entities through a Remove 
present
at the end of the derived entity tab. This mark enables the removal of
the derived entity. Derived Entities that are from the OFSAA provided
granularities do not have the Remove mark
and thus mapping for such derived entities cannot be removed or modified
from this window.
The Mapping window for any added derived entity allows mapping columns of the derived entity to the line item of the data schedule.
For example, the mapping window for Derived Entity DE - Securities AFS OCI by Portfolio [DEREG008] is displayed as follows.
Figure 88: Data Schedule Mapping Window
The mapping of the line item to the derived entity column can be modified by double-clicking on the respective row in the Metadata column of the mapping table.
When the row is clicked, all the columns of the Derived Entity are listed and can be selected to map that to the corresponding line item listed under the Column Name column of the table. If no mapping is required, then select the No Mapping Needed option.
Example for Derived Entity DE - Securities AFS OCI by Portfolio [DEREG008] is displayed as follows.
Figure 89: Metadata Mapping
NOTE:
Ensure that the data type of the selected metadata matches the column data type.
After the mapping is complete as described in earlier sections, select Save Configuration at the bottom of the window to save the configuration. The following message is displayed after the configuration is saved.
Figure 90: Saving Mapping Configuration
Click Close.
The adjustments feature is a new enhancement to adjust the differing values of the report systems. The Adjustments Derived Entity derives its values from the Adjustments Fact table (FCT_REG_REPORT_ADJUSTMENTS) that specifies the adjustment value and the seeded table (DIM_REG_REPORT_CELL) that specifies the Cell ID or MDRM Code and the Report Code to which the MDRM belongs to. This ensures that there can be direct adjustments made to MDRM(s) such that the values from both the derived entities are traceable and efficiently reported.
Topics:
· Implementing the Adjustment Feature
To implement the Adjustment feature, identify the Cell ID for the report and the line item where adjustment must be implemented.
For example: OPRS Report
070 Line Item: On balance sheet exposures subject to credit risk
Cell ID: OPRSR030C030
NOTE:
The Adjustment feature works only for fixed table cells (Open Y cells are not supported).
The report currently displays a Total value = 3,33,161 for the identified cell as shown in the following figure.
Figure 91: Adjustment Feature
Now, the requirement is to adjust this amount to 15,00,000+3,33,161=18,33,161
Figure 92: Drill down for Total Value
For example, with the page instance, identify the Cell ID for the report and the line item where adjustment must be implemented.
For example: CRSA Report
070 Line Item: On balance sheet exposures subject to credit risk
Cell ID: CRSAR070C030
NOTE:
The Adjustment feature works only for fixed table cells (Open Y cells are not supported).
The drill-down will always show the total page instance value but not the individual page instance.
The report currently displays a Total value = 2,06,24,54,006 for the identified cell as shown in the following figure.
Figure 93: Adjustment Feature for Page Instance
Now, the requirement is to adjust this amount to 2,06,24,54,006
Navigate to the drill-down of the adjusted amount for the page instance.
Figure 94: Adjustment Feature for Page Instance Value
This drill-down page will always show the total page instance value as shown in the following figure.
Figure 95: Drill down for Total Page Instance Value
Apart from the adjusted amount of 5000000000, the drill-down value from the total page instance is 684.9M.
Figure 96: Adjustment Feature for Total Page Instance
Topics:
· Refreshing Adjustment Derived Entity
FCT_REG_REPORT_ADJUSTMENTS: This table must be populated with the requisite Adjustment Amount and other related columns.
For example:
N_ADJUSTED_AMT = 5000000000
The corresponding N_CELL_SKEY value must be picked from DIM_REG_REPORT_CELL for the respective CELL_ID. The DIM_REG_REPORT_CELL table is pre-seeded with cell IDs for reports supported for this feature.
The following columns must also be updated accordingly:
1. N_ENTITY_SKEY
2. N_RUN_SKEY
3. N_MIS_DATE_SKEY
4. V_PAGE_INSTANCE_VALUE
NOTE:
V_PAGE_INSTANCE_VALUE shall be populated with appropriate instance value for reports with page instance filters. The page instance filter value shall match the values as populated in the DE hierarchy for page instance. The adjustment value shall be provided for all the page instance values that require adjustment including the �Total� instance. This attribute can be ignored for the reports without page instance.
Execute the resave batch for Adjustments (<<INFODOM>>_REG_ADJUSTMENT_RESAVE), to save the Adjustment derived entity - DEADJ001.
This ensures that the adjustment amount is reflected in the adjustment derived entity DEADJ001.
Post adjustments, the retrieved report should reflect the amount that is coming from the sourced systems and the adjusted amount.
The retrieved report should reflect the amount after adjustments as shown in the following figure.
(2,06,24,54,006 +5000000000) = 5002062.454006
Figure 97: Lombard Adjustment Verification
NOTE:
The Adjustment amount can be negative to achieve a subtracted amount.
This product feature allows line items for data schedule-based reports to be directly mapped to data sourced from various systems that are not captured through OFSAA regular granularities (for example, Portfolio granularity). The Direct Upload option involves using wrapper views and shadow derived entities for managing data from regular granularities and non-OFSAA granularities to be exposed together to the Lombard Agile Reporter.
Figure 98: Data Schedules Direct Upload
Topics:
· Setting up Shadow Derived Entity
· Defining Shadow Derived Entity
· Executing View Creation Batch
The initial step to enable data schedule involves setting up a shadow derived entity which holds data from sources that are not provided by OFSAA regular granularities.
The shadow derived entity and all the underlying objects which include the Datasets, Hierarchies, Measures, and Business Processors must be defined from the OFSAA UI page under the Financial Services Data Foundation, select Unified Analytical Metadata and then select Business Metadata.
See OFS Analytical Applications Infrastructure User Guide for more details.
NOTE:
For populating the shadow derived entity cases where a new table is introduced which are not already a part of the OFSAA data model, ensure that the following conditions are met:
1. The primary key of the shadow table is the same as the granularity of the data required for the data schedule.
2. Data is expected to be mutually exclusive between OFSAA results and the shadow table.
3. Customer to load data into shadow tables through ETL or DIH.
4. Run Identifier, MIS Date, and Entity Identifier must be mandatory attributes and part of the primary key.
5. This table can be created by extending the OFSAA data model followed by executing the source model generation to enable table visibility in the OFSAA framework.
Mapping of the shadow derived entity to the line items can be achieved by using the user interface described in Section 4.5: Data Schedule Mapping.
Post mapping columns for direct upload through the steps mentioned in the previous section, the view must be recreated in the database to reflect the shadow derived entity as a part of its definition.
This can be achieved by executing <INFODOM>_DS_RESAVE_UNION_VIEW_EBA batch from the batch execution page to save the view definition.
The resave batch is a sample batch for view resaves which can be utilized for the concerned view by replacing the sample view name with the desired view name under the batch maintenance page. After the changes are saved, the batch can be executed from the batch execution page.
This should modify the view definition to include the new shadow derived entity given all metadata mapped through the page has the same data type as the parent metadata.
NOTE:
If the metadata type required for the line item and as identified by the wrapper view does not match that of the shadow derived entity, the view recreation fails. The errors are logged in the ERR_LOG_UNION_VIEW_PARSER table in the atomic schema.
After the batch is successfully executed, use any SQL tool to verify that the view is dependent on the derived entity added to the configuration. This can be verified from the USER_DEPENDENCIES table by using the following query.
Select REFERENCED_NAME from User Dependencies Where NAME='<<VIEWNAME>>'
where the VIEWNAME specifies the wrapper view for which mapping was done.
This section details the migration of Data Schedule mapping across environments.
Topics:
· Steps for Source Environment
· Steps for Destination Environment
The following tables must be backed up in the source and target environments before the migration is performed:
· FSI_DS_CHILD_COL_MAP
· FSI_DS_INT_CHILD_INFO
· FSI_DS_SEEDED_VW_INFO
· FSI_DS_VW_CHILD_MAP
· FSI_DS_VW_COL_INFO
· FSI_DS_VW_COL_MAP
User-defined Derived Entity (Entities) created for data schedule mapping must be migrated through the Object Migration feature of OFSAA (OFS Advanced Analytical Applications Infrastructure Application Pack 8.0.8.0.0 User Guide).
The assumptions considered before the migration is performed are as follows:
· OFSAA objects (for example, determine derived entities) required for the data schedule mapping are present in the destination environment.
· Migration overwrites already existing configuration in the destination schema with the one from the source schema.
· The migration steps stated in the following sections for Data Schedule Mapping is performed for one view at a time.
Execute the following script files to migrate in the Source Environment:
1. VW_FSI_DE_MIGRATION_UNION_DE.sql
3. FSI_DE_MIGRATION_UNION_INSERT.sql (bypassing the union view name and jurisdiction code in the same sequence)
NOTE:
Information for the parameters to be passed in the step above for a particular schedule and report can be obtained from FSI_DS_REPORT_VIEW_MAP.
4. Generate insert scripts from the FSI_DE_MIGRATION_UNION table (say FSI_DE_MIGRATION_UNION_SOURCE_EXPORT.sql) in the source environment, which can be used to populate the same table in the destination environment.
Execute the following script files to migrate in the Destination Environment:
1. VW_FSI_DE_MIGRATION_UNION_DE.sql
3. FSI_DE_MIGRATION_UNION_SOURCE_EXPORT.sql (the insert script generated from the source schema)
4. MIGRATION_POPULATION_TABLES.sql (by replacing parameters P_JURISDICTION and P_UNION_VIEW with the Jurisdiction Code and Union View Name respectively).
Figure 99 explains the flow of data between OFSAA and AgileREPORTER.
Figure 99: 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 100: 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.
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 as forms or returns) for various jurisdictions. AgileREPORTER provides a reliable and efficient infrastructure to compile, generate, and submit regulatory reports.