Setting Up Operational Data Store (ODS)

You can use this process to do the following:

Create and configure ODS database
Create synonyms in the WIP database
Create Global ODS database (optional)
Configure ODS within SAP ME
Set up and schedule ODS scripts

The figure below illustrates the primary flow of system administrator actions when setting up ODS:

This graphic is explained in the accompanying text.

Recommendation

We recommend that you set up ODS at the time that the SAP ME is installed if ODS will ever be used for the SAP ME installation. Waiting to set up ODS until after the SAP ME system is in use, can cause significant problems in getting the ODS database “caught up” with all of the data that is already in the WIP database at that time.

End of the recommendation.

Prerequisites

You have created and configured ODS database during installation of SAP ME. For more information, see SAP ME Installation Guide at http://service.sap.com/instguides SAP Business Suite Applications SAP Manufacturing SAP Manufacturing Execution .
Use of the ODS tables for reporting depends upon master definition data in the WIP database. This includes names / IDs of the primary SAP ME business objects, such as sites, materials, operations, resources, and so on.

SQL scripts are used to create synonyms in the ODS database for the WIP tables that contain the master definition data.
You have created synonyms for the ODS tables in the WIP database during installation of SAP ME. For more information, see SAP ME Installation Guide at http://service.sap.com/instguides SAP Business Suite Applications SAP Manufacturing SAP Manufacturing Execution .
The ODS ETL scripts use these synonyms when extracting, transforming and loading data from the WIP database into the ODS database.
You have created and configured the Global ODS database during installation of SAP ME, if Global ODS database will be used (optional). For more information, see SAP ME Installation Guide at http://service.sap.com/instguides SAP Business Suite Applications SAP Manufacturing SAP Manufacturing Execution .
You have scheduled the ODS scripts to run on a regular basis, if you need to automatically store and manage WIP data (see Scheduling of ODS Scripts).

Process

In System Rule Maintenance, you set up the Store Data Collection Results in ODS and Store Test Plan Results in ODS system rules (see Miscellaneous Rules).
- Store Data Collection Results in ODS
  This system rule is used to specify that the parameter values collected in Data Collection are to be stored directly into the ODS database (Archive tables) instead of being stored into the WIP database.
  
  If the system rule is set to true, the parameter values will be stored directly into the Archive tables in the ODS database. If it is set to false, the parameter values will only be stored in the WIP database.
- Store Test Plan Results in ODS
  This system rule is used to specify that the parameter values collected from factory floor machines through test plans are to be stored directly into the ODS database (Archive tables) instead of being stored into the WIP database.
  
  If the system rule is set to true, the parameter values will be stored directly into the Archive tables in the ODS database. If it is set to false, the parameter values will only be stored in the WIP database.
In System Setup Maintenance, you retrieve data for the Property type and specify values for the expand.handle.timeout and max.records.for.handle.expansion (see System Setup Maintenance).
- expand.handle.timeout
  This property specifies the timeout value for the expansion of the SAP ME business object handles for the allowed number of database table records specified in the max.records.for.handle.expansion property.
  
  This property value is a numeric value specifying the number of minutes. The default value is 30.
  
  SAP ME business object handle is a text string that is comprised of the database record key values concatenated together. Expanding SAP ME business object handle consists of breaking the handle apart into its component key values. These key values are then stored in separate columns in the new database table record being created. Business object handles can be nested: a handle can contain another handle, which can contain another handle, and so on. Thus it is possible that a large number of handles could take a long time to expand. A timeout value is provided to ensure that the expansion process is stopped after a specified amount of time.
  
  For example, by setting the expand.handle.timeout value to 15, the expansion of the allowed number of handles (including the expansion of all nested handles) for a database table will stop if not completed in 15 minutes.
- max.records.for.handle.expansion
  This property specifies the maximum number of database records that can have their handle expanded as a single action in a specific table at a specific time.
  
  The default value is 50000.
  
  For example, by setting the max.records.for.handle.expansion value to 10000, the expansion of the handles for a table will be stopped after ten thousand records, if not already complete.
In ODS Rule Maintenance, you set up the following rules:
- Extract Lag Time
  This ODS rule specifies the number of minutes into the past at which the ODS data extraction process should stop extracting WIP data for storage into the ODS tables. This rule applies to all types of data and is used each time the ODS data extraction process runs. It provides a safety margin to ensure that each SAP ME business transaction is completed prior to any of its data being stored into the ODS tables. The default value is 0. This rule can only be set for the global (*) site.
  
  Recommendation
  We recommend that you set the Extract Lag Time rule to a value greater than zero. SAP ME WIP database records can be created through Web service calls. You can insert these records into the database with a record creation time that is in the past. If the Extract Lag Time value is too small, this can cause the inserted record to have a creation time that is prior to the end time of the last ODS execution (start time of the next ODS execution). In this case, the inserted record will never be copied to the ODS tables. The Extract Lag Time should be set to a value greater than the maximum possible delta, between the stored creation time for a record created by a Web service and the current time when the Web service is executed. Or the time zone for both the database server and the database should be set to GMT (see SAP Note 1471975).
  
  End of the recommendation.
- How data is summarized (see ODS Data Summarization)
  
  Recommendation
  We recommend summarizing data by all available objects. This is because if data is not summarized by an object, and later it is determined that summarization by that object is needed, it may no longer be possible to summarize the data based upon that object. For example, two users each log a nonconformance. If the NC data is summarized by user, there will be two records in the ODS NC Data Summary table, one for each user. However, if the NC data is not summarized by user, there will only be one record with a quantity of two and the user information will not be present in the ODS NC Data Summary table (see ODS Data Summarization).
  
  End of the recommendation.
- Summarize by Local Time Zone
  This ODS rule specifies whether to summarize data based on GMT time or based on the local time zone. This primarily affects data summarized using the time granularity of Day (see Miscellaneous Rules).
- ODS Retention Period
  This ODS rule specifies how many days data is to be kept in the ODS tables before being deleted from the ODS tables. The default value is 30.
  
  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
- WIP Retention Period
  This ODS rule specifies how many days data, which can be stored in the ODS tables, is to be kept in the WIP tables before being deleted from the WIP tables. The default value is 30.
  
  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
- Copy to Global ODS
  This ODS rule specifies whether or not to copy the summary data to the Global ODS database. The default value is false.
  
  There is a separate Copy to Global ODS rule for each of the following types of data:
  - Defect transfer table summary (DPMO)
  - Nonconformance summary
  - Order cycle time
  - Production operation summary
  - Production cycle time
  - Resource utilization summary
  - Work center production
- Copy Time-Based Data to ODS
  This ODS rule specifies whether or not to copy time-based genealogy data to the ODS tables.
  
  If the rule value is true, time-based genealogy data is extracted, transformed and loaded into the ODS tables. If the rule value is false, time-based genealogy data is not copied to the ODS tables. The default value is false.
- Convert to Discrete
  This ODS rule specifies whether or not to convert time-based genealogy data to discrete data when copying time-based data to the ODS tables.
  
  If the rule value is true, time-based genealogy data is converted to discrete data records. If the rule value is false, time-based genealogy data is not converted to discrete data records. The default value is false.
- ODS NC Serialization Adjustment
  This ODS rule specifies whether or not to remove a previously collected nonconformance (NC) against a panel. When NC data is recorded against a panel material and a location is referenced, then the NC data may optionally be moved to the relevant SFC number after serialization (see Post Split/Serialize - Copy NC Data). In this case, the ODS process may record the NC data against the panel at one ETL script execution and then later against the serialized SFC, thereby doubling the failure data. This ODS rule provides the deletion of the panel NC data from the ODS tables when the NC is recorded in ODS for the SFC number.
  
  If the rule value is true, then the original NC data that was recorded against the panel material is deleted from the ODS tables. If the rule value is false, the panel data is not deleted. The default value is false.
- Perform Summarization at
  This ODS rule specifies whether to perform nonconformance data summarization based upon incident closure or NC closure. The valid values are On Incident Closure and On NC Closure. The default value is On Incident Closure. This ODS rule is only available for the Nonconformance Summary type data.
For more information, see ODS Rule Maintenance.
To define how labor data is to be summarized and to be retained the ODS tables, you have set up the following rules on the Rollup Processing Rules tab page in Labor Rule Maintenance:
- Enable SFC Labor Summarization
  This rule specifies whether or not labor data is to be summarized by SFC number. If selected (default), labor data will be summarized by SFC number and the summary data will be loaded into the ODS tables.
- Enable User Labor Summarization
  This rule specifies whether or not labor data is to be summarized by user. If selected (default), labor data will be summarized by user and the summary data will be loaded into the ODS tables.
- Summarize SFC Labor by
  This rule provides control over how (for which business objects) the labor data is summarized by SFC number. If the checkbox associated to the business object is selected, the labor data will be summarized by SFC number for that business object.
  
  Labor data can be summarized by SFC number for the following business objects:
  - Cost center (default)
  - Customer
  - Customer order
  - Material
  - Operation
  - Reporting center
  - Resource
  - Routing step
  - SFC number
  - Shop order (default)
  - Work center
- Summarize SFC Labor by Custom Fields
  This rule specifies the custom fields for which the labor data will be summarized by SFC number. Custom field names must be separated by a comma.
- Summarize User Labor by Custom Fields
  This rule specifies the custom fields for which the labor data will be summarized by user. Custom field names must be separated by a comma.
- Labor Age
  This rule specifies how many days labor data (SFC number and user) is to be kept in the ODS tables before being deleted from the ODS tables. The default value is 60.
- Idle Factor for Labor Time Age
  This rule specifies how idle time between SFC numbers is to be applied. If the rule is set to Labor On to Labor On, the idle time is allocated to the preceding SFC number - time allocated to the SFC number is from labor on to the SFC number until labor on to a different SFC number. If the rule is set to Labor On to Labor Off, the idle time will be allocated to the default labor charge code (LCC) for the user’s cost center. The time allocated to the SFC number is only from labor on to the SFC number until labor off of that SFC number. The default setting is Labor On to Labor Off.
- Distribution of SFC Labor Time
  This rule specifies how labor time for multiple SFC numbers, worked simultaneously, will be distributed across the SFC numbers (see Labor Rule Maintenance).
- Time Card Approval Required for Export
  This checkbox specifies whether or not the user labor time must be approved prior to the labor data being extracted and loaded into the ODS tables. If selected (default), the user labor time must be approved. If the checkbox is not selected, the labor data can be extracted and loaded into the ODS tables without the user labor time being approved.
For more information, see Labor Rule Maintenance.
You define time granularity that will be used for data summarization in Time Granularity Maintenance (see Time Granularity Maintenance).
You can create a new time granularity by specifying its start date / time and end date / time. You can also specify whether or not the time granularity is to be recurring yearly.

Recommendation
We recommend that you use one of the predefined time granularities (Hour, Day, and Shift).

End of the recommendation.
You specify the summarization time granularity for each type of data that is summarized in Time Granularity Assignment Maintenance (see Time Granularity Assignment Maintenance).
You can specify multiple granularities for each type of data.

Recommendation
We recommend that you only specify one time granularity and that you use the predefined time granularity of Hour. With data summarized by hour, reports can then easily calculate day and shift summaries.

End of the recommendation.