SUPPORT / SAMPLES & SAS NOTES
 

Support

Problem Note 51212: PROMOTION_FACT and PROMOTION_FACT_FUTURE partition tables are missing indexes and might be empty

DetailsDownloadsHotfixAboutRate It

Following installation of hot fix D81024 for SAS® Merchandise Intelligence 4.2 M1, one or more of the following issues might exist after an incremental promotion fact data load using the Load_STG2DM_Promotion_fact job:

  • Lost data in the PROMOTION_FACT_FUTURE folder partition tables.
  • Significantly less data in the PROMOTION_FACT folder partition tables.
  • Missing indexes on the PROMOTION_FACT and PROMOTION_FACT_FUTURE partition tables.
  • Estimation fails with the following error:
    ERROR: No Index found PLIB.PARTITION_214035 using columns PROD_HIER_SK GEO_HIER_SK

To work around this issue, perform the following steps:

  1. Restore the PROMOTION_FACT partition tables to a known good state (prior to the first incremental load using the Load_STG2DM_Promotion_fact job following D81024 installation). You do not need to restore the PROMOTION_FACT_FUTURE partition tables because they can be built from the PROMOTION_FACT partition tables.
  2. Modify the job parameter etlp_price_hist_weeks_keep from 0 to 52 or greater in the JOB_PARAM table. This value should be based on the period for which you review Promotion KPIs.
  3. Run the Load_STG2DM_Promotion_fact job with the source data from the incremental that caused the issue. After running this job, you will still see incorrect indexes and lost data in the PROMOTION_FACT_FUTURE partition tables, but you should see significantly more data in the PROMOTION_FACT partition tables.
  4. Download rebuild_promo_fact_futr.sas and post_process_promo_fact_f1mi42.sas from the Downloads tab.
  5. Modify the following line in the rebuild_promo_fact_futr.sas file to reflect the correct saved file location:
    %include "<add the correct path to file here>/post_process_promo_fact_f1mi42.sas";
  6. Run the post-process code, rebuild_promo_fact_futr.sas, which calls the post_process_promo_fact_f1mi42.sas code. After running this code, the PROMOTION_FACT_FUTURE partition tables should contain data and the indexes should be correct on both sets of partition tables. For cross-reference, the indexes are sourced from the empty PROMOTION_FACT and PROMOTION_FACT_FUTURE tables in your DI_DM library.
  7. Include the post-process code in your job stream until a fix for this issue is provided.

Click the Hot Fix tab in this note to access the hot fix for this issue.



Operating System and Release Information

Product FamilyProductSystemProduct ReleaseSAS Release
ReportedFixed*ReportedFixed*
SAS SystemSAS Retail ForecastingMicrosoft Windows Server 2003 Datacenter Edition4.2_M19.2 TS2M3
Microsoft Windows Server 2003 Enterprise Edition4.2_M19.2 TS2M3
Microsoft Windows Server 2003 Standard Edition4.2_M19.2 TS2M3
Microsoft Windows Server 2003 for x644.2_M19.2 TS2M3
Microsoft Windows Server 20084.2_M19.2 TS2M3
Microsoft Windows Server 2008 for x644.2_M19.2 TS2M3
64-bit Enabled AIX4.2_M19.2 TS2M3
SAS SystemSAS Regular Price OptimizationMicrosoft Windows Server 2003 Datacenter Edition4.2_M15.29.2 TS2M39.3 TS1M1
Microsoft Windows Server 2003 Enterprise Edition4.2_M15.29.2 TS2M39.3 TS1M1
Microsoft Windows Server 2003 Standard Edition4.2_M15.29.2 TS2M39.3 TS1M1
Microsoft Windows Server 2003 for x644.2_M15.29.2 TS2M39.3 TS1M1
Microsoft Windows Server 20084.2_M15.29.2 TS2M39.3 TS1M1
Microsoft Windows Server 2008 for x644.2_M15.29.2 TS2M39.3 TS1M1
64-bit Enabled AIX4.2_M15.29.2 TS2M39.3 TS1M1
SAS SystemSAS Promotion OptimizationMicrosoft Windows Server 2003 Datacenter Edition4.2_M15.29.2 TS2M39.3 TS1M1
Microsoft Windows Server 2003 Enterprise Edition4.2_M15.29.2 TS2M39.3 TS1M1
Microsoft Windows Server 2003 Standard Edition4.2_M15.29.2 TS2M39.3 TS1M1
Microsoft Windows Server 2003 for x644.2_M15.29.2 TS2M39.3 TS1M1
Microsoft Windows Server 20084.2_M15.29.2 TS2M39.3 TS1M1
Microsoft Windows Server 2008 for x644.2_M15.29.2 TS2M39.3 TS1M1
64-bit Enabled AIX4.2_M15.29.2 TS2M39.3 TS1M1
* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.