Meeting with a colleague

It seems that I didn't mention this during October, when I was working heavily on my literature review, but one of the papers on user satisfaction didn't seem to have a journal reference (it turns out that I was looking at a pre-print which has yet to be published). I looked at the list of authors and realised that most (if not all) of them are affiliated with Ben Gurion University in Israel. I contacted one of the authors only to be told that she was abroad and to try again in a few weeks.

Once contact was made, we discussed a few things and made a tentative date to meet. That date got postponed a few times but was kept yesterday. Ben Gurion University is in north Be'er Sheva and has a railway station within easy walking distance. Although there is no direct train from Bet Shemesh to Be'er Sheva, it's very easy to make the journey, which lasts only 80 minutes (including a 15 minute wait between trains). Thus yesterday I worked until lunchtime, drove to the railway station, caught the train and arrived at BGU an hour and a half later. I met the author (who is a part-time lecturer at BGU) and had a riveting one and a half hour conversation. Then it was back to the railway station and home by 5:30pm.

One of the conclusions from the discussion (which was by no means centered around my research) was that almost all research into user satisfaction and/or user resistance has taken place in companies moving from non-ERP to ERP systems. I want to look at more mature implementations, a subject which seems not to have been examined. An interesting point arose when I mentioned that earlier that day I had spoken to a new employee about training: although the ERP implementation in the company may be mature, most new employees normally start with no ERP experience. So this issue of mature implementations is not the only factor at play, here: the user's experience with ERP seems to be more important.

Another interesting issue came up when I mentioned the possibility of researching some British companies and comparing the results to Israeli companies. She thought that this was a very good idea (not necessarily for the doctorate but for a later paper) but suggested that this be carried out by means of a qualitative case study which could require researching only two or three companies in each country. This idea has 'game changing' potential but I think that it should be left aside for the time being.

We were talking about English comprehension and the difficulty that she has in preparing a paper in English, even though she knows exactly what she wants to write (maybe I can help here). Anyway, she mentioned a nice joke (which is funny to the cognoscenti): in her course on ERP implementation, she mentions that one of the important factors is change management (knowing how to manage change). One of her less capable students wrote in an exercise that an important factor in ERP implementation is changing the management - he understood 'change' to be the imperative form of the verb 'to change' and not the noun 'change'. It would seem that the lecture in which this topic arose was held in Hebrew but that the concept appeared in English, for otherwise it's difficult to understand how the student misunderstood. OK: it's not a funny joke.

[SO: 3693; 3, 15, 36
MPP: 574; 1, 1, 6]

Enron's spreadsheets

Remember Enron, the American oil company which cooked its books for several years, showing profits which didn't exist in reality, etc? Well, it transpires that as part of the court case against them, all the company's emails were sub poena'd. This material has formed the basis of a study of their spreadsheets.

I wrote seven months ago (it seems like a lifetime ago) about discovering the European Spreadsheets Risks Interest Group and being in contact with one of their number (Dr Felienne Hermans). Yesterday I was looking at her blog and read her account of discovering the 'Enron Corpus'. Apart from the blog itself, there is also a link to an academic paper which I found fascinating.

Following is data extracted from Dr Hermans' analysis with regard to formulas:

Description	number
Number of spreadsheets analyzed	15,770
Number of worksheets	79,983
Number of non-empty cells	97,636,511
Average number of non-empty cells per spreadsheet	6,191
Number of formulas	20,277,835
Average of formulas per spreadsheet with formulas	2,223
Number of unique formulas	913,472
Number of unique formulas per spreadsheet with formulas	100

and with regard to functions:

Rank	Function	# Spreadsheets	Percentage
1	SUM	6493	72.0
2	+	5571	61.8
3	-	4866	54.0
4	*	3527	39.1
5	/	3112	34.5
6	IF	1827	20.3
7	NOW	1501	16.7
8	AVERAGE	879	9.8
9	VLOOKUP	763	8.5
10	ROUND	606	6.7
11	TODAY	537	6.0
12	SUBTOTAL	385	4.3
13	MONTH	325	3.6
14	CELL	321	3.6
12	YEAR	287	3.2

In case this table isn't clear, it means that 72.0% of the spreadsheets which used functions contained the 'SUM' function. The insight that I derive from this information is that the part of my research questionnaire which deals with spreadsheet competence needs to ask about the 'SUM', 'IF', 'AVERAGE' and 'VLOOKUP' functions in order to cover the most highly used functions. I looked at the questionnaire over the weekend and there are questions about the first three; I should add a question about VLOOKUP to cover myself.

The second insight from the analysis is that employees of Enron were sending spreadsheets back and forth, or as Dr Hermans puts it, about 100 emails with spreadsheets attached were sent each day, so we can safely conclude that emailing spreadsheets was common practice at Enron. This practice is known to result in serious problems in terms of accountability and errors, as people do not have access to the latest version of a spreadsheet, but need to be updated of changes via email. Presumably I will have to ask a question such as "How frequently do you work on spreadsheets which are passed back and forth between your colleagues?" in order to quantify this behaviour.

I also found yesterday an interesting paper on measuring cognitive style in students of accountancy; the authors note that cognitive style influences how information is acquired and utilised during problem solving whereas cognitive ability determines how well an individual performs. The authors determine on a sample of 138 accounting students that students’ cognitive ability had a higher impact on their performance than cognitive style. There is a significant interaction between students’ cognitive styles and the cognitive strategy demanded by the accounting task: students with both global and sequential styles performed better when the cognitive demands of the task matched their cognitive style. It is not clear how these findings can be extrapolated to ERP users.

[SO: 3683; 2,15,36

MPP: 574; 1,1,6] 

Matching a computer language to the problems it needs to solve

For the last few days, I've been looking for material about the Sapir-Whorf hypothesis (aka linguistic relativity) which states that people's thoughts are shaped by the language they speak. This is why, supposedly, Eskimos have fifty words for snow, the British have twenty words for rain and the Israelis have thirty words for taxes. I haven't found anything promising yet, but one hint did lead me to Paul Graham's book "Hackers and Painters".

The actual quote is "Our ideas about what's possible tend to be so limited by whatever language we think in that easier formulations of programs seem very surprising". This isn't exactly what I'm looking for. Elsewhere, Graham writes that the Prolog language "has fabulously powerful abstractions for solving about 2% of problems, and the rest of the time you're bending over backwards to misuse these abstractions to write de facto Pascal programs".

I feel the same about the programming language contained in Priority. I showed only a little of it in my previous blog, but I have known for a long time that certain operations - getting required data from a table or updating tables - are much easier to program in SQL than they would be in any other language, whereas supposedly basic operations such as looping and conditionals are on the level of assembly language.

Another Priority interface - multiple import files

I had to write another interface/importing program for Priority. In this case, an external optimising program creates several output files, each containing the id and quantity of parts (wood sheets) which have to be cut. There are three problems to be overcome (stated in order of their occurrence, not in order of importance):

1. There could be many files per day
2. The files are in Excel whereas Priority reads tab delimited files
3. An interface has to be created within Priority

Problem 3 is near enough the same problem as the one which I solved a few weeks ago. Whilst writing such interfaces are new for me, they're quite straight-forward once understood, and the new interface didn't really provide any challenges which I hadn't overcome the previous time (there are one or two innovations but very minor).

The first two problems occupied me the most. As it happens, the other day I found tucked away at the back of the Priority Programming Manual (yes, there is such a thing) a routine for finding all the files in a given directory (I remembered reading about this); what I didn't remember was that the routine shown also loads the files into an interface, which was exactly what I needed.

But before I could deal with the Priority problem, I had to deal with converting an Excel file into a tab delimited file. After looking up similar questions on the Internet, I cobbled together some code which (via automation) started Excel, loaded the required file, determined how many rows and columns the file had, then created a text file in the required format. After playing around with this for about an hour, each time receiving an obscure error message which resulted in Excel staying in memory, I realised that I had been over-engineering. I could simply tell Excel to output a tab delimited file! Then I had problems with the name of the file, but those were easily overcome.

Here is the Priority code as it was yesterday (derived almost without change from the example)

    /* load a list of files from directory DIR into table ST6 */
    EXECUTE FILELIST :DIR, :ST6, :MSG;  
    LINK STACK6 TO :ST6;
    DECLARE FILES CURSOR FOR
    SELECT NAME FROM STACK6 
    WHERE NUM <> 0;
    OPEN FILES;
    GOTO 300 WHERE :RETVAL <= 0;       
    LABEL 100;
    FETCH NAME INTO :NAME;
    GOTO 200 WHERE :RETVAL <= 0;       /* no more files */
    :MYFILE = STRCAT (:DIR, '/', :NAME);
    /* call my Delphi program to convert XLS to TAB */
    EXECUTE WINAPP 'X:\SYSTEM', '-w', 'XLS2TAB.EXE', :MYFILE;  
    /* do something with the tab delimited file */
    LOOP 100;
    LABEL 200;
    CLOSE FILES;
    LABEL 300; 

Ignoring the low level language which is required for programming in Priority, what the above code is doing is getting a list of files in a given directory, then the filter program is called for every file in that directory. I would then have to manipulate the filenames, so that instead of 'file.xls', I would have to input 'file.txt' into the interface. One way of doing this would be to delete each xls file in turn, then call the FILELIST program again to build a new list of filenames.

It occurred to me yesterday evening that there was a better way to skin a cat. This version involved writing a filter program which converts all the xls files in a directory to txt. This approach is going to be faster for two reasons:

1. the external filter program will be called only once instead of once per file
2. Excel will be invoked only once instead of once per file.

The filter program is exceedingly simple:

$APPTYPE CONSOLE}

uses SysUtils, comobj, ActiveX, variants;

const
 xlText = -4158;

var
 i: integer;
 xlApp, wb: variant;
 rec: tsearchrec;
 fn, s, dir: string;

begin
 if paramcount = 1 then
  begin
   CoInitialize (nil);
   xlApp:= CreateOleObject ('Excel.Application');
   xlApp.visible:= false;
   xlApp.displayalerts:= false;

   dir:= IncludeTrailingPathDelimiter (paramstr (1));
   if findfirst (dir + '*.xls', faAnyfile - faDirectory, rec) = 0 then
    repeat
     fn:= dir + rec.name;
     i:= pos ('.', fn);
     s:= copy (fn, 1, i) + 'txt';
     wb:= xlApp.workbooks.open (fn);
     wb.saveas (filename:= s, FileFormat:= xlText, CreateBackup:=False);
     wb:= unassigned;
     deletefile (fn);
    until FindNext (rec) <> 0;
   FindClose (rec);

   xlApp.quit;
   xlApp:= unassigned;
   CoUninitialize;
  end
end.

I still had to use the FILELIST command in Priority, but only once; the conversion from xls to txt had been moved out of the loop.

    /* call my Delphi program to convert XLS to TAB */
    EXECUTE WINAPP 'X:\SYSTEM', '-w', 'MXLS2TAB.EXE', :DIR; 
    EXECUTE FILELIST :DIR, :ST6, :MSG;  
    LINK STACK6 TO :ST6;
    DECLARE FILES CURSOR FOR
    SELECT NAME FROM STACK6 
    WHERE NUM <> 0;
    OPEN FILES;
    GOTO 300 WHERE :RETVAL <= 0;   
    LABEL 100;
    FETCH NAME INTO :NAME;
    GOTO 200 WHERE :RETVAL <= 0;       
    /* do something with the tab delimited file */
    LOOP 100;
    LABEL 200;
    CLOSE FILES;
    LABEL 300; 

This new version works like a charm - poetry in motion.

---

Overnight, I had some more ideas on how I can improve the execution speed of this program, moving operations from Priority to the Delphi program. It would be interesting to know whether it takes the same amount of time to delete a file via Priority as it does via Delphi. Unfortunately, the clock resolution in Priority is only minutes - not seconds and certainly not milliseconds. Also, one can only measure 'wall time' as opposed to CPU time, so any measurement is going to be inaccurate.

My basic idea is building one big tab delimited file with the Delphi program then passing this to the interface, as opposed to passing several small files. Technically this should reduce overhead but I don't know whether the saving will be worthwhile. Also, it forces the Delphi program to be less general than I had originally intended. 

I've just checked how long it takes to process eight small files - less than a minute. It's not worth my time making this faster. Unfortunately, I won't get the two hours of lost sleep back (I woke up at about 11:30pm with the ideas but couldn't get back to sleep till about 2am).

Navigating by machine

As part of my extra-curriculum doctoral activities, yesterday I participated in a morning seminar aimed at SMEs. This was held in Ra'anana, 60km away, in a location with which I am unfamiliar. In previous times, I would have gone to Google Maps (or similar) and printed out a map or route which would lead me to the location (I know how to get within 5km of the location, but no closer). But now that I have a smart phone, I thought I should be like every other driver in Israel and use the 'Waze' application. This app is more than an a  GPS/map; it 'knows' (by means of other users) which is the best/fastest/shortest route to the location and 'knows' where there are problems (such as traffic jams, etc).

I downloaded the app and after a few minutes incomprehension, discovered how I could enter my location and my destination. The app gave me three options for getting to my destination; I chose the fastest. When I got into the company car, I discovered that this was fitted with a GPS, so out of curiosity, I entered my destination into this GPS. At first, I was getting directions from both devices, but as I knew at least the first half of my journey, I turned Waze off.

I turned Waze back on when I was on the eastern side of Kfar Sava. At first, the two apps agreed on how I should travel, but then suddenly Waze told me to turn off the major road and on to a side road, presumably to slide around a local jam. The car's GPS hummed for a minute then recalibrated. Waze let me a jinking dance around the back streets of Kfar Sava which might have saved me some time; the poor GPS of the car was having a nervous fit at what appeared to be my blatant disregard of its orders. The best bit came towards the end when Waze said to turn left after 200m whereas the GPS said to turn right after 800m!

In the end, I arrived at my destination - after an hour and a half, when I had been promised 65 minutes. 

The seminar had some interesting sessions - one on negotiation and one on modern marketing (ironically, two MBA subjects) - but the one on ERP for SMEs (which is probably why I went in the first place) was disappointing and the others were irrelevant for me.

Driving back was very easy as the location was right next to Route 4; I had no need of either Waze nor GPS.

What I didn't know until the evening was that Waze almost completely drained the mobile phone's battery (it was down to about 25%) and that the phone as a phone wasn't working: I couldn't make or receive calls. I will know for next time.

[SO: 3673; 2,15,36

MPP: 574; 1, 1, 6]

Literature review: second draft completed

I wrote three weeks ago that I had completed the first draft of my literature review. Although I mentioned that it ran to 93 pages, I neglected to mention that it contained about 41 thousand words (including references). I sent it to my supervisor; I received the printed copy back last Sunday (2 November). My supervisor says that the draft is far too long: the review should be about 10K words in length.

It took a few days to figure out how I could quarter the size of the draft. The section on previous literature reviews was maybe eight pages long; by cutting out all the details along with my critical comments, the new version was a touch under two pages long. It was more difficult to understand the section discussing case studies on ERP implementations, but one day the penny clicked. Instead of discussing paper by paper, I should take the six research questions about companies from my research proposal and discuss the studies in the context of those questions. Of course, I had to prune the text drastically.

Once I had finished that, I could devote myself to editing the very long section of psychological issues. I had found seven different constructs (such as cognitive style, perceived management support and self-efficacy), then for each construct I presented a definition and whatever papers that I could find on the subject and ERP. After a few evenings of editing, I was able to reduce this section in size.

The second draft contains just over 22K words, including the bibliography. There's been a drastic reduction in size (I wish I could lose weight in the same way that I have lost words) but it probably needs to be reduced further in size. I could ignore the bibliography (2.8K) and say that the first section is a history of ERP and not really part of the literature review; this would reduce the word count to a mere 14K words.

Hopefully I won't have to wait two weeks to receive the supervisor's response. Apart from the fact that I am left bereft of doctoral work, these gaps eat into my proposed timetable.

Undoing textual changes

I frequently get asked, normally in a programming context, whether I can do such and such a thing. If the solution is clear, then I will answer yes, otherwise I will answer no. It has been my experience that a 'no' can be turned into a 'yes', but a 'yes' can never be turned into a 'no'. I had a case of this yesterday, when the Occupational Psychologist asked whether it would be possible to undo changes in a text field in a certain screen in the management program. 

My immediate answer was 'no' - once a change has been written to the database, it can't be undone. "But Word has an undo feature", she said. "Yes", I replied, "but it only works as long as the file has not been saved. After it's saved and you exit Word, the changes get lost". Today I saw a way of turning that 'no' into a 'yes'.

The 'comments' table in the management program has several fields, but only three interest us: one is the primary key, an autoincrement; one is the foreign key to its owner (normally a docket), and one is the actual text. I saw that if I created an 'oldcomments' table with four fields (autoincrement primary key, foreign key to the original comment's primary key, date and text), I could create a backup of the comments and allow restoration.

Here is the explanation as to how it works. The first time that a comment is created, it is written to the 'comments' table. Obviously, there is no backup. The second time that the comment form is accessed, the original text is saved; if the comment is edited and saved, then the new text gets saved to the 'comments' table whereas the old text, along with the comment's id, get saved to the 'oldcomments' table. The third time that the comment is accessed, a new 'restore' button is visible. Pressing it would display a dialog with all the saved texts for this comment - at the moment, only the original text would be displayed. The user has several options: she can choose to restore this prior text, she can edit the current text (and in so doing, save another version in the 'oldcomments' table) or she can reject any changes and exit silently.

The actual program code is not particularly fancy so I am not displaying it here.

I have added a little code to delete all 'oldcomments' entries which are more than 30 days old. This code may be removed in the future. Whilst it is intended to save space, it also seems pointless to me to save text for such a long period: normally an 'undo' will be performed the same day or the day after the text was saved.