2013-01-17

MD_STOCK_REQUIREMENTS_LIST_API performant bulk use

Stock figures; what is in stock and what is available is a challenge in any ERP system. You should not calculate stock figures yourself but use an available function or API. In SAP I have used BAPI_MATERIAL_AVAILABILITY, but now I learnt MD_STOCK_REQUIREMENTS_LIST_API is a better RFC function for this.
It is simple to set up a job in ITL (Integration Tag language) that extracts the stock figures.
This job selects the materials (from a MySQL table the <forevery> job iterator) we like to fetch stock figures for. The job runs the MD_STOCK_REQUIREMENTS_LIST_API for each material and creates a MySQL database table if not exists for the E_MDSTA output and loads the data. If the table exists it is truncated prior to loading.
This job does the work correctly, but it took +70 minutes for 23000 materials . The problem here is that the SAP part of the job is iterated once for each material, it means log on to SAP extract the data and then log off from SAP and all this sequentially. This takes an awful long time (+70 minutes). The best way to solve this is to make an ABAP RFC to do the job. But this time we will to do this without ABAP coding.  
First we like to parallel process the materials in chunks, that is easily fixed:
Adding chunksize and parallel attributes chops the <forevery> array into 1000 row chunks and parallel process them but not more than 10 at a time. This do not however fix the problem with logging on and off to SAP for each material. This can be fixed either by introducing initialization  and termination routines to the <forevery> job iterator or create a piggyback iterator. The piggyback iterator is the simplest solution and requires no PHP coding.  
Now we have a job that chops up the <forevery> into chunks and parallel process each chunk, but this time the job iterator is pushed down to the SAP communicator sap2.php, which now only log on once for the entire chunk. But there is a snag, since we are parallel process the chunks we cannot truncate the target table in this job. We must create a truncate job that we run first.
And that is what this job does. The <prereq> statement intercepts execution if the @TABLE not exists in MySQL. (It would be nice with ‘TRUNCATE IF EXISTS’ command in SQL).
And that’s it, now we extract stock figures for 23000 material in less than 8 minutes, without any ABAP code. This could most likely be optimized below 5 minutes, (see ‘ when fast is not enough ’).
Mixing iterators can create some amazing functionality. If you take a close look at the example above you will find there is quite a lot of functionality packed in there with little code. Creating a job for each chunk with job templates is an alternative, but it is harder to control the parallel processing.
If you like this post I suggest you also read:
My series on Integration Tag language ‘ Job Scheduling with PHP 1-4 ’.

2013-01-06

SQL foreign key access and tree structures

Relation  in relational database theory means table  and nothing else, table with rows, columns and cells. Relational databases are tables, SQL is the programing language for manipulating and accessing those tables. The relational database is based on the theory of sets, this is a mixed blessing. On the positive side the theory of sets is mathematically proven and that is a good thing in itself at least it says the foundation is sound. But databases in applications are more than sets, a database is a model of the real world the application is supposed to support. E.g. sets do not have structure or order, the real world has. The structure the relational database have is the table structure and simple ‘relational links’ between tables, that can be used to restrict creation or deletion of rows in linked tables. Unfortunately SQL do not understand these links well, you cannot implicitly access linked data or show hierarchies.

Access via foreign keys.

Something I miss in SQL is the ability to walk through children and infer through parents via foreign key links. (You can to a certain extent compensate for these shortages with database views and virtual columns.)

For this database I would like to be able to say:

  1. select sono, customername , quantity sum-throu  SOlines as qty for SalesOrder;
  1. customername is the customername of the SalesOrder infer through the customerid foreign key link and in case of ambiguity can be qualified as SalesOrder.customername . Customer is a ‘link owner’ of SalesOrder.
  2. sum-throu is an infix function that sums the quantity of the SOlines children of the SalesOrder through sono foreign key link. Some other throu functions are max, min & mean. SalesOrder is a ‘link owner’ of the SOlines.

Compare this with the more correct SQL:

  1. select sono,customername, sum(quantity) as qty for SalesOrder left join Customer on customerid, left join SOlines on sono group by sono, customername;

I do not like specifying things the computer should be able to figure out. Getting rid of explicit joins is an advantage, even trivial joins are complex for most people. Ever since the introduction of foreign key links in DB2 I wondered when SQL should include access via foreign key links. I have thought of creating a preprocessor in MySQL proxy, but we still use MyISAM storage engine which do not support foreign keys. MyISAM is very convenient for BI system, and historically it has been the fastest storage engine, with the latest MySQL versions this may not be true anymore, I will  have a look at InnoDB when MySQL 5.6 is released.

(Foreign keys are the SQL feature I miss the most in MyISAM. Other features I miss in MySQL are computed/virtual columns and materialized views.)  

Tree structures.

The support for tree structures in SQL is at the best crappy.Good tree structure support includes:

  1.  Ability to go up and down the tree, starting from the top or the bottom.
  2. Stop traverse the tree at certain nodes or exclude nodes from the result tree.
  3. Tree level indicator so you know where in the tree you are.
  4. Ability to intercept updating if loops are found in the tree structure

Tree structures are very useful but unfortunately also very complex, so they are ‘under-utilized’. Simple and powerful support for tree structures including the bullets above would take SQL to a new level. Tree structures are a big topic that deserves a post of its own. I end this post with a link to a post  describing a tree structure we use.