RUN defines the purpose of data execution. Few examples are Solvency Run, Regulatory Reporting Run and so on.
In DIM RUN table, Run Surrogate Key (Run Skey) is the primary attribute in DIM RUN Table. This is to identify a particular set of data, which is defined by combination of RUN ID and Execution Date for every execution or RUN EXECUTION IDENTIFIER.
· In each of the FCT table, data is characterized by combination of RUN SKEY, Business Type, Legal Entity and scenario code as appropriate. Details of the same are given below.
§ Run Skey: is a Surrogate key for a particular processed data set. This is a numeric field and is referenced to the table DIM_RUN that contains the list of all such run surrogate keys. This table is required to be updated with the N_Run_Skey each time a report for any new instance is to be generated assuming other paramrters constant. Multiple executions on same FIC MIS DATE will have different run skey.
§ FIC_MIS_DATE/Extraction Date/Reporting Date: is a common date identifier for the records across all the tables for which you want to generate the report.
§ Business Type: can be Life, Non Life or Composite. This is not part of DIM RUN table, but all FCT tables have these as an important identifier.
§ Entity: is the list of legal entities for the institute is provided in the table DIM_ORG_STRUCTURE.
§ Scenario: the Various Scenarios used by entity for testing uncertain future developments. List of Scenarios needs to be stored in DIM SCENARIO table.
· In DIM RUN, F REPORTING FLAG should be marked 'Y' for all the run executions which will be used for reporting. If there are multiple executions for the same RUN ID and FIC MIS DATE, the reporting flag should be used to highlight one specific execution for reporting. It is assumed that single RUN will contain all the information required. Multiple scenarios and multiple FIC MIS DATE for a single RUN ID is assumed to provide the required flexibility.
· Scenario and Baseline data should be stored in the same run and are differentiated using scenario code in the fact table.
· For each Run execution, FCT_LEGAL_ENTITY_GROUP_DETAILS will have details of Legal Entity. If the run executed is SOLO execution then there will be one row entry in the stated table and lead entity and legal entity will have same entries. If the run executed is COMPOSITE execution then there will be 'n' rows entry in the stated table (where n = number of entities involved in the run), lead entity attribute will have entry of the Group entity and legal entity attribute will have entries of all the child entities to the group entity. Parent child relationship of the entities will be available in DIM_ORG_STRUCTURE.
· Executions can be done at two different levels, composite as well as for one business segment entity. For each type of the execution composite and single business segment data should be separately calculated, solo executions will not be consolidated for composite execution.
For the list of staging area tables and attributes that can be used for QMR, refer the Oracle_Insurance_Solvency_II_QMR_Interface_-_DL_Specs_4 spreadsheet.