Data size impact on ODSResourceETL

[Former Member]

Dear experts,

we are trying to check the impact of data size on a given ETL function mainly the ODSResourceETL.

How long will ETL take to process around 5000 data entry in resource time log table, given that we need to do that once every 24 hrs

Thanks in advance

Ali

[Former Member · Accepted solution]

Ali

Performance is dependent on a huge number of factors, such as the database vendor, overall table sizes, ODS summary rules etc. That said, you should have an expectation that 5000 resource_time_log records can be processed in a matter of minutes under normal circumstances. Why would you extract once every 24 hours, instead of more regularly?

Stuart

[Former Member · Accepted solution]

Thanks Stuart

We are trying to backdate some information in the resource time log, and we want to make sure that the data in the ODS is consistent with the WIP. We are forced to do so since the information we are getting from the machines are not straight forward and they do not fit into ME context directly.

Goal is to make sure that any change, we do in time log table, must be considered in the ODS ETL script. And we want to do those changes before the script runs. The concern here that if we change archived none deleted information, the changes will not be archived in the next run of the script.

So we are trying to reduce the archiving rate or plan it on a suitable point of time, to be sure that if we change old entries in time log table the changes will be archived too.

As far as I understand from the ODS archiving, the "archived" or summarized data are kept for a while in WIP until it ages then it gets cleared.

Questions:

Saying that, if entries were archived at a given point of time, they are still in WIP and we run the script once again. Will the old WIP data be considered in the second run? are the old data marked as archive, hence during the second run they will not be touched. In this case what if WIP changes the old archived data, will the script consider them in the second run?

Clarification of those questions will help us on deciding whether we keep it simple and adhere to the standard ODS configuration or we have do the archiving on a suitable point of time with a lower rate maybe.

Thanks again

Ali

[Former Member · Accepted solution]

Ali

The entire topic of ODS really needs expert involvement to address your needs fully - I'm still not clear exactly what your issue is, and this forum isn't the best place for a detailed discussion.

1) it's best not to use the word "archiving" when discussing ODS, as the ETL scripts have nothing to do with archiving in SAP ME terms: archiving is strictly reserved for the removal of SFC-related WIP data, and relocating it in the AR_ tables within the ODS schema.

2) the table to which you refer "resource_time_log" is not subject to the archiving process. Nor is it even subject to data removal from WIP, unless you specifically execute the ODSWipAging script. This job permanently deletes WIP data, whether it's been extracted to ODS or not, so you need to be sure before this is executed.

3) the ODS scripts simply extract records from the WIP tables that have been newly added or updated since the execution of the ODS script. WIP records are not flagged as already sent to ODS - it's all based on timestamps.

At worst, you can clear down ODS by truncating the tables and restart the ODS scripts, but this takes some knowledge to perform. Also you need to adjust the scripts to execute in a loop so they don't run out of memory, since they will try and process all WIP records in a single execution.

Stuart

[Former Member · Accepted solution]

Thanks Stuart,

I agree the term archiving is not suitable here. What I meant is the overall ETL process for ME objects mainly the resource time log one.

regarding point 2) the resource time log is mentioned under this link again for ETL process not archiving

Scheduling of ODS Scripts - SAP Manufacturing Execution (SAP ME) - SAP Library

Thanks

Ali

[Former Member · Accepted solution]

Ali

For the resource_time_log, the ETL process specifically looks at the WIP record's MODIFIED_DATE_TIME (this is an internal use only column). If the date of the record is later than the last execution of the script, and the record is not marked as Active, it will be extracted and sent to the ODS.

[Former Member · Accepted solution]

Thanks Stuart,

the provided information are really helpful.