Forums
Some time ago Martin F. was sent in by his IT consulting company to help fix some problems with the HR Database at a major European banking / insurance firm. He admits that the WTF worthy warning signs were there at the onset (among them being that he was the 4th in a series of consultants assigned to this project), but being relatively naive to such things, he accepted the position and spent a year shaking his head in bewilderment and, at the same time, his fists at Rob. He was an HR 'specialist' and a true IT genius who had a self-proclaimed hobby of programming in Visual Basic and was, of course, long gone from the corporation.
The HR Access-pool
The problem database held monthly snapshots of information about all the (over 50,000) employees of the bank, such as their names, DOBs, home address, function, fixed and variable salaries going back about 4 years. Over that period of time, it had expanded to an impressive 2 Million records in size which is not unheard of in any large corporation, but your typical "Select and Group By" query on 200 people took about eight minutes. However, as Martin came to discover, this was mostly due to the fact the corporate HR database was in reality an Access database sitting on some network fileshare.
Feeding-wise: every month, some 100 local providers from daughter companies would fill-in an Excel-based tool (created by the infamous Rob) that exported CSVs and send them by e-mail to the manager of the application. He would, in turn...
1. Process every file through a series of checks implemented in Ms Access. That would export new "Verified CSVs"
2. Consolidate all the verified files into another Access database
3. Find and remove people who appeared twice and do the necessary corrections (to avoid them being counted as multiple FTEs). Overwrite the "verified CSVs" with the result
4. Import the CSV's into temporary tables of the master database
5. Perform some more checks and corrections
6. Consolidate the temporary data into the master tables
7. Export all the valuable statistics to Excel files for those who needed them (mainly the financial reporting).
This song and dance took 2 people a week to accomplish. After that was done: leave 3 days for feedback and corrections from the providers, then go to step 1 and repeat the process for the "final" version.
Ol' Teller
One of Martin's first tasks was to correct some flaws in the Excel-based input tool (which every provider had a different version of since problems were dealt by e-mail on a case-to-case basis). Under the spreadsheet's ordinary looking thin veneer was a writhing dung heap of undocumented VBA code and WTFs galore that could possibly stand on its own as a separate CodeSOD submission, but for Martin, one procedure stood out as being truly a case of doing something completely wrong.
Dim j As Integer
Dim is_empty As Boolean
Dim teller As Integer
teller = 0
Dim curr_cell As String
For j = 1 To lLastCol
curr_cell = Trim(Replace(sh.Cells(iRowNr, j).value, Chr(10), ""))
If curr_cell <> vbNullString Then
teller = teller + 1
Else
teller = teller
End If
Next j
If teller = 0 Then
is_empty = True
Else
is_empty = False
End If
teller = 0
If is_empty = true Then
Row_Is_Empty = is_empty
Else
Row_Is_Empty = false
End If
is_empty = False
teller = 0
End Function
[Advertisement]
BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!