Load SAP forecasts into MySQL with RFC_READ_TABLE and PHP

Why do we not have the history of forecasts in the Data Warehouse? Because you have not asked for it.

Update: If this post is of interest you should also read this post.

Summary

This post describes how you can extract sales forecasts from SAP. How you can create dynamic SQL predicates for RFC_READ_TABLE. And how you can parallel process these predicates for performant extraction of data from SAP.

Forecast

Forecasts in SAP is stored in the PROW table (forecast results). Or to be correct the Sales forecasts for our products and spare parts are stored in the PROW table. The key to this table is a sequence number PNUM2, a pointer to forecast results. PNUM2 does not tell you much. PNUM2 has to be translated into PNUM1 which is the key to the PROP table (forecast parameters). Unfortunately the PNUM1 key does not tell you much, it must be translated into MATNR and WERKS those fields are the key to products (and components). Translate PNUM1 into WERKS and MATNR can be done in table MAPR.

To find all  forecasts for a product you:

1. Look for the product (WERKS & MATNR) in MAPR.  
2. Take all PNUM1 in MAPR and find all corresponding rows in table PROP.
3. Take all PNUM2 in PROP and find all  corresponding rows in PROW.

Material forecasts are stored in PROW table. But it is not indexed by material number. It is indexed by pointer. The link to PROW is through PROP through MAPR. MAPR-> Get Pointer for forecast Parameters (PNUM1)-> Read PROP ->Get Pointer for forecast results (PNUM2)->Read PROW using PNUM2.  RAVI KALYANA SUNDARAM

As you can see I started with the MPOP_V table, but after consulting Google  I changed that to MAPR.

Ok let’s fetch the data from SAP and import it into our  Data Warehouse.

There is no problem to pick up all rows for a product or spare part, the problem arises when you download all forecasts for a plant. Using my PHP job scheduler  I quickly set up jobs to extract data from MAPR using RFC_READ_TABLE. To test extracting via a pointer (PNUM1 and PNUM2), I downloaded about 137,000 rows with RFC_READ_TABLE from PROP selecting on date.

But how to extract the corresponding rows from the PROW table? Here I only could select on PNUM2. Our PROW table contains a hell of lot of rows. I decided to to play a little with RFC_READ_TABLE and parallel process selection on PNUM2 from the 137,000 rows PROP table.

First I created a job genArray  that creates a selection predicate for each PNUM2 in the PROP table.

select distinct concat("PNUM2 EQ '",PNUM2,"'") as TEXT from @C_DB.FCC_PROP order by PNUM2;

Then I split the result into chunks that SAP program RFC_READ_TABLE can munch. I decided 10,000 ‘ PNUM2 or predicates ’ was a reasonable chunk to send over to SAP. The program mergesplit_array.php  in job splitArray  takes the result array from genArray and chops it up into 10,000 (2 * 5000) chunk arrays that we feed into the <forevery> job iterator in job getPROW . Here you can see the schedule with the jobs defined.

The SAP forecast schedule. The splitArray job generates arrays with SQL predicates use in the getProw job.  

The split of the SQL result array is not a standard function so I had to write the mergesplit_array.php program used in job splitArray , here it is.  

I removed top documentation and compacted the code a bit, as you see the processing code is quite small.

Now I only had to run the schedule. With a cold cache it takes about one minute  to extract 1,629,660 rows  from PROW and insert them into a Data Warehouse table by 14 parallel tasks each of them with an SQL where clause with 10,000 ‘PNUM2=n OR ’ predicates.  

I consider this test extremely successful. Now we only have to denormalise the MAPR,PROP and PROW tables and create one usable history file and load that forecast history into a graphical user interface and create some snazzy graphics. It will take me an hour or two to create a proper production schedule out of this once the users decide they want this. Then we create a Qlikview  app for presentation. If and when this is done I post some graphics showing forecast history diagrams.  

More examples can be found here .