This chapter covers the following topics:
Manufacturing Operations Center enables items to be analyzed by different hierarchies. Any number of hierarchies can be built and analyzed. However, for out-of the box reporting, only three hierarchies are exposed in the OBIEE presentation layer:
Item Hierarchy 01
Item Hierarchy 02
Item Hierarchy 03
The following diagram shows the structure of item hierarchies:
The item dimension setup steps are as follows:
Load items.
Update the hierarchy master.
Load the item hierarchy.
Data items are collected as follows for each Manufacturing Operations Center plant in the source systems:
From EBS, based on the master data logic definitions.
From csv templates.
To load items from EBS:
Run one of the following process flows:
MTH_ITEMS_DIM_EBS_INIT_PF in the MTH Utilities project, under the MTHEBIIP module, for an initial data collection. This must be run for the first time since it truncates data in the MTH_ITEMS_D table.
MTH_ITEMS_DIM_EBS_INCR_PF in the MTH Utilities project, under the MTHEBICP module, for an incremental data collection
Correct errors using SQL Developer or Oracle APEX. All errors in MTH_ITEMS_ERR must be fixed and the reprocess ready flag set to Y.
Rows that are ready for reprocessing will be picked up in the next incremental run.
To load data from Microsoft Excel:
In the Microsoft Excel template, update MTH_ITEMS_MASTER.csv
In MTH Utilities, load the items into the MOC staging table by running MTH_ITEM_MASTER_XS_ALL_MAP in the OWB Mappings in the MTH Utilities project.
Load the items into the MOC Fact table by running one of the following processes:
MTH_ITEMS_DIM_EXT_INIT_PF, in the MTH Utilities project, under the MTHEXIIP module, for an incremental data collection
MTH_ITEMS_DIM_EXT_INCR_PF, in the MTH Utilities project, under the MTHEXICP module, for an incremental data collection
Correct errors using SQL Developer or Oracle APEX. All errors in MTH_ITEMS_ERR must be fixed and the reprocess ready flag set to Y.
Rows that are ready for reprocessing will be picked up in the next incremental run.
To load EBS Category Set for the seeded hierarchy, change the Hierarchy name to the EBS Category Set name.
You can build item hierarchies in Manufacturing Operations Center or import them from EBS.
To load item hierarchies from EBS:
Load the item hierarchy from EBS by running one of the following processes:
MTH_ITEM_HRCHY_EBS_INIT_PF for an initial data collection
MTH_ITEM_HRCHY_EBS_INCR_PF for an incremental data collection
Correct errors using SQL Developer or Oracle APEX. All errors in MTH_ITEM_HIERARCHY_ERR must be fixed and the reprocess ready flag set to Y.
Rows that are ready for reprocessing will be picked up in the next incremental run.
For OBIEE reporting, run the MTH_ITEM_DENORM_EBS_INIT_PF denorm process flow in the MTH project under the MTHEBIIP module for initial load and MTH_ITEM_DENORM_EBS_INCR_PF denorm process flow in the MTH project under the MTHEBICP module for incremental load.
To load item hierarchies from Microsoft Excel:
Update MTH_ITEM_DIMENSION_DENORM.csv in the Microsoft Excel template.
In the MTH Utilities, load the item hierarchy into the MOC staging table by running the MTH_ITEM_DIM_DENORM_XS_MAP in OWB Mappings.
To load item hierarchy from staging table into MTH_ITEM_HIERARCHY table, run MTH_ITEM_HRCHY_EXT_INIT_PF for the initial load and run MTH_ITEM_HRCHY_EXT_INCR_PF for the incremental load.
Correct errors using SQL Developer or Oracle APEX. All errors in MTH_ITEM_HIERARCHY_ERR must be fixed and the reprocess ready flag set to Y.
Rows that are ready for reprocessing will be picked up in the next incremental run.
For OBIEE reporting, run the MTH_ITEM_DENORM_EXT_INIT_PF denorm process flow for the initial load and run the MTH_ITEM_DENORM_EXT_INCR_PF for the incremental load.
The csv template contains all columns from the item.
The Hierarchy Master table is as follows:
Dimension Name | Hierarchy Name |
ITEM | Inv.Items |
The Item Category table is as follows:
category_pk | system_fk | category_name | Description | ebs_category_id |
cat1 | dbi73d | cat1 | new | -1 |
cat2 | cat2 |
The Item Hierarchy Denorm table is as follows:
Hierarchy Name | Item | Level 9 | Level 8 | Level 7 | Level 6 | Level 5 | Level 4 | Level 3 | Level 2 | Level 1 |
Product Category | Item1 | C9 | C8 | C7 | C6 | C5 | C4 | C3 | C2 | C1 |
Items are moved to the error table because of dangling key issues for the following reasons:
The system reference is not available in the system table.
The plant reference not available in the plant table.
The primary unit of measure is not specified.
This data is fixed, and the reprocess flag switches from N to Y. During the next incremental item load, data from the error table is moved to the staging table for reprocessing.
Item hierarchy relationships are moved to the error table because of dangling key issues for the following reasons:
The hierarchy ID is not available.
The category for the level or parent level is not available in the category table.
This data is fixed, and the reprocess flag switches from N to Y. During the next incremental item load, data from the error table is moved to the staging table for reprocessing.