Hierarchy Deactivation

Beginning in version 23, foundation dimension ad hoc loads have been changed to use Type 1 slowly-changing dimension (SCD) behavior, which means that the system will no longer create new records every time a parent/child relationship changes. Instead, it will perform a simple merge on top of existing data to maintain as-is hierarchy definitions. The foundation data model holds hierarchy records separately from product data, so it is also necessary to perform maintenance on hierarchies to maintain a single active set of records that should be propagated downstream to other RAP applications. This maintenance is performed using the program W_PROD_CAT_DH_CLOSE_JOB in the RI_DIM_INITIAL_ADHOC process. The program will detect unused hierarchy nodes which have no children after the latest data has been loaded into W_PROD_CAT_DH and it will close them (set to CURRENT_FLG=N). This is required because, in the data model, each hierarchy level is stored as a separate record, even if that level is not being used by any products on other tables. Without the cleanup activity, unused hierarchy levels would accumulate in W_PROD_CAT_DH and be available in AI Foundation, which is generally not desired.

There are some scenarios where you may want to disable this program. For example, if you know the hierarchy is going to change significantly over a period of time and you don’t want levels to be closed and re-created every time a new file is loaded, you must disable W_PROD_CAT_DH_CLOSE_JOB. You can re-enable it later and it will close any unused levels that remain after all your changes are processed. Also be aware that the program is part of the nightly batch process too, so once you switch from historical to nightly loads, this job will be enabled and will close unused hierarchy levels unless you intentionally disable it. This job must be disabled if you are using RDE programs to load Merchandising data.