Data Collection Technical Workshop
ASCP- Data Collection 43/53
Sanjeev Kale
Oracle Corporation
ASCP- Data Collection 43/53
INTRODUCTION
This paper focuses on understanding the of Data architecture used in the collection of planning data from multiple operational sources into Oracle ASCP. These sources could be different versions/instances of Oracle Applications or other legacy systems. Oracle ASCP uses a data store based on the planning data model that is exposed through interface tables. The data is pulled from the designated data sources into its data store; Oracle ASCP Collections are responsible for synchronization as changes are made to the data sources. The configurability of the collections is enabled through a pull program based on AOL concurrent program architecture. Thus, for example, different business objects can be collected at different frequencies. Supplies and demands, which change frequently, can be collected frequently. Routings and resources, which change relatively less often, can be collected less frequently.
Collecting data can take a significant amount of time compared to the time for the overall planning cycle. Oracle Advanced Supply Chain Planning (ASCP) provides a collection method that allows the collections duration to be reduced in cases where information about some - but not all - planning-related business entities on the planning server needs to be updated.
There are three collection methods
· The Complete Refresh method clears all transaction data for all business entities from the planning server (for the source instance being collected), then copies over information about the user-selected entities. This method can be time consuming.
o You should use Complete Refresh the first time you perform collections from a source instance to the planning server. You may also wish to use complete refresh collections after a significant proportion of the setup data in your transaction system has been altered, and you would like to make a fresh copy of all source instance business entities (items, bills of material, sourcing rules, resources, and so on) on the planning server. You typically collect all business entities in a Complete Refresh Collection.
· The Net Change Refresh method copies only incremental changes to business entities to the planning server (and is thus faster), but is supported mainly for demand and supply business entities only.
o You should use Net Change Refresh if you would like to update the supply and demand picture on the planning server as quickly as possible, and the incremental changes to supply and demand in the source instance since the last collection have not been extensive relative to the existing (already collected) body of supply and demand information. In this case, Net Change Refresh is the fastest way to achieve the desired update of the planning server operational data store, because it copies over from the source instance only the incremental changes in supply and demand since the last collection.
· The Targeted Refresh method clears transaction data for only the user-selected business entities from the planning server, and then copies the entity information over from the transaction instance. Information about nonselected entities remains intact on the planning server. All planning business entities are supported by Targeted Refresh collections.
o You should use Targeted Refresh if you would like to update the planning server information for some (but not all) business entities, and some of these entities fall outside the category of supply and demand entities supported by Net Change Refresh. For example, to update the planning server with a newly rebuilt manufacturing calendar, you would run Targeted Refresh collections for just the calendar business entity. Data on the planning server about all other business entities would remain unaffected by this collection.
o You would also use Targeted Refresh (in lieu of Net Change Refresh) to bring over the latest picture of supply and demand to the planning server in cases when the incremental changes to supply and demand on the source instance since the last collection are very extensive. In this case, the update mechanism employed by Targeted Refresh collections (wholesale deletion followed by rebuilding of data on the planning server) is faster than the mechanism employed by Net Change Refresh collections (incremental insertions into existing data on the planning server).
The data collection process consists of the Data Pull and the Operational Data Store (ODS) Load. The collection process lets you collect across Oracle Application Versions 11.0 or higher. It supports several configurations. The two types of collections process are standard and continuous.
· Standard collections process: Using the standard collections process, you can manually run three types of collection methods including a complete refresh, a net change refresh, or a targeted refresh on specific business entities. Standard data collection consists of the following processes:
o Pull program: Collects the data from the ADS and stores the data into the staging tables. This pull program is a registered AOL concurrent program that could be scheduled and launched by a system administrator. If you are using a legacy program, you must write your own pull program.
o ODS Load: A PL/SQL program which performs the data transform and moves the data from the staging tables to the ODS. This collection program is a registered AOL concurrent program that could be scheduled and launched by the system administrator.
Planning Pull Program / MSCPDP / MSCCLFAB.pls / MSC_CL_PULL.launch_monitorPlanning ODS Load / MSCPDC / MSCCLBAB.pls / MSC_CL_COLLECTION.launch_monitor
ATP Data Collection is a variation of the same Standard Collection process, it calls the same programs with the same parameters. The important parameters are
Collection Type - This parameter decides the type of Collections to be performed.
· Complete Refresh - Deletes the data in Destination for ALL Entities (except Sales Order which is still driven by the sales order parameter) and Collects all the new data ONLY for the entities set to YES on the 'Planning Data Pull' form.
· Net Change Refresh- Collects the changes for all the Entities set to YES in 'Planning Data Pull' form
Sales Order – This parameter decides the way Sales Order data is collected.
· Complete Refresh - Deletes the Sales Order in Destination and collects all the new Sales Order data and all the enities set YES.
· Net Change Refresh- Collects the changes for the Sales Order.
ATP Data PULL / MSCATPDP / MSCCLFAB.pls / MSC_CL_PULL.launch_monitorPlanning ODS Load / MSCPDC / MSCCLBAB.pls / MSC_CL_COLLECTION.launch_monitor
· Continuous collections process: The continuous collections process is an automated process of data collection that efficiently synchronizes the data on the planning server by looking up the sources. If you opt for continuous collections, the system automatically determine the type of collection that needs to be run on entities selected by you. The continuous collections process collects data from the sources with the least user intervention. The Continuous Collections concurrent program performs continuous collections.
Continuous Collections automatically selects either the net change refresh or targeted refresh collection modes for each entity selected for collection. Currently, you need to know which entities have snapshots associated with their base tables and are therefore supported by net change refresh collections, and then schedule or run a separate collections process for just these entities.
For entities with snapshots, Continuous Collections chooses net change refresh or targeted refresh based on the extent of changes to the entity data (since the last collection) relative to a concurrent program parameter called Snapshot Threshold (%). If the extent of changes is within the Snapshot Threshold (%), then Continuous Collections invokes net change refresh for the entity; otherwise it invokes targeted refresh. It thus employs the net change refresh mechanism only when it is computationally advantageous to do so.
For entities without snapshots, Continuous Collections always chooses targeted refresh. These entities are typically setup entities such as calendars, demand classes, customers and suppliers that are not updated as frequently as entities with snapshots.
You would typically schedule Continuous Collections to run frequently for supply/demand transactions with snapshots (to take advantage of net change collection of information from the transaction source instance to the planning server) and less frequently for setup entities.
Continuous collections aims to combine the benefits of targeted and net-change collections, and allows certain entities to be collected in a complete (targeted) manner and others in a net-change manner. The system makes the determination of the mode to collect an entity in. The motivation for doing this is to be able to keep the data in the APS tables as close to the data in the source ERP tables, as well as to achieve this synchrony in an efficient manner.
The continuous collections feature provides the following functionality:
· Ability to indicate the entities to be marked for continuous collection,
· System figures out the appropriate collection mode of complete (targeted) or net-change for that entity.
· Ability to schedule the continuous collections as a separate concurrent program, which runs periodically.
Continuous collections thus provide the ability to bring in the latest transactional data (supplies and demands) to the planning tables in an efficient and non-obtrusive manner, with little user intervention.
Automatic Collection / MSCAUTP / MSCCLFAB.pls / MSC_CL_PULL.launch_monitor_contAdditional Parameters for Continuous Collection
Snapshot Threshold (%)
For entities that have a snapshot associated with the source of that entity, it will be possible to determine whether to do a targeted or net-change collections. This is done by comparing the counts of records in the snapshot and the snapshot log: Let a be the count of new records in the snapshot log since the previous run of collections (actually, it will be since last refresh of the snapshot), and b the count of records in the snapshot.
Example
Suppose the Snapshot Threshold % is set to 40 %. (threshold = 40)
select count(*)
from MLOG$_BOM_BILL_OF_MATERIAL;
COUNT(*)
------
350
a = 350
select count(*)
from BOM.BOM_BOMS_SN;
COUNT(*)
------
864
b = 864
if a = 0 then
Don't do Collection for that particular entity.
elsif ((a/b)*100) > threshold
This means that the changes in the base table is more than the threshold percentage and so it is better off to do a Targeted Collections for this entity. The Continuous Collection will do a targeted collection.
else
Do net-Change refresh for this entity.
end if
threshold will be a parameter in %, settable by the user. The threshold determines when it will be efficient to simply collect all the rows from the snapshot versus when it will be efficient to only collect the changed rows using the snapshot log. If continuous collections are run frequently, then for most entities (which have snapshots), we will collect them in net-change mode.
Targeted Frequency (Hours)
For entities that do not have any snapshots associated with the source tables of that entity, it will not be possible to do net-change collections. For these entities, the targeted mode will be selected. The frequency parameter specified by the user will determine how frequently these entities will be collected in targeted mode.
Define two separate schedules for collections. One scheduled to run almost continuously collecting data entities which have snapshots associated with them like WIP, items, transactions etc. Another program scheduled to bring setup entities that do not have snapshot associated to them like customers, suppliers, sourcing rules etc. scheduled to run periodically.
DEFINITIONS
Oracle Applications Data Store (ADS) Represents all the source data tables used to build and maintain the planning data store within Oracle ASCP. It represents a single source data instance. E.g. transaction system (Source Instance)
MRP_AP_XXX Source transaction tables
MRP_SN_xxx_Tn Snapshot triggers
MRP_AD_xxx Planning source transaction tables
MRP_AD_xxx Planning source planning tables
Rows are populated in the MRP_AD_xxx and MRP_AP_xxx tables by database triggers (MRP_SN_xxx_Tn) defined on the corresponding database snapshots (xxx_SN). This activity occurs on the source instance.
Operational Data Store (ODS) Part of Oracle ASCP that represents all the planning data tables that act as destination for the collected data from each of the data sources (both ADS and Legacy). This acts as the input for the snapshot portion of the planning process. ODS and PDS share the same physical tables where a special plan identifier (for example, -1) is used for distinction.
Planning Data Store (PDS) Represents all the tables within Oracle ASCP which encompass those in the ODS and other output tables from planning, including copies/snapshots of the input data used for the current planning run striped by the appropriate plan identifier.
Collection Workbench Collection Workbench is a centralized data repository providing collected data from the source. Data from different source instances can be viewed using the Collection Workbench. The functionality here is similar to the Planner Workbench functionality. The collection workbench is used to verify that the intended data has been collected – If necessary, troubleshoot errors in data collection and rerun the data collection program.
Key Transformation To track data entities back to the source instance, APS transforms each unique business entity into a unique ID within APS. After the source data is transformed, the LID tables stay intact even during a complete refresh. Key transformation is two step process
· The first step is to generate the local ID for the new source key, which does not exist in the mapping tables.
· The second step is to transform the source ID to the local ID.
Key transformation is provided for
· Internal Item Numbers and following business entities
· Category Sets
· Suppliers
· Suppliers Sites
· Customers
· Customers Sites
Key Transformation Exercise
MSC_CATEGORY_SET_ID_LID (MSC_CATEGORY_SETS)
desc MSC_CATEGORY_SET_ID_LID
Name Null? Type
------
SR_CATEGORY_SET_ID NOT NULL NUMBER
SR_INSTANCE_ID NOT NULL NUMBER
CATEGORY_SET_ID NOT NULL NUMBER
MSC_COMPANY_ID_LID (MSC_COMPANIES)
desc MSC_COMPANY_ID_LID
Name Null? Type
------
SR_COMPANY_ID NOT NULL NUMBER
SR_INSTANCE_ID NOT NULL NUMBER
COMPANY_ID NOT NULL NUMBER
PARTNER_TYPE NOT NULL NUMBER
MSC_COMPANY_SITE_ID_LID (MSC_COMPANY_SITE_ID_LID)
desc MSC_COMPANY_SITE_ID_LID