Tales from the Mortgage Monster: who are we paying today?



  • Another gem from the "land of plenty".

    Every morning, a spreadsheet was emailed around to pretty much the entire company - whether they wanted it or not, whether they cared or not - listing the mortgages that were due to be completed that day.

    Now, I've mentioned before the trials and tribulations of whose case something was. And in theory this spreadsheet was subdivided down by which team a case was with. So you had a tab for 'all the cases' and a tab for each team.

    All the time I was a mail monkey, I didn't care, delete the email when it came in. Then I became a completions team member, and suddenly I started caring.

    And I began to notice quirks between what the list said and what I knew to be the case. Seeing how my filing cabinet had shelves for each day of the week that cases were to complete on, and I knew in advance what I was dealing with.

    The 'all' tab was correct, but the tab for my team wasn't. And I asked questions but was told that the report was perfect.

    Fast forward a couple of years. Now I'm in the audit team - the people who actually prepare this spreadsheet. And so I got to see some of the inner workings... and one day I happened across a copy of it in its entirety.

    I said it was a spreadsheet. It was, in fact, a spreadsheet with a VBA macro in it. Previously it had been password secured, but someone had amended it and not resecured it.

    Inside said macro, I found a login string to the main database system, with a developer level account. And the query it was running to the Oracle database with what seemed like a gazillion tables.

    And then I realised the problem. It was running this same query multiple times over... with different criteria. The criteria was supposed to split it in to the different teams, except it had some interesting omissions that would have led to cases falling through the cracks if the people working the cases weren't diligent in the first place.

    Also, no wonder this report took half an hour to run if it was doing that and dumping it into Excel.

    So on the sly, I rewrote it. I had it get all the data in totality, to the All Teams tab - and this time collected the broker's id and post code (which would be necessary to identify the team responsible), and then proceeded to build a list of postcode->team mappings. Ideally, I'd have done using the fields buried in the database, but no-one would let me have the schema, so I had to figure it out from what I did have. But it was a mess that was left unchecked for years and was only solved by diligence of humans, which is in itself a WTF.


  • BINNED

    I don't know which is worse - reverse engineering a schema from queries, or actually seeing some of those monstrosities.

    Now, you say it's Oracle, which is, from all I heard, a giant PITA, so I'm gonna have at least a modicum of a benefit of a doubt that someone who can tame that beast made at least a half-competent schema.

    ...

    Fuck it, no benefit of a doubt. It sucked more likely than not.



  • The schema from the bits I could see seemed sane. We're talking a sufficiently complex system to run a mortgage account, and I gather that we were in the order of a thousand entities in the system (which may not necessarily have all been physical tables, but some views built out of those tables), so sanity is probably not entirely ridiculous a notion.

    I would have much preferred to do it from the schema itself but as I wasn't part of IT or Ops Dev at this point in time, I had to reverse engineer from what I did have. That part is, naturally, a WTF in itself and I don't mind admitting it - but if I wanted any kind of solution inside 12 months, it was the least unpleasant route.


  • BINNED

    @Arantor said:

    The schema from the bits I could see seemed sane.

    Well, at least you found one sane thing in that place I guess. My experience so far with places that require that kind of very specialized software was... sketchy to say the least.


    Filed under: 40 columns, 2 rows



  • I still have more WTFs to share, most of the sanity I encountered came about because I pushed it to happen.


  • ♿ (Parody)

    @Arantor said:

    Also, no wonder this report took half an hour to run if it was doing that and dumping it into Excel.

    I kept re-reading, but I couldn't see how long it took after you fixed it.



  • I thought that it was SOP to do the same thing in two different places in a similar but slightly different manner.



  • I forgot to mention that, went from 30-35 minutes to under 2 minutes.


  • ♿ (Parody)

    I love it when this sort of thing is possible.



  • It's great that it was possible, but it shouldn't have had to be necessary in the first place.


  • ♿ (Parody)

    Not in a perfect world. In the real world, however, these things start simple and grow over time, often becoming a giant mess that kinda sorta works well enough. And when you're growing the new pieces you don't have the time (or even the awareness or knowledge) to figure out how to make it all elegant and performant.



  • I have no words to explain what happened here. It had all the appearance of being 'designed' rather than 'built because it had to be'.


  • ♿ (Parody)

    That probably means that at some point, someone with at least a partial clue tried to straighten things out.



  • Sadly it was too many years in the past before I got my hands on it. Odd thing was... only a few people had that kind of access - for all the years up to the point I got involved.



  • @Arantor said:

    I forgot to mention that, went from 30-35 minutes to under 2 minutes.

    How many people complained that they can't take extended coffee breaks anymore?



  • None, because they invariably had so much work to do that they would leave it running in the background while getting on with important stuff. The one person that had to run it each day was also in the middle of actually approving funds to be sent out that day.


Log in to reply