When executing a sales document delta job in the Sales RapidMart, the process is hanging on the ABAP data flow R3_SalesOrderStageDelta. After reviewing the generated ABAP, it appears that the entire, unlimited row set from the joined results of VBAK, VBAP, VBUP and VBUK are queried into an internal table, and then the delta algorithm is implemented by looping through the contents of the SalesOrderID.dat file and moving the desired rows into another internal table for the result set and ultimate return to Data Services. I suspect at some point as order activity grows the size of these tables that the ABAP couldn’t even handle this approach and would result in the SAP Job doing a core dump.
I suppose that would be fine as long as you don’t have many sales documents in total, but that seems like an incredibly bad approach otherwise. I understand that it auto-generates the ABAP this way because the list of delta sales documents is in an external file, but I’m questioning the practicality of this approach in the real world. Has anyone else had to deal with this or come up with a better approach? Or, am I misunderstanding what the ABAP is actually doing?
I think we have a translation issue here. You are talking from the ABAP world, and I am trying to get it translated in to BODS objects
The SalesOrderID.dat, is that a list of sales documents that should be extracted? And that list is a list of changed documents earlier determined?
In the R3 flow in BODS you can determine the join order with the join rank.
There is a join rank on the tabel definition on the R/3. Assuming you have BODS 4.0: set the join ranks on the tables/flat file to 0. Then go to the query and set the join ranks from there in the FROM tab. This is the 4.x approach and considered “better”.
I suggest the following join rank:
flat file: 60 (maybe 50, equal with VBAK)
VBAK: 50
VBAP: 40 (maby also 50)
VBUK: 30
VBUP: 20
Sometimes you need to test the join order to get the best results.
Thanks for your input. Ultimately the issue is the extent to which we can control how the ABAP gets generated by making design or setting changes in BODS. Yes, SalesOrderID.dat is the list of changed/new document numbers. The list is not distinct so that is addressed in the ABAP data flow in a query transform before connecting to the core (VBAK, VBAP, VBUK, VBUP) query.
Yes this is BODS 4.x. I tried adjusting the join ranks as you suggested and I was able to modify (to some extent) the behavior of the ABAP generation. That is a nice feature I had never used. I tried a number of setting combinations but I wasn’t able to get the internal table holding the file data to inner join to the VBAK table in the ABAP. The best I was able to do was get it to Loop on the internal table’s document numbers, with it executing the core query including a WHERE clause for the document number. That would be better than before (I suspect).
I have another option at my disposal for this implementation which should prove much more efficient. I’ll pursue that path next and see how it works out.
Normally the join order determines how things join. However the distinct is blocking this.
There are 2 approaches for this:
Don’t do a distinct on the SAP side
Or write to another flat file first with a distinct in between
It is a bit tricking stuff, but there is sometimes no neat solution for this.
Edit:
Another thought:
Why bother with extracting a complete document? Why not first extract the relevant tables one by one and join them together outside SAP?
If you have this option at your disposal you can get a huge performance improvement (~20:1) out of the Sales and Delivery document R3 delta data flows. I created a Z table in ECC to use in place of the text file. I first load the Z table with the distinct list of document numbers which were previously held in the file, and then inner join the new Z table to the query and remove any references to the file. This results in the ABAP’s main SELECT statement including the Z table, and ultimately pushes the document selection down to the database rather than filtering it after the fact in a code Loop.
The only thing some people might take issue with is how you go about loading the document numbers into the SAP Z table from DI. I had the option of creating a data store connected directly to the ECC database, including only the new Z table and wrote directly to it. Yes, this bypasses the normal SAP integration and connectivity approach and certainly won’t be permitted by most BASIS folks. However, I argue that implemented responsibly, it poses no risk and is much more efficient. Granted, SAP would never approve of such an approach but rational thought led me to feel this was a far more appropriate method than loading the table through a painful idoc approach.
This turned a 17 hour sales doc load into 1 hour, and a 33 hour delivery document load into 1.25 hours.
~750K delta rows resulting in SALES_ORDER_FACT
~1.6M delta rows resulting in DELIVERY_FACT
However, the rows into the RapidMart are not the controlling factor so much as the number of total rows in the ECC tables that the ABAP would otherwise have to sift through row by row.
(BOB member since 2005-06-22)
(BOB member since 2012-03-20)