PHP 5.4 at last

I often compare software with perishables like milk. Who wants sour milk? Mostly new software versions are better than the previous one. Not utilising new features is wasting money if you ask me. Of course you have to test new versions well before deployment, that goes without saying. This is especially true if you like me are prepared to throw Beta versions into production, and yes I have thrown in Alpha versions too. But only after careful and tedious testing. Not only can you give your audience a better user experience sooner, using bleeding edge software will give you a learning experience and sometimes direct contact with the developers.

I have used all beta versions of PHP since 2001 in production. During the years I have submitted about 5-6 bug reports of those about 2 have classified as bogus, 1 or 2 was fixed (in hours) I reported late night and had a fix in early morning, for the rest of bugs I was not able to give enough info, I’m sorry for that. I learnt a lot from my testing with early alpha releases. Of course you should only put in non-production releases into production if you are prepared to take the full responsibility for failures and burn a lot of midnight oil. It can be very lonely when  you screw up, and that is when  not if .

Anyway this was not the case with PHP5.4, only recently I finally put PHP5.4 (11) in production. I have had problems with SAPRFC and SAPNWRFC. They were not compatible with PHP5.4. SAPRFC I could fix myself, but SAPNWRFC was a too hard nut for me to crack. But recently I found out that the creator and maintainer of SAPNWRFC Piers Harding  had fixed the incompatibilities, so I just downloaded and compiled, tested ( I found one PHP bug, more of that later) and deployed PHP5.4.11 in production beginning of this week.

Thank you so much Piers and Axel Bangert  new maintainer of SAPRFC who have fixed the ‘official’ version of SAPRFC.

In the coming weeks I will upgrade to PHP5.4.12 and start work with PHP5.5.X, I’m especially interested in 5.5 generators, since my PHP job scheduling system ADAP utilize iterators a lot, hopefully generators can be of use.

I think I found a bug in PHP5.4.11, unfortunately I cannot bug report in PHP5.4.11, I have to upgrade to PHP5.4.12 first.

PHP5.4.11 thinks my array is a string and convert my string index to ‘0’ here it is:

This piece of code:

 

Gives the following result:  

The array index $ci=’LOTSIZE’ trigger the buggy warning message and the invalid result $nval=”” when directly indexing the array >>  $fce->$sapTable[$ci]  <<.  

Offloading the array to $nvalarr before indexing with ‘LOTSIZE’ gives the correct result $nval=16.000. Now I missed this error in my tests, we only found this in production. If you are a production planner you know the implications of nullify LOTSIZE, fortunately we stopped this before much harm was done.  

Apart from this small bug PHP5.4 works like a charm for me, so PHP5.5 here I come :))

UPDATE!

I found another possible bug in php 5.4.13

Business Intelligence Ping Pong

This morning I had this request from a user.

- I need a list of all materials in our plant with purchase prices. Can you fix that?

- Sure

select matnr, purch_price, currcd for materials

I sent the Excel by mail.

-But I need this for all products with quantity?

- You want a BOM exploded list?

- Yes

- Oki

select

a.product,a.component, a.qty, c.purch_price, c.currcd.

from bom_tree a inner join materials  c

on a.component = c.matnr

order by a.product,a.component

I sent the Excel by mail.

-Hello again, I also need lotsize, can you add that

select

a.product,a.component, a.qty, c.lotsize, c.purch_price, c.currcd.

from bom_tree a inner join materials  c

on a.component = c.matnr

order by a.product,a.component

I sent the Excel by mail.

- This is a nice report, but where is the price in swedish crowns?

- There is no price in swedish crowns.

- I also need the purchase price in swedish crowns using company year currency rate, can you add this?

- Shure

select

a.product,a.component, a.qty, c.lotsize, c.purch_price, c.currcd

,round(coalesce(c.purch_price * d.factor,0),2) as 'PRICE_IN_SEK'

from bom_tree a

inner join materials  c on a.component = c.matnr

left join cur_rate d on d.f_currcd = c.currcd and d.t_currcd = 'SEK' and d.year = '2013' and d.ratetype  = 'ACAB'

order by a.product,a.component

I sent the Excel by mail.

-Hello again, I like the report but I also need unit price. Can you add that?

-Yes

select

a.product,a.component, a.qty, c.lotsize, c.purch_price, c.currcd

,round(coalesce(c.purch_price * d.factor,0),2) as 'PRICE_IN_SEK'

,round(coalesce(c.purch_price * d.factor / c.lotsize,0),2) as 'UNIT_IN_SEK'

from bom_tree a

inner join materials  c on a.component = c.matnr

left join cur_rate d on d.f_currcd = c.currcd and d.t_currcd = 'SEK' and d.year = '2013' and d.ratetype  = 'ACAB'

order by a.product,a.component

I sent the Excel by mail.

- Hello, I really like the report, but I also need the description of the component. Can you add?

-Yes.

select

a.product,a.component, c.descr, a.qty, c.lotsize, c.purch_price, c.currcd

,round(coalesce(c.purch_price * d.factor,0),2) as 'PRICE_IN_SEK'

,round(coalesce(c.purch_price * d.factor / c.lotsize,0),2) as 'UNIT_IN_SEK'

from bom_tree a

inner join materials  c on a.component = c.matnr

left join cur_rate d on d.f_currcd = c.currcd and d.t_currcd = 'SEK' and d.year = '2013' and d.ratetype  = 'ACAB'

order by a.product,a.component

I sent the Excel by mail.

-Hello, now I only need the material overhead for the component?

-???

- If you look into transaction CK11 you find  the overhead in there . Can you add it?

This is what I call BI ping pong , and it is not that bad. I’m playing this match now. We paused for the night, and the SQL in here is not the actual SQL  but a slight transcription for clarity . It is iterative development by interaction and it’s fast. When the user contacted me we were not sure what he wanted, but now at least I have a pretty good idea. And we have not spent many minutes on the development. When I have figured out what the material overhead is, this user will come back and say - ‘I want to compare this report with the figures from last year’. And then we already have a very good, tested and working prototype for the OLAP app the user wanted without anyone of us knowing it from the beginning, (the user actually does not know this yet, but I will suggest a Qlikview app tomorrow).

The conventional (and boring) approach; start a project with lots of eventual users creating demand specifications etc. etc. will take longer time, cost more money and deliver a less satisfactory result.    

RFC READ TABLE and count(*)

Sometimes you need to know the amount of rows in a table before you access the table. If your only weapon in your arsenal is rfc_read_table, you have a problem, it can’t be done. I circumvented this limitation by inserting this piece of code:

Instead of returning the selected part of the table the program now returns the row count, if the parameter ROWCOUNT is set to a negative number. The result is returned in the file TABLE_ROWS.CSV. Admittedly this is a crude hack but it does the job. I am not sure of the (copy)rights of RFC_READ_TABLE so I just publish this addition to the program, but it’s a no-brainer to to figure out how to apply the snippet. (You do not have to be a brain surgeon to create the snippet either.)

This is an example how I pick up the rowcount  for the DD03T table:

In the first job I specify a negative rowcount which activate the ABAP code snippet above in my RFC_READ_TABLE and the rowcount is stored in the TABLE_ROWS.CSV file which is picked up by the ‘TABLEROWS’ tag in the subsequent job ‘calcIterator’ job. When you know how many rows there are in table it’s easy to download the table in chunks. And that I show in another post.

 

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 ’.

PHP, MySQL, Iterators, Jobs ,Templates and Relation sets .

Other examples .

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.

 

 

Planning the next IT year.

When I started my present employment (year 2000) I promised myself never again take on operational responsibility. Well, I kept my promise a week or two then I sat there again burning the midnight lamp fixing a corrupt database in an MRP system I had created 1980, that system is now scrapped, but now I’m  heavily involved in the operations of our BI system the Data Warehouse. I’m also involved in some other systems that needs some instant tinkering from time to time. Operations is the most important factor when I set up my calendar. Meetings is the second most important factor. To me it seems meeting is getting more and more important or frequent. I do not know if this is just a fact of my professional role is changing or if this is a more general trend.

The third most important factor for my calendar is projects. This is definitely a trend, more and more work are done as projects. When I started to work with IT most work was assigned to  individuals, only big ‘projects’ were actually organized in projects. Today no task is too small for a project organisation.

Vacations is something you have to take into consideration when planning the year. In western europe most of us take a month off in the summer. (In Sweden five weeks vacation is the minimum, I think I have six weeks. I remember when I was employed in the US I was given three weeks vacation since ‘you are from Europe, but please do not tell anyone’.)

Basically I use three cycles when I plan my next years calendar. First we have the year itself, then the month and last the week.

Week cycle.

Monday mornings is reserved to operations if anything gone wrong during the weekend. Monday afternoon project meetings, so project members can plan their week, the earlier in the week the better. Do operational changes on Mondays, so you can fix the problems on Tuesday. Wednesday is ‘meeting day’ experience have taught me operations problems are least likely to occur on Wednesday, so plan for important repeating meetings on Wednesday. Thursday small operational changes and ‘unplanned’ meetings, seminars etc.. Never change operations on Fridays, (not even a simple program bug fix if it can be postponed to next week) unless you want to work during the weekends. Fridays are for those boring sometimes unnecessary meetings you cannot avoid. Note the later a Friday meeting is held the shorter it will be. At 16.00 even a hopeless filibuster often keep quiet. Weekends lastly are for big operational tasks.

Month cycle.

First day of month is Business Intelligence day. The BI system must be up and cope with peak load. If the system is of any magnitude and have active users there are new/changed reports, it’s very likely there are problems to deal with. Do not plan for anything the first no matter what weekday it is, your BI system may need you. The second and third week in the month are the quiet weeks plan for repeating events and important meeting. Last we have month period closing that starts four days before month end and lasts to the third day of the next month. During this time the financial guys become hysterical if they do not get all attention from you (and everyone else for that matter).

Year cycle.

The year starts with what I call the ‘Iron day’, in my younger days I took a taxi from the new year party directly to the office and after a barrel of coffee I dealt with the year end’s problems. Nowadays I try to sleep a few hours before I log in from home and deal with year end’s problems. The ‘Iron day’ is the single most important IT operational day and it is an effort of will. Be prepared for anything. (Three years ago our VPN-system broke down so I could not log in at 06.00. I had to take a taxi to the office as in the old days. A software license had expired).

Be cautious with planning to much in the beginning of January, there might be difficult year end’s problems to deal with.  

February is software license renewal month. (Never ever have software licences expire in December, January or during the vacation period.)

For us the summer vacation period is July and August (in Sweden it is July and in Belgium it is August). Avoid plan anything during the vacation period. You should not start up anything new two weeks before the vacation period, and one week after the vacation period.

The most IT-critical period of the year is the year end. Do not start up any major IT-finance projects from mid november until mid January, the finance guys will be absorbed by year end. Declare ‘production freeze’ from second week in December to second week in January, only allow necessary maintenance in the IT landscape and infrastructure.

When these three cycles are firmly booked in the calendar you only have to mark up holidays like Xmas and easter and bank holidays and your calendar is ready for use and you are prepared for planning the next year.

Office activity peaks and Business Intelligence users

Every Database Administrator knows there are work related activity peaks during the day in the office. You have a long peak of activity shortly after most workers arrive in the morning and another shorter one before lunch. After lunch there is an hour of intense activity and a short burst of activity at the end of the day. In between those two peaks not much is happening. How do I measure? In the ERP systems of course, when we work we tend to use the ERP systems, so to measure the work activity rate just calculate the ERP transaction rate. As you may have noticed I’m vague in timings of the peaks. If you have observed activity peaks in different countries you know activity  differs between countries.  I will not mention more about that other than Swedes tend to come in early in the morning, I myself is a bit extreme I often start between 05.30 and 06.00.

A good friend of mine and ex-colleague Ulf Davidsson co-creator of the Data Warehouse used the Business Intelligence system activity to categorise BI-workers into ‘ doers ’ and ‘thinkers ’. The doers start early in the morning and pick up the reports they need for their day tasks. The thinkers tend to come in later their activity peak coinciding with the after lunch peak. The classification into doers and thinkers are quite useful. Doers need precise detailed but rather limited amounts of information in Excel format, while the thinkers are more for quantity ‘give me all, I want all goods movements in all factories since the days of Eden’ and they prefer olap  presentation of the information, today we use Qlikview  for olap visualization. The doers request 100% quality of data, while thinkers demands performance. (The first thinker using the Data Warehouse Styrbjörn Horn  forced me to upgrade the hardware, at the time we were operating on scrapped laptops, ‘I go bonkers while waiting for my reports’. Styrbjörn later founded his own company Navetti .)

A common mistake creating BI is to create the BI for thinkers, since they seldom have detailed knowledge of data, they assume it is correct and when they finally find bad data quality they can be nasty to deal with. When you create a BI application you should find the doers that benefit from the system and start by create reports for them, if they find the reports useful they will help you iron out quality issues. (You should base your BI on solving problems rather than visions and grand ideas.) When the application is stable with correct data it is time to invite the thinkers, if they find your application useful they will help you with the performance issues. Thinkers often have a budget and they can be surprisingly generous if they like your BI system.

This post is based on generalized observations, there are swedish nighthawks and early birds from other countries. We do not only work in ERP and BI systems. BI doers can also be thinkers and vice versa.

I have no connections to those behind the olap video link, I stumbled across it and I like it, especially the brick wall, to create successful BI you must be in the business.

My only connections to Navetti are professional, we use their software and ‘my’ systems feed their software with information.