ODS_OPERATION_PRODUCTION table is empty

[Former Member]

Hi,

In my 6.0.3.5 environment I am having an issue with my ODS Summary.

I run the ODSProductionETL.bat file every hour. An extract from the log file looks like this:

Tue Oct 16 14:32:57 CEST 2012 ODSProductionLogExtract: Started

Tue Oct 16 14:33:00 CEST 2012 ODSProductionLogExtract: Extract inserted 1109 records into ODS_TEMP_PRODUCTION_LOG

Tue Oct 16 14:33:00 CEST 2012 ODSProductionLogExtract: Completed

Tue Oct 16 14:33:01 CEST 2012 ODSShopOrderExtract: Started

Tue Oct 16 14:33:03 CEST 2012 ODSShopOrderExtract: Extract inserted 152 records into ODS_TEMP_SHOP_ORDER

Tue Oct 16 14:33:03 CEST 2012 ODSShopOrderExtract: Completed

Tue Oct 16 14:33:03 CEST 2012 ODSShopOrderExpansion: Started

Tue Oct 16 14:33:09 CEST 2012 ODSShopOrderExpansion: Inserted 152 records in to ODS_SHOP_ORDER with 0 errors

Tue Oct 16 14:33:09 CEST 2012 ODSShopOrderExpansion: Completed

Tue Oct 16 14:33:10 CEST 2012 ODSProductionLogExpansion: Started

Tue Oct 16 14:33:24 CEST 2012 ODSProductionLogExpansion: Inserted 1109 records in to ODS_PRODUCTION_LOG with 0 errors

Tue Oct 16 14:33:24 CEST 2012 ODSProductionLogExpansion: Completed

Tue Oct 16 14:33:25 CEST 2012 ODSProductionLogSummary 1: Started

Tue Oct 16 14:33:26 CEST 2012 ODSProductionLogSummary 1: Inserted 0 records in to ODS_OPERATION_PRODUCTION for Site TEST1 with 0 errors

Tue Oct 16 14:33:26 CEST 2012 ODSProductionLogSummary 1: Updated 0 records in ODS_OPERATION_PRODUCTION for Site TEST1

Tue Oct 16 14:33:26 CEST 2012 ODSProductionLogSummary 1: Completed

Tue Oct 16 14:33:27 CEST 2012 ODSProductionLogSummary 2: Started

Tue Oct 16 14:33:29 CEST 2012 ODSProductionLogSummary 2: Inserted 0 records in to ODS_OPERATION_CYCLE_TIME for Site TEST1 with 0 errors

Tue Oct 16 14:33:29 CEST 2012 ODSProductionLogSummary 2: Updated 0 records in ODS_OPERATION_CYCLE_TIME for Site TEST1

Tue Oct 16 14:33:29 CEST 2012 ODSProductionLogSummary 2: Completed

Tue Oct 16 14:33:29 CEST 2012 ODSProductionLogSummary 3: Started

Tue Oct 16 14:33:43 CEST 2012 ODSProductionLogSummary 3: Inserted 1468 records in to ODS_ORDER_CYCLE_TIME for Site TEST1 with 0 errors

Tue Oct 16 14:33:43 CEST 2012 ODSProductionLogSummary 3: Updated 2 records in ODS_ORDER_CYCLE_TIME for Site TEST1

Tue Oct 16 14:33:43 CEST 2012 ODSProductionLogSummary 3: Completed

Tue Oct 16 14:33:43 CEST 2012 ODSProductionLogSummary 4: Started

Tue Oct 16 14:33:52 CEST 2012 ODSProductionLogSummary 4: Inserted 553 records in to ODS_ORDER_CYCLE_TIME_SUMMARY for Site TEST1 with 0 errors

Tue Oct 16 14:33:52 CEST 2012 ODSProductionLogSummary 4: Updated 8 records in ODS_ORDER_CYCLE_TIME_SUMMARY for Site TEST1

Tue Oct 16 14:33:52 CEST 2012 ODSProductionLogSummary 4: Completed

Tue Oct 16 14:33:52 CEST 2012 ODSProductionLogSummary 5: Started

Tue Oct 16 14:33:55 CEST 2012 ODSProductionLogSummary 5: Inserted 0 records in to ODS_WORK_CENTER_PRODUCTION for Site TEST1 with 0 errors

Tue Oct 16 14:33:55 CEST 2012 ODSProductionLogSummary 5: Updated 0 records in ODS_WORK_CENTER_PRODUCTION for Site TEST1

Tue Oct 16 14:33:55 CEST 2012 ODSProductionLogSummary 5: Completed

My ODS_OPERATION_PRODUCTION table never gets filled with any data.

Anybody have a clue of what I am doing wrong?

Best Regards,

Johan

[sergiy_katerinich · Accepted solution]

Johan,

What about ODS Rule Maintenance?

Regards,

Sergiy

[Former Member · Accepted solution]

Hi Sergiy,

This is my ODS Rule parameters

Production Operation Summary
ODS Retention Period		9999
Copy to Global ODS	TrueFalseUse Global Setting	FALSE
Summarize by Site
Summarize by Material
Summarize by Operation
Summarize by Shop Order	TrueFalseUse Global Setting	TRUE
Summarize by Labor Charge Code	TrueFalseUse Global Setting	FALSE
Summarize by Order Type	TrueFalseUse Global Setting	TRUE
Summarize by Customer	TrueFalseUse Global Setting	TRUE
Summarize by Customer Order	TrueFalseUse Global Setting	FALSE
Summarize by Routing	TrueFalseUse Global Setting	TRUE
Summarize by Resource	TrueFalseUse Global Setting	TRUE
Summarize by Step ID	TrueFalseUse Global Setting	TRUE
Summarize by Reporting Center	TrueFalseUse Global Setting	TRUE
Summarize by User ID	TrueFalseUse Global Setting	TRUE
Summarize by Cost Center	TrueFalseUse Global Setting	TRUE
Summarize by Rollup Cost Center	TrueFalseUse Global Setting	TRUE

Br,

Johan

[sergiy_katerinich · Accepted solution]

I would turn on SQL trace to check what's actually going on during ODSProductionLogSummary 1 phase.

There is a Note 1289112.

Regards,

Sergiy

[Former Member · Accepted solution]

Hi Sergiy,

This is the SQL Trace

Thu Oct 18 09:00:15 CEST 2012 ODSProductionLogSummary 1: Started

Thu Oct 18 09:00:16 CEST 2012 ODSProductionLogSummary 1: [745]{1} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='*'  AND SYSTEM_RULE.SYS_RULE='ODS_LAG_TIME'  AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO

Thu Oct 18 09:00:16 CEST 2012 ODSProductionLogSummary 1: [29]{1} SELECT EXTRACT_DATE_TIME FROM ODS_EXTRACT_CONTROL_DATE  WHERE TABLE_NAME ='PRODUCTION_LOG'  AND TRANS_TYPE ='1'

Thu Oct 18 09:00:16 CEST 2012 ODSProductionLogSummary 1: [7]{1} SELECT EXTRACT_DATE_TIME FROM ODS_EXTRACT_CONTROL_DATE  WHERE TABLE_NAME ='PRODUCTION_LOG'  AND TRANS_TYPE ='2'

Thu Oct 18 09:00:16 CEST 2012 ODSProductionLogSummary 1: [6]{1} SELECT EXTRACT_DATE_TIME FROM ODS_EXTRACT_CONTROL_DATE  WHERE TABLE_NAME ='PRODUCTION_LOG'  AND TRANS_TYPE ='3'

Thu Oct 18 09:00:16 CEST 2012 ODSProductionLogSummary 1: 10 UPDATE ODS_EXTRACT_CONTROL_DATE SET  LAST_EXTRACT_DATE_TIME = '2012-10-18T06:51:42.000Z' , EXTRACT_DATE_TIME = '2012-10-18T06:55:07.000Z'  WHERE TABLE_NAME = 'PRODUCTION_LOG'  AND TRANS_TYPE = '3'

Thu Oct 18 09:00:16 CEST 2012 ODSProductionLogSummary 1: [8]{0} SELECT DISTINCT SITE FROM ODS_PRODUCTION_LOG WHERE PARTITION_DATE > '2012-10-18T06:51:42.000Z'  AND PARTITION_DATE <= '2012-10-18T06:55:07.000Z'  ORDER BY SITE

Thu Oct 18 09:00:16 CEST 2012 ODSProductionLogSummary 1: Completed

Best Regards,

Johan

[sergiy_katerinich · Accepted solution]

Johan,

So, this means that the system checked ODS_PRODUCTION_LOG table for the timeframe from the previous run (2012-10-18T06:51:42.000Z) till the current run of the script (2012-10-18T06:55:07.000Z), but found no rows which were expected to be new to ODS after data export from WIP during the current execution. This could be OK if taking into account that you run the script 4 minutes apart.

However, the origianl logs say:

Tue Oct 16 14:33:24 CEST 2012 ODSProductionLogExpansion: Inserted 1109 records in to ODS_PRODUCTION_LOG with 0 errors

but

Tue Oct 16 14:33:26 CEST 2012 ODSProductionLogSummary 1: Inserted 0 records in to ODS_OPERATION_PRODUCTION for Site TEST1 with 0 errors

Tue Oct 16 14:33:26 CEST 2012 ODSProductionLogSummary 1: Updated 0 records in ODS_OPERATION_PRODUCTION for Site TEST1

So, the issue is either due to wrong values in PARTITION_DATE as result of incorrectly set clocks at DB side, or the data itself, or a bug in code.

So, the clocks should be in sync throughout the environment. As to timezone setup at Application server and DB server, there was a SAP Note.

As concerns the cases of data issue and a possible bug, this investigation will require the database dumps and step-by-step examination of SQL trace for each transaction of the script.

Regards,

Sergiy

[Former Member · Accepted solution]

Hi Sergiy,

I have a new tracefile for ODSLog now where there is data inserted into ODS_PRODUCTION_LOG but then

nothing is inserted to ODS_OPERATION_PRODUCTION. Maybe you now how a normal log file should look like

and can see anything?

Logfile is attached to this message.

Br,

Johan

ODSLog.txt.zip

[sergiy_katerinich · Accepted solution]

Johan,

It's all about Time Granularity. Looks as if you do not have Time Granularity assignment for the corresponding categories at the site TEST1. Please check the maintenance activity.

Regards,

Sergiy

[Former Member · Accepted solution]

Hi,

According to the How-to for ODS Setup, the time granularity should have 3 pre-defined values, HOUR, SHIFT and DAY. When I look in Time granularity maintenance, these values are missing. I have no records at all when i press the browse-button. Should these values be entered manually or have I missed something in my system setup?

Br,

Johan

[sergiy_katerinich · Accepted solution]

Johan,

Those 3 entries are predefined and not accessible in Time Granularity Maintenance. You can access only custom entires that you created manually if you need them at all.

But predefined entries are visible in Time Granularity Assignment Maintenance.

Regards,

Sergiy

[Former Member · Accepted solution]

Hi Sergiy,

Thank you for the help on this

I have now, according to the best practice, set up time granulariy of HOUR in time granularity assignment maintenance for Production Operation. After this I now have data in the ODS_OPERATION_PRODUCTION table.

Any idea how I can transfer all the old data that is now in ODS_PRODUCTION_LOG?

Best Regards,

Johan

[sergiy_katerinich · Accepted solution]

Johan,

The only consistent (and proven) way is to clear out all ODS data and re-run processing from scratch.

However, the cheating is possible though I have not tried it:

- stop ODS processing;

- return dates in ODS_EXTRACT_CONTROL_DATES table for TABLE_NAME ='PRODUCTION_LOG'  AND TRANS_TYPE ='3' back to the very beginning;

- then create a ETL script that will execute only ODSProductionLogSummary 1 transaction;

- execute that script manually to catch up on the old data;

- the same recommendations on avoiding Out Of Memory issue are applicable here.

If I have not missed anything, this might work.

Regards,

Sergiy

[Former Member · Accepted solution]

I can confirm that this will work - I do this regularly to fix broken extracts. You may need to loop and process smaller data sets of course, to avoid out of memory.

[Former Member · Accepted solution]

Hi,

How should I update the ODS_EXTRACT_CONTROL_DATE table?

Should I set both the LAST_EXTRACT_DATE_TIME and EXTRACT_DATE_TIME back to time for the very first record in ODS_PRODUCTION_LOG table?

Should the result be that many records should be inserted into ODS_OPERATION_PRODUCTION?

Should the records be cleared in ODS_PRODUCTION_LOG table?

I know many questions, but I guess I wan't to learn as much as possible when I am anyway struggling with this.

Best Regards,

Johan

[Former Member · Accepted solution]

Yes, set both the LAST_EXTRACT_DATE_TIME and EXTRACT_DATE_TIME back to time for the very first record in ODS_PRODUCTION_LOG table, or earlier.

That's all - it reads from ODS_PRODUCTION_LOG table and summarises from that.