Setting Up Item Dimensions

This chapter covers the following topics:

• Dimension Levels
• Item Dimension Setup Steps
• Item Master: CSV Templates
• Item Dimension Maintenance

Dimension Levels

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:

Item Dimension Setup Steps

The item dimension setup steps are as follows:

1. Load items.

2. Update the hierarchy master.

3. Load the item hierarchy.

Load Items

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:

1. 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

2. 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:

1. In the Microsoft Excel template, update MTH_ITEMS_MASTER.csv

2. 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.

3. 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

4. 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.

Update the Hierarchy Master

To load EBS Category Set for the seeded hierarchy, change the Hierarchy name to the EBS Category Set name.

Load the Item Hierarchy

You can build item hierarchies in Manufacturing Operations Center or import them from EBS.

To load item hierarchies from EBS:

1. 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

2. 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.

3. 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:

1. Update MTH_ITEM_DIMENSION_DENORM.csv in the Microsoft Excel template.

2. 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.

3. 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.

4. 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.

5. 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.

Item Master: CSV Templates

The csv template contains all columns from the item.

Hierarchy Master

The Hierarchy Master table is as follows:

Dimension Name	Hierarchy Name
ITEM	Inv.Items

Item Category

The Item Category table is as follows:

category_pk	system_fk	category_name	Description	ebs_category_id
cat1	dbi73d	cat1	new	-1
cat2		cat2

Item Hierarchy Denorm

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

Item Dimension Maintenance

Reprocessing of Error Items

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.

Reprocessing of Error Item Hierarchy

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.