Perf Sec Instances

Owned by Natalie Gesin (Deactivated)
Last updated: Feb 05, 2024 by Kinga Huszno
2 min read

Perf Sec Rollup Relation Instance

The INSTANCE is defined as the unique combination of ENTITY_ID, resolved END EFFECTIVE DATE, DICTIONARY ID, SOURCE, SECURITY_ALIAS resolved from SECURITY ID and DICTIONARY_ITEM_ID= "Perf Rollup Returns ID" resolved from Fields #5 - 11 for the incoming record.

The PERFORMDBO.PERF_SEC_ROLLUP_RELATION table stores the relationship of a security to the rollup level that the security belongs to in a specific performance model by Effective Date.

Steps used if the records exist for the same data that is found for the incoming record for ENTITY_ID and Dictionary ID:

  1. Retrieve the most recent record corresponding to the incoming record. This is the record where PERF_SEC_ROLLUP_RELATION.ENTITY_ID = ENTITY_ID on the incoming record, PERF_SEC_ROLLUP_RELATION.DICTIONARY_ID = DICTIONARY_ID on incoming record, SECURITY_ALIAS = SECURITY_ALIAS on incoming record, and PERF_SEC_ROLLUP_RELATION.EFFECTIVE_DATE = MAX(EFFECTIVE_DATE) where EFFECTIVE_DATE <= END EFFECTIVE DATE (Field #8) on incoming record

  2. If PERF_ROLLUP_RETURNS_ID on the record retrieved in Step 1 is equal to DICTIONARY_ITEM_ID on the incoming record, the PERF_SEC_ROLLUP_RELATION table will not be updated.

  3. If PERF_ROLLUP_RETURNS_ID on the record retrieved in Step 1 is NOT equal to DICTIONARY_ITEM_ID on the incoming record, the PERF_SEC_ROLLUP_RELATION table will be updated:

    • If END EFFECTIVE DATE = EFFECTIVE_DATE on the retrieved record in Step1, this record will be updated according to the Performance Data Map.

    • If END EFFECTIVE DATE > EFFECTIVE_DATE on the retrieved record in Step1, a new record will be inserted to the PERF_SEC_ROLLUP_RELATION table according to the Performance Data Map.

Perf Sec Universe Instance

The INSTANCE is defined as the unique combination of ENTITY_ID resolved and SECURITY ALIAS resolved from the SECURITY ID resolution section. The PERF_SEC_UNIVERSE table stores the universe of all securities that have been held by an entity.

Since this table stores the security universe across time, the data matching ENTITY_ID and SECURITY_ALIAS should not be replaced by the batch. Records will be inserted if the combination of ENTITY_ID and SECURITY_ALIAS does not currently exist.

The procedure will perform a lookup in the PERF_SEC_UNIVERSE table using the ENTITY_ID and SECURITY_ALIAS:

  • If a match is found, the procedure will ignore the record.

  • If a match is not found, the procedure will insert a new record according to the Performance Data Map.