Exporting data from multi-instance SCP to Data Lake

The schema prefix application variable is now also used when planning data is exported from SCV to Data Lake. The data is exported into the Data Lake table with the SCP_<Schema Prefix>_<SCV Table Name> format.

When the Data Lake schema is shared by multiple SCP instances, the data is exported from different SCV instances to the same Data Lake table.The data exported from each SCV instance must not be overlapping as the primary keys of the tables are also shared. Unless there is a strict functional split across multiple instances, the same plan value can exist in multiple SCV instances and exporting that plan value to Data Lake cause inconsistent results when querying the same data using Data Lake Compass.

The scp_instance column is introduced in the schema published to ION Data Catalog to guarantee that the data exported from multiple SCV instances to Data Lake is not overlapped.

During data export to Data Lake, the scp_instance column is automatically populated by the internal instance ID. When the data exported from the main instance in a multi-instance SCP, or when there is a single instance, then the scp_instance column is populated with Null value. This guarantees that the data exported from single instance SCP remain unchanged. The column is populated with the instance ID in all other instances in a multi-instance SCP.

The column is added with the first schema published after the upgrade. Hence, existing data in the Data Lake does not have the column in the source data documents. When a primary-key column is missing in the documents, the records from those documents are not retrieved by Data Lake Compass using the default settings. The data is however still available in Data Lake and can be queried by using the infor.AllVariations('objectname') table function. Exporting the same data to Data Lake once more after the upgrade populates the new PK column and make the data visible to Compass.

Note: By default, this feature is enabled. You are not required any new role or privilege access to use this feature.

SC_ITEM table is now extended by default. This table contains one column to hold the base unit for items.

  • SC_ITEM.base_uom: Holds the imported base unit value for the item.
  • SC_EXT_ITEM.xs_base_uom: Holds the manually entered base unit value for the item.
  • SC_V_EXT_ITEM.base_uom: Mapped to SC_ITEM.base_uom; holds the imported value.
  • SC_V_EXT_ITEM.xs_base_uom: Mapped to SC_EXT_ITEM.xs_base_uom; holds the manually entered value.
  • SC_V_EXT_ITEM.res_base_uom: Mapped to SC_EXT_ITEM.xs_base_uom or SC_ITEM.base_uom; returns the manually entered value if it is available, else, the imported value.