ETL Step 4: MSI

In the MSI step, the system obtains the enterprise key generated from MD_BUSINESS_KEY in the XREF step and stores it in the MSI step result tables (such as *_MSI tables). If the target entity has references to other entities, it looks up the *_XREF tables of other entities to obtain the enterprise key of the referenced entities and stores it in the <ROLE_ENTITY_NAME>_ENTERPRISE_KEY field.

The system also converts all date fields from the PPM timezone to the data warehouse timezone. If the PPM timezone is enabled with daylight saving, daylight saving is also applied to the data warehouse timezone.

Vertica for PPM content pack leverages the daylight-saving information in the Java Runtime Environment (JRE). Thus you need to update the JRE on a regular basis.

During EXT, the hash code for source entities has been generated. Because the model and data granularity has been changed in the SSI step, in the MSI step, the system regenerates the hash code for records. To verify if an SCD2 field has been updated, the system generates hash codes for all SCD1 fields and all SCD2 fields respectively.

The system also checks the *_XREF tables to understand if dummy records need to be generated for late arriving data. If the *_XREF table contains records in which the value of MD_BATCH_ID is -1, dummy records are generated.

*_SSI tables and *_XREF tables are input in this step, while *_MSI tables are output.

The following table lists the key staging tables.

Key Staging Tables

Table Name

Table Description

<SOURCE_PRODUCT_NAME>_<TARGET_ENTITY_NAME>_<MD_SOURCE_INSTANCE_ID>_MSI

Output table of the MSI step, including the enterprise key generated in the XREF step, and the dummy records for late arriving data.

The MSI table contains the following system reserved fields.

System Reserved Fields in Staging Tables

Field Name

Field Description

MD_ENTERPRISE_KEY

Unique identifier for a record. Generated base on MD_BUSINESS_KEY populated in the SSI step.

<ROLE_ENTITY_NAME>_BUSINESS_KEY

Business key of entities that will be referred to. This should be exactly the same as MD_BUSINESS_KEY of the referred entity.

<ROLE_ENTITY_NAME>_ENTERPRISE_KEY

Enterprise key of the entities that are referred to. The system automatically populates this field through looking up the *_ XREF table of the referred entity based on the value of <ROLE_ENTITY_NAME>_BUSINESS_KEY.

 

MD_HASH_CODE_SCD1

Hash code that is generated by the SCD1 columns for the dimension table.

MD_HASH_CODE_SCD2

Hash code that is generated by the SCD2 columns for the dimension table.

MD_HASH_CODE

Hash code that is generated by all columns for the fact table.