Work with Operational Data Store (ODS)

You use the following scripts to work with ODS:

ODS ETL scripts
ODS ETL Global Copy scripts
ODS WIP Aging script
ODS Aging script

Extracting, Transforming, Loading and Summarizing ODS Data

The ODS ETL scripts copy data from the WIP database and store it in temporary tables in the ODS database. The handles for the data records are expanded by breaking each handle text string into its component key values. The detail data and the additional key values are stored into the ODS history detail tables. Some of the data is summarized and stored in the ODS summary tables. For more information, see the ODS Reporting Guide at https://service.sap.com/instguides SAP Business Suite Applications SAP Manufacturing SAP Manufacturing Execution .

There is an ODS ETL script for each of the following types of data:

Genealogy
Nonconformance
Production
Resource utilization
Shop order

There is a rollup (summarization) script for the labor data. Summarization of the other types of data is handled by the ETL script.

The figure below illustrates the flow for the ETL and extract scripts and the labor rollup script:

This graphic is explained in the accompanying text.

Copying Summary Data to Global ODS

The ODS ETL Global Copy scripts copy summary data from the ODS tables to the corresponding tables in the Global ODS database. There is an ODS ETL Global Copy script for each of the following types of data:

DPMO (nonconformance)
Nonconformance
Production
Resource utilization
Shop order

Deleting WIP and ODS Data

The ODS WIP Aging script deletes WIP data that has been in the WIP database for a longer time than the WIP Retention Period value for that type of data.

The ODS Aging script deletes ODS data that has been in the ODS data tables for a longer time than the ODS Retention Period value for that type of data.

Data can exist in both the WIP database and the ODS tables for some time. For example, production log data may remain in the WIP database for use with internal SAP ME reports even though the data has already been copied to the ODS tables.

There is a separate WIP Retention Period rule for each of the following types of data:

Defect transfer table detail (DPMO)
Production log detail
Resource utilization detail
Time-based component detail

There is a separate ODS Retention Period rule for each of the following types of data:

Defect transfer table detail (DPMO)
Defect transfer table summary (DPMO)
Nonconformance detail
Nonconformance summary
Order cycle time
Production log detail
Production operation summary
Production cycle time
Resource utilization detail
Resource utilization summary
Time-based component detail
Work center production

Caution Caution

If you choose to schedule the WIP Aging script to run automatically, make certain that you set every WIP Retention Period rule to a value large enough to ensure that no data would be lost due to the failure of an ETL script and carefully monitor the execution of the ODS scripts. Otherwise, the situation could arise where an ETL script fails resulting in some data not being copied from the WIP database to the ODS database. If the WIP Aging script then runs and deletes the data (that was not copied to ODS), that data may be permanently lost. If the data that was not copied is time-based genealogy data, this important traceability data would be lost.

For more information about the ODS Retention Period and WIP Retention Period rules, see ODS Rule Maintenance and Setting Up Operational Data Store (ODS).

End of the caution.