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:
· Overview of OFS REG REP APME User Interface
· Adjustment Feature for Template-based Reports
· Mapping of Results to Line Items in Reporting
This section explains the input data preparation from OFSAA.
Topics:
· Assumptions for Data Preparation
· Reclassification of Regulatory Dimensions
· Configuring Setup Tables for Standard Set of Values
· Run or Execution Expectations
· 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
· Inclusion of GL Recon Reconciled Accounts in Reporting
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 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 REG REP data sourcing.
3. Baseline and stress data must be populated with appropriate codes. Inaccurate mappings 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/year), only one set of data is expected to be stored.
Oracle Financial Services Regulatory Reporting Data Sets and Governance for Asia Pacific and Middle East Jurisdictions Pack provides the APME (APRA/MAS/RBI) RUN Chart listing the tasks required for the population of data for APME Reports. This covers the following tasks:
· Setup Table Population
· Stage Dimension Load
· Seeded Dimension Data Population
· Common Data Population
· Common Tasks like Exchange Rate Population
· APRA Specific Data Population and Transformation
· Derived Entity Refresh
Download the 8.1.1.0.0 RUN Chart for APME (APRA/MAS/RBI) from the MOS.
This section provides information about Regulatory Dimension Tables in the Regulatory Reporting for Australian Prudential Regulation Authority (OFS REG REP APME) application and step-by-step instructions to use this section.
This section includes the following topics:
· Overview of Reclassification of Regulatory Dimensions
· Overview of Reclassification of Regulatory Dimensions Population
· Dimension Data Expectations through SCD
· Overview of Mappers for Reclassification of Regulatory Dimensions
· Maintenance of Mappers for Reclassification of Regulatory Dimensions
· Loading Mapper Maintenance through Backend
· Usage of Mapper Tables in Data Flow and Reports
Topics:
· Overview of Reclassification of Regulatory Dimensions
· Dimension Data Expectations through SCD
· Overview of Mappers for Reclassification of Regulatory Dimensions
· Maintenance of Mappers for Reclassification of Regulatory Dimensions
· Loading Mapper Maintenance through Backend
· Usage of Mapper Tables in Data Flow and Reports
There are certain Regulatory Dimensions in OFS REG REP APME, which are pre-populated with a standard set of values. These values are used by downstream applications for various reporting requirements. There are equivalent customer-specific dimension tables that are populated using a Slowly Changing Dimension (SCD) process. It is required to reclassify these user-specific values to standard / regulatory specific values as the reporting expects these standard set of values. The reclassification is done using out of the box Mapper Definitions under the Mapper Maintenance screen.
By default, all standard dimensions are pre-populated with seeded data. It is mandatory to have data in user-specific dimensions and then maintain the reclassifications. Therefore, you must execute the SCDs and then map the reclassification codes under Mapper Maintenance.
These are out of the box mappers that are available in Oracle Financial Services Data Foundation (OFSDF) for the regulatory dimension reclassifications:
· MAP_GL_CODE_REP_LINE: Mapper for GL Code to Reply Code
The mapper can be maintained under OFSAAI.
1. Login to OFSAA, navigate to Oracle Financial Services Regulatory Reporting Data Sets and Governance for Asia Pacific and Middle East Jurisdictions, select Metadata Management and then select Map Maintenance.
Figure 160: Map Maintenance Page
2. For illustration, we have selected Mapper for GL Code to Repline Code. Click Mapper Maintenance.
Figure 161: Mapper Maintenance Page
3. OFS REG REP APME 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 162:Mapper Maintenance Search Result Page
Prerequisites for Mapper Maintenance
1. Login to OFSAA, navigate to Oracle Financial Services Regulatory Reporting Data Sets and Governance for Asia Pacific and Middle East Jurisdictions, select Metadata Management and then select Map Maintenance. 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
Figure 163: 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 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 mappings.
· 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. Login to OFSAA, navigate to Oracle Financial Services Regulatory Reporting Data Sets and Governance for Asia Pacific and Middle East Jurisdictions, select Metadata Management and then select Map Maintenance.
Figure 164: Map Maintenance Page
2. Select an existing Map. For illustration, Mapper for GL Code to Repline Code value is selected. Select the Mapper Maintenance icon.
3. The Mapper Maintenance window opens (in this illustration, the Map - Mapper for GL Code to Repline Code window opens). To conduct One to One or Many to One mapping, in the Member Combinations section, click Add.
Figure 165: Map Maintenance Add page
4. The Add Mappings pop-up window opens. In this illustration:
§ To map One to One: select one value each in General Ledger Code for Mgmt Reporting data model, Debit Credit Indicator for Mgmt Reporting data model, GL Rollup Signage for Mgmt Reporting data model, and one value in Reporting Line Code for Mgmt Reporting data model, and click Go. Repeat this step for each One to One data model mapping, and then click Save.
In this illustration, 200001MAP1 - 200001MAP1 is mapped to C - Credit, N - Negative Multiplier, and 1001 - Redeemable Cumulative Preference Shares.
Figure 166: One to One Mapping window
§ To map Many to One: select more than one value each in General Ledger Code for Mgmt Reporting data model, one or more value in Debit Credit Indicator for Mgmt Reporting data model, GL Rollup Signage for Mgmt Reporting data model, Reporting Line Code for Mgmt Reporting data model, and click Go. Repeat this step for each Many to One data model mapping, and then click Save.
§ In this illustration:
§ 200001MAP1 - 200001MAP1 is mapped to C - Credit and D - Debit, N - Negative Multiplier and P - Positive Multiplier, 1 - Total Assets, and 1001 - Redeemable Cumulative Preference Shares and 1002 - Redeemable Non-Cumulative Preference Shares.
§ 200001MAP2 - 200001MAP2 is mapped to C - Credit and D - Debit, N - Negative Multiplier and P - Positive Multiplier, 1 - Total Assets, and 1001 - Redeemable Cumulative Preference Shares and 1002 - Redeemable Non-Cumulative Preference Shares
Figure 167: One to Many Mapping window
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.
Load each Physical table in Atomic Schema with V_MAP_ID as mentioned against each mapper,
V_MEMBER_1 => Customer Specific Value Dimension's Member Code, V_MEMBER_2 => Standard Dimensions Member Code.
This following Mapper Physical Table is required details:
PHYSICAL TABLE |
V_MAP_ID |
|---|---|
MAP_GL_CODE_REP_LINE |
1494610765133 |
The mapper maintenance output is always physically stored in underlying tables. These tables are registered in OFSAA as an object. Therefore, these tables can be used, without any restrictions, in any of the metadata that requires reclassification. OFS REG REP APME Data Flows (T2Ts and Rules) make use of this information to populate the Standard Dimension Surrogate Keys of Results area tables.
The following are the setup configurations that are required to be done before executing the APME Regulatory Reporting Run.
Topics:
The SETUP_MASTER table in an atomic schema must be modified with the required values for APME.
V_COMPONENT_ CODE |
V_COMPONENT_ DESC |
V_COMPONENT_VALUE |
Description |
|---|---|---|---|
DEFAULT_FINANCIAL_ELEMENT |
Default Financial Element |
DEFAULT |
Component Value to be updated according to the values used in STG_GL_DATA.V_FINANCIAL_ELEMENT_CODE. This is used for Fact Management Reporting T2T. |
DEFAULT_FX_RATE_SRC |
Default FX Rate Source |
DEFAULT |
Component Value to be updated according to the values used in STG_FORWARD_EXCHG_RATES.V_RATE_DATA_SOURCE_CD. This is used for Calculating the Reporting Currency. |
DEFAULT_GAAP |
DEFAULT_GAAP |
Same as mentioned in the description |
AUGAAP for APRA, SGGAAP for MAS and INGAAP for RBI. |
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, RRDF application requires data 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
The following points provide information on the projection data:
1. Baseline run also populates projected date data.
2. This application requires projected data at two levels - Quarterly and Annual.
3. The DIM_CONSOLIDATION table is used to identify the projections. It contains the codes for projected quarters and years as required by the templates.
4. In the Fact tables, projection data is referred to with the respective Consolidation codes (scenario code for FCT_MGMT_REPORTING). BHC must populate the data accordingly.
5. In the following example, FQ1 means Financial Quarter 1, FY1 means Financial Year 1 and so on.
Consolidation Code |
Consolidation Description |
Reporting Line |
Scenario |
EOP Balance |
|---|---|---|---|---|
100 |
Actual |
100 |
BSL |
426,367 |
400 |
FQ1 |
100 |
BSL |
608,618 |
401 |
FQ2 |
100 |
BSL |
870,502 |
402 |
FQ3 |
100 |
BSL |
567,736 |
403 |
FQ4 |
100 |
BSL |
846,196 |
404 |
FQ5 |
100 |
BSL |
775,027 |
410 |
FY1 |
100 |
BSL |
470,092 |
411 |
FY2 |
100 |
BSL |
473,880 |
412 |
FY3 |
100 |
BSL |
942,034 |
413 |
FY4 |
100 |
BSL |
497,889 |
414 |
FY5 |
100 |
BSL |
807,813 |
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 need not provide this difference as a download. Movement data for actual is identified through different runs and 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), REG REP sums up data points across all 90 days to arrive at a quarter-end movement figure.
Code |
Projected Period |
Reporting Line |
Scenario |
Run ID |
Date |
Projected Amount |
Movement |
|---|---|---|---|---|---|---|---|
100 |
Actual |
100 |
BSL |
RUNID001 |
10-Oct-13 |
300,000 |
900,000 |
100 |
Actual |
100 |
BSL |
RUNID002 |
15-Nov-13 |
100,000 |
|
100 |
Actual |
100 |
BSL |
RUNID003 |
20-Nov-13 |
300,000 |
|
100 |
Actual |
100 |
BSL |
RUNID004 |
30-Dec-13 |
200,000 |
|
400 |
FQ1 |
100 |
BSL |
-- |
-- |
-- |
608,618 |
401 |
FQ2 |
100 |
BSL |
-- |
-- |
-- |
870,503 |
402 |
FQ3 |
100 |
BSL |
-- |
-- |
-- |
567,736 |
410 |
FY1 |
100 |
BSL |
-- |
-- |
-- |
470,093 |
411 |
FY2 |
100 |
BSL |
-- |
-- |
-- |
473,881 |
412 |
FY3 |
100 |
BSL |
-- |
-- |
-- |
942,035 |
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(s) 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 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:
· 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/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.
After transformation, the regulatory data is reclassified as follows:
Source |
Target |
|---|---|
DIM PARTY TYPE |
DIM REG PARTY CATEGORY |
High Net Worth Individual |
Individual |
Individual |
Individual |
Retail |
Individual |
Household |
Household |
The sample reclassifications performed to transform the existing hierarchies to regulatory specific hierarchies are:
· Party Category Classification
· Product Category Classification
· Interest Type Classification
· Intra Group Indicator
· Regulatory Loan Purpose
The additional transformations that are performed are:
· Original Maturity Band
· Residual Maturity Band
· Delinquency Band
See Business Metadata for details of 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 consist of 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/schemas within the processing area.
The processing area tables/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 (see Section 5.1.11). 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 FTP Account Summary and FACT REG FTP Account Summary
which stores account level measures computed by FTP application.
FACT FTP Account Summary table needs to be populated manually if OFS FTP
application is not available.
APRA provides a PMF Run that can be executed to populate FACT REG FTP Account
Summary from FACT FTP Account Summary.
For more information, see APRA RUN CHART.
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 below options to maintain consistency in terms of data lineage in Metadata browser as the configured metadata can be made available in the meta-model via MDB publish:
· Data Integration Hub (DIH) Connectors
· Data Mapping (T2T) option in Application Infrastructure
· 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 168: 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 Oracle Financial Services Advanced Analytical Applications Infrastructure Application Pack 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 Oracle Financial Services Advanced Analytical Applications Infrastructure Application Pack User Guide on OHC 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.
Sl. No. |
List of Dimension Tables |
Table/Entity Logical Names |
Table/Entity Descriptions |
Table/ Entity Type |
|---|---|---|---|---|
1 |
DIM_ACCOUNT_PURPOSE |
Account Purpose Dimension |
This table stores the purpose for which the bank has initiated the account. |
SCD |
2 |
DIM_BANDS |
Bands Dimension |
This table stores the list of band dimensions. Information on the table name, columns containing the band codes, upper and lower bound values are stored in the setup table and a generic code is executed to populate the band codes in the respective fact tables. |
Seeded |
3 |
DIM_BOOLEAN_FLAGS |
Boolean Flag Dimension |
This table stores the list of the Boolean Flags. |
Seeded |
4 |
DIM_CONSOLIDATION |
Consolidation Dimension |
This entity stores the details of various values to be analyzed like actual or budget. |
Seeded |
5 |
DIM_COUNTRY |
Country Dimension |
This table stores the master list of countries. |
Seeded |
6 |
DIM_CREDIT_PARTCPTION_TYPE |
Credit Participation Contract Type Dimension |
This table stores the type of the contract identifiers for the main participation or syndication contract. |
Seeded |
7 |
DIM_CURRENCY |
Currency Dimension |
The table stores the currency information. ISO currency codes is a standard published by the International Organization for Standardization 4217, which delineates currency designators and country codes (alpha and numeric). |
Seeded |
8 |
DIM_CUSTOMER |
Customer Dimension |
This entity stores the list of the organization's customers and counterparties and their attributes. |
SCD |
9 |
DIM_DATES |
Date Dimension |
This table stores the list of dates generated between any two dates typically covering extraction dates and cash flow dates. |
Seeded |
10 |
DIM_INDICATOR_VALUES |
Indicator Values Dimension |
This table stores the indicator values used in various columns for identifying the Boolean or indicator values. This is a seeded dimension table from OFSAA products. |
Seeded |
11 |
DIM_INSTRUMENT_CONTRACT |
Instruments Contracts Dimension |
This entity stores the contracts and instruments in the Market and their details like Effective Date, Maturity Date, Face Value, Day Convention, Strike, and so on. |
Seeded |
12 |
DIM_INTEREST_TYPE |
Interest Type Dimension |
This table stores the interest type. |
Seeded |
13 |
DIM_LOCATION |
Location Dimension |
This table stores the location dimension. |
SCD |
14 |
DIM_ORG_STRUCTURE |
Organization Structure Dimension |
This entity stores the Organization Structure of the Financial Institution. |
SCD |
15 |
DIM_PARTY |
Party Dimension |
This table stores the history of the party. Party here can be customer, issuer, guarantor, and so on. |
SCD |
16 |
DIM_PARTY_TYPE |
Party Type Dimension |
This table stores the party type. Party here could be Individual, Banks, Corporate - Small, Corporate - Medium, State Government, Sovereign, and so on. |
SCD |
17 |
DIM_PRODUCT |
Product Dimension |
This table stores the details of all the products (existing/stopped) offered by the Financial Institution. |
SCD |
18 |
DIM_PRODUCT_TYPE |
Product Type Dimension |
This table stores the loan product type information. |
SCD |
19 |
DIM_REG_INTEREST_TYPE |
Regulatory Interest Type Dimension |
This table stores the list of indices that are designed to store the regulatory based interest type code as designated by the regulator for an account at the account level or group of accounts at a credit line level. For example FIXED, FLOATING, MIXED, and so on. |
Seeded |
20 |
DIM_REG_LOAN_PURPOSE |
Regulatory Loan Purpose Dimension |
This
table stores the description for the regulatory loan purpose/utilization
of the loan amount. Values expected are: |
Seeded |
21 |
DIM_REG_PARTY_CATEGORY |
Regulatory Party Category Dimension |
This entity stores the reclassified regulatory party categories. |
Seeded |
22 |
DIM_REG_PRODUCT_CATEGORY |
Regulatory Product Category Dimension |
This entity stores the reclassified regulatory product categories. |
Seeded |
23 |
DIM_REG_REPORT_CELL |
Regulatory Reporting Cell Dimension |
This table stores the cell IDs / MDRM codes as provided by the AgileREPORTER templates. |
Seeded |
24 |
DIM_REP_LINE |
Reporting Line Dimension |
This table stores the list of all computed reporting line items. |
Seeded |
25 |
DIM_RUN |
Run Dimension |
The entity stores the baseline and simulation runs. |
|
26 |
DIM_STD_MITIGANT_TYPE |
Standard Mitigant Type Dimension |
This entity stores the standard mitigant type. |
SCD |
For all tables with data flow type tagged as a Processing, it is recommended that you map data directly to the result area if 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
Sl. No. |
List of Fact Tables |
Table/Entity Logical Names |
Table/Entity Descriptions |
Table/ Entity Type |
|---|---|---|---|---|
1 |
FCT_ACCOUNT_MITIGANT_MAP |
Fact Account Mitigant Map |
This entity stores the account to mitigant mapping. It supports more than one mitigant to be mapped to an account. |
FACT |
2 |
FCT_ACCT_PLACED_COLL_MAP |
Fact Account Placed Collateral Map |
This table stores the account to placed collateral mapping. It is an intersection table to denote that a placed collateral can be used in multiple accounts and an account contains multiple collateral. |
FACT |
3 |
FCT_COMMON_ACCOUNT_SUMMARY |
Fact Common Account Summary |
This table stores the common account level information that usually comes as an input through staging. |
FACT |
4 |
FCT_IFRS_ACCOUNT_SUMMARY |
Fact IFRS Account Summary |
This table stores the measures related to an account that are computed by IFRS application. |
FACT |
5 |
FCT_MGMT_REPORTING |
Fact Management Reporting |
This table stores the management reporting data related to organization and product profitability/income statement/balance sheet. |
FACT |
6 |
FCT_MITIGANTS |
Fact Mitigants |
This entity stores the mitigants and their details. |
FACT |
7 |
FCT_PLACED_COLLATERAL |
Fact Placed Collateral |
This table stores the details of collateral that are placed against an account. |
FACT |
8 |
FCT_REG_ACCOUNT_SUMMARY |
Regulatory Account Summary |
This table stores the regulatory reclassifications and other information as required for regulatory reporting. |
FACT |
9 |
FCT_REG_REPORT_ADJUSTMENTS |
Fact Regulatory Report Adjustments |
This table stores the adjusted amount against a particular cell ID / MDRM code for a regulatory report. |
FACT |
10 |
FCT_REG_RUN_LEGAL_ENTITY_MAP |
Fact Regulatory Legal Entity Run Map |
This table stores a reporting entity identifier for every regulatory reporting run. |
FACT |
11 |
FCT_REG_CAP_ACCOUNT_SUMMARY |
Fact Regulatory capital account Summary |
This table stores the processed data for capital adequacy reporting. |
FACT |
12 |
FCT_MARKET_RISK_REPORTING |
Fact Market Risk Capital |
This table stores the capital available for market risk |
FACT |
13 |
FCT_REG_LE_CAPITAL_SUMMARY |
Fact Regulatory Legal Entity Capital Summary |
This table stores the regulatory capital related information for the legal entity. This table stores all information from the GL related to the capital structure processing as well as the various levels of capital computations processed and computed by the application. |
FACT |
14 |
FCT_REG_CAP_PLCD_COLL_SUMMARY |
Fact Regulatory Capital Placed Collateral Summary |
This table stores the information of all exposures to a bank which are placed collateral. The placed collateral are collateral placed by the bank for either default fund contribution or for other OTC transactions, with a central counterparty. It is generally used for Cleared transactions and Default fund contributions |
FACT |
15 |
FCT_MR_CAPITAL_SUMMARY |
Fact Market Risk Capital Summary
|
This table stores the information of the market risk capital calculations at a portfolio level. |
FACT |
16 |
FCT_REG_CP_CAPITAL_SUMMARY
|
Fact Market Risk Capital Summary |
This table stores the information of the market risk capital calculations at a portfolio level. |
FACT |
17 |
FCT_REG_MARKET_RISK_EXPOSURES
|
Fact Regulatory Market Risk Exposures |
This table stores Basel Processing output for Market Risk Exposures for Regulatory Reporting |
FACT |
By default, the Regulatory Reporting expects reconciliation data in the staging area for all the reports. For OFS Data Management (OFSDM) pack (OFS General Ledger Reconciliation Application (GL Recon)) installed in the same Infodom as Regulatory Reporting is installed, the results area tables will have accounts with account numbers (having prefixes defined in REVELEUS_PARAMETER_MASTER.V_PARAM_VALUE column for the REVELEUS_PARAMETER_MASTER.V_PARAM_CODE = 'ADJUSTMENT_EXP_PREFIX' used in GL Recon application).
Report-specific treatment for such accounts is handled in Regulatory Reporting application for cases like a number of accounts that must be reported.
For example: ARF7200A - Section A and Section B.
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 APME UI
· Viewing Data Elements Summary
· Viewing the Pre and Post Adjusted Data
After the applications are installed and configured, to access the OFS REG REP APME 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 APME UI, follow these steps:
1. Enter the OFSAAI URL in your browser. The OFSAAI login page is displayed.
Figure 169: 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 170: OFSAA Applications Screen
4. Select the Financial Services Data Foundation. The FSDF landing page is displayed.
Figure 171: Financial Services Data Foundation Landing Page
5. Or select the Oracle Financial Services Regulatory Reporting Data Sets and Governance for Asia Pacific and Middle East Jurisdictions. The Oracle Financial Services Regulatory Reporting Data Sets and Governance for Asia Pacific and Middle East Jurisdictions landing page is displayed.
Figure 172: Oracle Financial Services Regulatory Reporting Data Sets and Governance for Asia Pacific and Middle East Jurisdictions Page
6. Select the
Navigation Menu 
in the
OFS REG REP APME UI to access the following windows:
a. Home
b. Inbox
c. Regulatory Reporting Deployment
d. Administration
i. Key Indicator Assessment Configuration
ii. Control Assessment Parameters
e. Data Elements
f. Standards and Policies
i. Business Terms
ii. Critical Data Elements
g. Metadata Management
i. Dataset
ii. Map Maintenance
iii. Build Hierarchy
iv. Measure
v. Business Processor
vi. Derived Entity
vii. Save Metadata
viii. Reports
h. Metadata Browser
i. Controls
j. Operations
i. Process Modeller
ii. Process Monitor
iii. Batch Execution
iv. Batch Monitor
k. 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 REG REP APME UI, navigate to Metadata Management and select Reports to view Reports Summary window.
Figure 173: 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 174: 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 175: 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 176: Report in Tile View
Figure 177: Report in List View
Select the Report Code to navigate to the Schedule Summary window.
Figure 178: 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 ARF7200A report is displayed as follows.
Figure 179: 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 96) 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 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 180: 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 181: 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 182: Data Elements Screen
2. Select Filter to apply filters on the selected data. The filter pane allows filtering data on specific columns.
Figure 183: 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 REG REP APME UI, from the main navigation menu select Data Elements to view all the Data Elements.
Figure 184: Data Elements Summary
By default, the page displays all the data elements.
Figure 185: Selection Panel
2. Click a row and the selection panel displays the selected entity and attribute.
Figure 186: Selected Entity
The tabs on the right can be used to view reports, schedules, and cells as shown in Figure 102, which are utilized for the selected data element.
Figure 187: 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 104).
For example, the Cells for Schedule Part7 summary window under the CRSA report is displayed as follows.
Figure 188: 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 104) 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 189: Cell Information
The Cell Information window is displayed as follows.
Figure 190: 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 191: Multiple Filter Values
The filters in case of not in condition are highlighted in red are displayed as follows.
Figure 192: Not in Condition Filters
To view the pre and post adjusted data, follow these steps:
1. From the Oracle Financial Services Regulatory Reporting Data Sets and Governance for Asia Pacific and Middle East Jurisdictions window navigate to Metadata Management and then select Reports. The Report Summary window is displayed.
2. Select the required Report Name and then select a Schedule. The Schedule Summary for the selected report is displayed.
3. Select the required Cell ID. The Cells for the selected Schedule are displayed.
4. Click 
Issue icon on the Cell Details
tile. The Issues Summary window is displayed.
Figure 193: Issue Summary Window
5. Click View Adjustment Details. The Data Adjustment Summary window is displayed. In the Actions window, the action that was created for the issue is displayed.
Figure 194: Data Adjustment Summary Window
6. Click the required Action. The adjustments that are defined for the actions are displayed.
Figure 195: Adjustments and Actions Window
7. Click the required adjustment. The pre and post adjusted data is displayed.
Figure 196: Pre and Post Adjusted Data Window
NOTE:
For the Data Adjustment - Regulatory Reporting, only the adjusted data appears.
To create a new action for the system generated Issue, follow these steps:
1. From the Oracle Financial Services Regulatory Reporting Data Sets and Governance for Asia Pacific and Middle East Jurisdictions window navigate to Metadata Management and then select Reports. The Report Summary window is displayed.
Figure 197: Report Summary Page
2. Select the required Report Name and then select a Schedule. The Schedule Summary for the selected report is displayed.
Figure 198: Schedule Summary Page
3. Select the required Cell ID. The Cells for the selected Schedule are displayed.
Figure 199: Cell Summary Page
4. Click Issue icon on the Cell Details
tile. The Issues Summary window is displayed. Here, you can edit an issue
and create an action for system-generated issues for a Cell. See section
Editing an Issue and Creating an Action for more information.
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.
To implement the Adjustment feature, identify the Cell ID for the report and the line item where adjustment must be implemented.
For example: ARF7200A v1 Report
070 Line Item: On balance sheet exposures subject to credit risk
Cell ID: BSAO27429
NOTE:
The Adjustment feature works only for fixed table cells (Open Y cells are not supported).
The report currently displays a Total value = 3.758 M for the identified cell as shown in the following figure.
Figure 200: Adjustment Feature
Now, the requirement is to adjust this amount to 2.758M+1 M=3.758 M
Figure 201: 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: ARF7200A v1 Report
2.12 ADIs Line Item: On balance sheet exposures subject to credit risk
Cell ID: BSAO27429
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 = 3,757,527 for the identified cell.
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 = 3,757,527
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
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.578 M+1 M) = 3,757,527
Figure 202: Adjustment Verification
NOTE:
The Adjustment amount can be negative to achieve a subtracted amount.
Figure 112 explains the flow of data between OFSAA and AgileREPORTER:
Figure 203: 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 204: 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 Out-of-Box / pre-configured from OFSAA. You need not perform any mapping for the reports. However, this information can be useful for maintenance or extensions when Out-of-Box 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.