My Oracle Support Banner

Project Activity And UDF Codes Missing Or Write To Wrong ID In Star Schema After ETL Run (Doc ID 2430317.1)

Last updated on OCTOBER 11, 2021

Applies to:

Primavera Analytics - Version and later
Primavera Data Warehouse - Version and later
Primavera Analytics Cloud Service - Version and later
Information in this document applies to any platform.





Multiple symptoms can be observed.

  • <UNDEFINED> appears in Analytics for all values.
  • Either the wrong value, or <NO VALUE> or <UNDEFINED> appears when multiple datasources are in-use.
  • Code values/descriptions can show as <UNDEFINED> in the staruser table(s)  (w_codes_project_d, w_codes_resource_d, w_codes_activity_d)
  • Nonsequentially Mapped Codes Or UDFS Write To Wrong ID In Data Warehouse Target Tables (W_CODES_% And W_UDF_%)
  • Project codes, activity codes, udf codes are NOT present in the Star tables (w_codes_activity_d, w_codes_project_d, w_udf_activity_d, w_udf_project_d) following an ETL run.

When mapping a code or UDF in nonsequential order, the correct ID is written to ETL_PARAMETER (holding the mapping) and W_XLAT_S (holding the generic code/udf translation name) but does write the values to the correct IDs in the following tables:

Instead, it is written to the above tables in the sequential ordering.

For example, if I have ID mappings 1,3,5 it will write the mappings to 1,2,3 in the above tables. This causes the incorrect values to be displayed within Analytics because the translation will reflect a value which does not match the core tables.

When mapping a code or UDF in non sequential order, for the values to map to the proper nonsequential columns in the following target tables:

Using three mappings as example:

  1. Connect to the star Configuration Wizard
  2. Edit the associated datasource and move to the udf or codes page
  3. Click either the codes or udfs link
  4. Move three codes from the available to selected UDFs grouping
  5. Adjust the IDs to following:
    • change ID 3 to ID 5
    • change ID 2 to ID 3
    • This will have the mappings to ID 1,3,5
  6. Complete out of the wizard once the changes are made
  7. Execute the following SQL to confirm the mappings:
    • If UDF:
        select p_feature, p_1, datasource_id
        from etl_parameter
        where p_feature like 'udf%';
    • If Code:
        select p_feature, p_1, datasource_id
        from etl_parameter
        where p_feature like 'code%';
    • Note, the p_feature column contains the correct mapping ID #
  8. Execute the ETL
  9. Note the following behavior:
    • The Core tables W_CODES_% / W_UDF_% write the mappings to the sequentially ordered numbers.
    • For example:
      • Mapping ID 3 is written to 2
      • Mapping ID 5 is written to 3




To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.