Warehouse Performance Inbound Interface

This page describes the Warehouse Performance object, the streams and rules it uses, and the logic designed for its streams, including:

Overview

The purpose of the Warehouse Performance Inbound interface is to upload Total, Segment-level and Index Constituent Performance returns into the Eagle data warehouse from external sources. 
It assumes that performance has been calculated and data is committed to the database in order to establish the classification structure that reflects the model of the index or fund being loaded (e.g. Country/Sector).  This structure is defined by the grouping rule of the Performance Calculation report and it is the PACE commit process that ensures the structure is written to the PACE dictionary and dictionary_detail tables.  The configuration of the performance model must exist in order for data to be loaded.
Warehouse Performance inbound interface loads and updates data in the following database tables:

• PERFORMDBO.PERF_SUMMARY

• PERFORMDBO.PERF_SEC_RETURNS

You can use pipe-delimited and EagleML-formatted files as incoming data.

Stream Logic

EagleML Processing Sequence

Warehouse Performance is loaded into DB via eagle_ml-2-0_default_in_xml_warehouse_preproc in two steps:

• XSLT transformation. XSLT rule 'eagle_default/in/xml/xslt/wrh_egl_gen_interface3.xsl' is used.

• Loading data into the database. The 'eagle_default/in/xml/xml-warehouse_v2.xml' rule is used.

To load or update data in Warehouse Performance database tables send an EagleML message into the eagle_ml-2-0_default_in_xml_warehouse_preproc stream.

CSV Processing Sequence

Warehouse Performance is loaded to DB with the following streams:

• eagle_default_in_csv_warehouse  - this is PRF stream which consist of two steps transform and load

• eagle_default_in_csv_warehouse_t - transformation CSV to EagleML

  • _performance.xml​ {_}rule

• eagle_ml-2-0_default_in_xml_warehouse_preproc

  • XSLT transformation. XSLT rule 'eagle_default/in/xml/xslt/wrh_egl_gen_interface3.xsl' is used.

  • Loading data into the database. The 'eagle_default/in/xml/xml-warehouse_v2.xml' rule is used

To load or update data in Warehouse Performance tables, send your CSV message to the eagle_default_in_csv_warehouse stream.

Pipe-delimited Processing Sequence

Warehouse Performance is loaded to database with the following streams:

• eagle_default_in_pipe_warehouseCLP  - this is PRF stream which consist of two steps transform and load

• ​eagle_default_in_pipe_warehouseCLP_t - transformation PIPE to XMLDBAPI

  • ​pipe-wrhs_performance_xmldbapi.xml rule

• eagle_ml-2-0_default_in_xml_warehouse – loading data into the database. In this step the rule eagle_default/in/xml/xml-dbdirect_invar.xml ​is used.

To load or update data in Warehouse Performance tables, send your pipe-delimited message to the eagle_default_in_pipe_warehouseCLP stream.

Required Elements

The following elements are required in the EagleML message: Effective Date, Entity Id (or Entity Identifiers), Source Name, Lot Number, identifiers to resolve Security Alias.

• Effective Date: warehouseTransaction/warehousePerformance/effectiveDate 

• Entity Id: warehouseTransaction/warehousePerformance/entityId (or warehouseTransaction/warehousePerformance/xrefAccountId and warehouseTransaction/warehousePerformance/xrefAccountIdType) – mapped to PERFORMDBO.PERF_SUMMARY.ENTITY_ID

• Source Name: warehouseTransaction/warehousePerformance/sourceName​

• Security Identifiers to resolve the Security Alias (see the section 'Security Resolution Logic'), Security Alias is mapped to PERFORMDBO.PERF_SEC_RETURNS.SECURITY_ALIAS

Security Resolution Logic

Constituent level records will need to resolve to the SECURITY_ALIAS found in the security.xreference table. Records at the 'Total' or 'Segment' level are loaded to the performance table with a security alias of '0'. To resolve security alias the procedure will do the following:

The element Security Alias should not be specified in the incoming EagleML message. Please refer to Security Resolution for Warehouse Objects

Entity Resolution Logic

There are two ways to define Entity Id for Warehouse objects:
1. Refer to Entity Resolution for Warehouse Objects.
2. Providing EntityXrefIdType and EntityXrefId in the Batch Key in the incoming message.

The procedure will resolve the record to the proper EntityID based on the value provided in EntityXrefIdType(BatchKey - Field #2). If the incoming value in EntityXrefIdType is NULL, then the procedure will get entity_id from the EntityId of the incoming record (BatchKey - Field #1).  If the incoming value in EntityXrefIdType is not NULL then entity_id will be resolved from table RULESDBO.ENTITY_XREFERENCE where xref_account_id is the value from incoming record EntityId (BatchKey - Field #1) and xref_account_id_type is the value from the incoming record EntityXrefIdType (BatchKey - Field #2).

BatchKey

To facilitate the identification of a unique batch within the file, the procedure will use BatchKey as the unique batch parameter. The BatchKey is the concatenation of

EntityXrefId^EntityXrefIdType^EndEffectiveDate^SourceName^FrequencyCode^DictionaryName^PerfSummaryType (if you use Entity Xref for EntityId resolution) or 

EntityId^^EndEffectiveDate^SourceName^FrequencyCode^DictionaryName^PerfSummaryType (if you use direct EntityId value.)

For example:  ENTVALUE^^20130713^EAGLE PACE^D^DICTIONARY1^S

Constituent Insertion Logic

All Constituent records (Security_alias != "0") are loaded to the perform..perf_summary and Perform..Perf_Sec_Returns tables with the following values:

• Perf_summary.Dictionary_id = "0"

• Perf_summary.Perf_summary_type = "S"

• Perf_Sec_Returns.Perf_rollup_returns_id = "0"

The procedure will then move to the Perf Sec Rollup Relation  Section. 

Dictionary ID Resolution Logic

A performance dictionary (model) must be established in the target environment prior to any data load.  If one does not exist, please contact your Eagle representative to review the alternatives for establishing a performance dictionary.In order to load the returns to the appropriate performance model, the procedure will perform a lookup in the rules..dictionaries table and select dictionary_id where dictionary_name is equal to DictionaryName (BatchKey - Field #6)

• If the result is Null, the procedure will generate an error with the message: "Invalid Dictionary/Model Name."

• If the result is NOT NULL, the procedure will consider the result to be the dictionary_id variable.

Dictionary Item ID Resolution

Severity can take the following values: F, W.

Once the dictionary_id is resolved, the Dictionary Node within the appropriate dictionary level must be resolved. This is also referred to as the Dictionary Item ID. If a dictionary Node is not found, the procedure will be permitted to add that node only if the severity on the edit 'Dictionary item not found' is set to 'W' (which stands for warning).  If the severity level is set to 'F' (fatal), this will result in an error condition as documented below. Constituent/Security returns (Security_alias != 0) are stored in the Perform.Perf_Sec_Returns table with a perf_rollup_returns_id of '0'. The procedure will not have the ability to establish new levels within a dictionary, only nodes within established levels. 

The steps to resolve the Dictionary Item ID are as follows:

1. The procedure will evaluate variables (dictLevel2… dictLevel8) in the incoming record to determine which is the first column having a NULL value:

   1. If none of the variables are null, the incoming record is assigned DICTIONARY_LEVEL_CODE of 8.

   2. If a column other than DL1 (not input from file) is found to be NULL, the procedure will check whether any subsequent columns in the subset are NOT NULL.

      1. If any subsequent columns are NOT NULL, an error will be given that 'Invalid Dictionary Levels Found'.

      2. If no subsequent columns in the subset are NULL, the procedure will assign DICTIONARY_LEVEL_CODE based on the first column that was found to be NULL in accord with the table below.

2. The procedure will perform a lookup in the rulesdbo.dictionary table selecting Dictionary_item_id where the following characteristics are met:

   • Dictionary_id = Dictionary_id

   • DICT_L1_CODE_VALUE = DL1

   • DICT_L2_CODE_VALUE = DL 2

   • DICT_L3_CODE_VALUE = DL 3

   • DICT_L4_CODE_VALUE = DL 4

   • DICT_L5_CODE_VALUE = DL 5

   • DICT_L6_CODE_VALUE = DL 6

   • DICT_L7_CODE_VALUE = DL 7

   • DICT_L8_CODE_VALUE = DL 8

3. If the result is NOT NULL, the procedure will consider the variable to be DICTIONARY_ITEM_ID and store the record in accord with the data map. 

4. If the result is NULL, the procedure will check the severity level of the edit 'Dictionary Item Not Found.'

   1. If the edit is not found or if the result is anything other than 'F' or 'W' the procedure will give an error that 'Dictionary Item ID unclear'.

   2. If the result is 'F', the procedure will fail the record with the error 'Dictionary Item not found.

   3. If the result is 'W', the procedure will count the number of records in the rulesdbo.dictionary_format table having the following attributes:

      • Dictionary_id = DICTIONARY_ID

      • Dictionary_level = DICTIONARY_LEVEL_CODE

1. If the count is zero, an error will be given that 'Invalid Dictionary levels Found'.

2. If the count is greater than zero, the procedure will give the warning 'Dictionary Item not Found AND perform a series of checks to determine whether the higher-level nodes exist.  As illustrated in the table below, this iterative check requires the lower level values to be considered NULL. The DL1 Code does not require a check as there can be only one node with one value at this level.

   1. If any of the higher level nodes are not found in the dictionary_detail table, they will be inserted along with the node for the incoming record in accord with the table below

Additional Logic

• If the incoming record does not have a startDate (BEGIN EFFECTIVE DATE) the FrequencyCode(BatchKey - Field #5)  will be used to determine the begin_effective_date using the business calendar for the 'Eagle PACE' source. The logic is as follows:

  • If FrequencyCode(BatchKey - Field #5)  = 'D', the begin_effective_date will be the prior business date relative to EndEffectiveDate (BatchKey - Field #3)

  • If FrequencyCode(BatchKey - Field #5)  = 'M', the begin_effective_date will be the last day of the prior month relative to END EndEffectiveDate (BatchKey - Field #3)

  • If FrequencyCode(BatchKey - Field #5)  = 'Q', the begin_effective_date will be the last day of the prior quarter relative to EndEffectiveDate (BatchKey - Field #3)

  • If FrequencyCode(BatchKey - Field #5)  = 'Y', the begin_effective_date will be the last day of the prior year relative to EndEffectiveDate (BatchKey - Field #3)

• Update the business calendar to reflect the appropriate day, month, quarter or year-end flag (set the flag to 1 only) for the date corresponding to EndEffectiveDate (BatchKey - Field #3) if the flag is missing on the business calendar row for the source data being loaded.

• Records resolved to the constituent/security level (Security_alias != 0) need to have additional processing logic. That logic is outlined in the Perf Sec Rollup Relation Instance and Perf Sec Universe Instance logic below. 

Perf Sec Rollup Relation Instance

• Perf_sec_rollup_relation stores the relationship of a security to the rollup level that the security belongs to in a specific performance model by effective date.

• The INSTANCE is defined as the unique combination of EntityId (BatchKey - Field #1), EndEffectiveDate (BatchKey - Field #3), Dictionary_id resolved from the DictionaryName (BatchKey - Field #6), security_alias resolved from XrefIdentifiers and dictionary_item_id(Perf Rollup Returns ID) resolved from(dictLevel2… dictLevel8). If the interface finds that records exist for the same data as is found for the incoming record for EntityId, and Dictionary_id resolved from DictionaryName, then the procedure will do the following:

1. Retrieve the most recent record corresponding to the incoming record.  This is the record where Perf_sec_rollup_relation.Entity_id = EntityId on 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 <= EndEffectiveDate on Incoming record 

2. If Perf_rollup_returns_id on record retrieved in Step 1 is equal to Dictionary_item_id resolved from(dictLevel2… dictLevel8)., then the perf_sec_rollup_relation table will not be updated.

3. If Perf_rollup_returns_id on record retrieved in Step 1 is NOT equal to Dictionary_item_id resolved from(dictLevel2… dictLevel8), then the perf_sec_rollup_relation table will be updated.  If EndEffectiveDate = Effective_date on retrieved record in Step1, then this record will be updated according to the data map below.  If EndEffectiveDate > Effective_date on retrieved record in Step1, then a new record will be inserted to the Perf_sec_rollup_relation table according to the data map below.

Perf Sec Universe Instance 

• Perf_sec_universe stores the universe of all securities that have been held by an entity.

• The INSTANCE is defined as the unique combination of EntityId (BatchKey - Field #1), and Security Alias resolved from XrefIdentifiers

• 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.

1. The procedure will perform a lookup in the Perf_sec_universe table using the entity_id and security_alias.

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

   2. If a match is not found, the procedure will insert a new record according to the data map

PACE Derived Fields