Uncontrolled Code

  • Comments posted to this topic are about the item Uncontrolled Code

  • It's not Excel, spreadsheets, or anything else in particular. It's crap code and human error in general. I just saw an article yesterday where a couple got a snail-mail address to "Daughter died in car crash or current occupant". Obviously, someone typed something into the wrong field on some data entry screen somewhere.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Quick someone pop round to Steve's house I think a troll has him! 🙂

  • It's not just Excel, it's BI in general.

    Does BI development go through the same rigour that application development has to? I seriously doubt it!

    Excel is particularly prone because its greatest strength is its greatest weakness.

  • Now BI is an oxymoron! Please close this thread before it gets too stoopid!

  • In my experience many Excel solutions are business unit driven where I and other developers are brought in when it becomes untenable to keep Excel as the solution. As can be expected the common issues are too many concurrent users (some would say more than one is too many), data protection requirements, regulatory and legal compliance, central control of systems and data, central enforcement of minimal standards for application support (e.g. data backup, source control, availability, disaster recovery etc.).

    Many of these Excel based solutions have been reasonably accurate, however, most have resolved some erroneous data issues. Some of which have been financial in nature. Having said that this continuous cycle of business units developing Excel solutions and their replacement with managed applications probably means that the financial skew of a any business with this model of application development is almost a constant. If the skew isn't too great or the business too fiscally weak then I can imagine that it is probably only out as much as any large business' accounts.

    I would not say the same for financial institutes or financial service companies. Here a skew is likely to be disproportionate to the finances of the business by comparison.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • I have seen Excel being used by an insurance actuary and then someone promised that the same equations could be done in a "proper" language and a "proper" client rich application. Over 2 Weeks later 2 developers managed to get the sub 1 second accurate excel spreadsheet calculation to a staggering 10 seconds with bugs in the calculation.

    This I say would be typical of many companies where IT tries to gold plate a (*&* and tries to impress the company to justify their budget. It is little surprise that the many business units dont want IT development involved.

  • Yet Another DBA (1/21/2014)


    I have seen Excel being used by an insurance actuary and then someone promised that the same equations could be done in a "proper" language and a "proper" client rich application. Over 2 Weeks later 2 developers managed to get the sub 1 second accurate excel spreadsheet calculation to a staggering 10 seconds with bugs in the calculation.

    This I say would be typical of many companies where IT tries to gold plate a (*&* and tries to impress the company to justify their budget. It is little surprise that the many business units dont want IT development involved.

    Yep. Seen that too...now who's turn is it to say "It Depends"?

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Well, that sounds a little biased - Dev teams aren't just out to justify their budgets. Quite often they act sincerely where they see bad practice. The fact that the spreadsheet can work in under a second is completely irrelevant if it's got bad code or bad practice (such as unnecessary and risk-laden human intervention required.

    If humans have to cut and paste info into a spreadsheet, or worse, transcribe it, they are 100% certain to make a mistake one day: missing some cells, getting a decimal point in the wrong place etc.

    Working in BI for years, I've seen End User Computing (EUC) of a wide variety of quality, some of it is amazingly complex for an untrained user to have come up with, and while I have a lot of respect for that - they're often riddled with mistakes - from the simplest rounding errors to logic errors that would only occur in rare circumstances and take days to track down.

    However, the biggest problems with EUC are:

    Lack of independent testing. If there is any rarely do any of the best practices in testing get done, particularly regression testing, and there is usually no formal testing stage.

    Lack of code re-usability, modular design etc: leading to different parts of the business using different business logic and getting different results, or changes in business logic not being universally applied, and also a massive overhead once spreadsheets start 'spawning'.

    The Dunning-Kruger effect: good to see that mentioned in the UOH article - The amount of arrogance from senior managers when it comes to their EUCs is overwhelming, sometimes it feels like you're criticising their children's looks.

    Managing change: related to code reuse, but having gone through several phases of redundancies; mergers, data migrations, and - working in the finance industry - regulatory changes, the hardest part is tracking down the sometimes 100s of independently developed processes and analysing them - they rarely have design documents, and if they do, they're well out of date as several different users have added and changed them. They have no test plans and no version control.

    Yet, every company I've worked at has known the problem, and yet still produces them - the problem is that the manager is looking for a short term solution and doesn't want to waste his time specificing the problem and helping with the design of a good system. It's hard to get them to see the big picture - and if we're telling her that we can give her a solution in 8 weeks time and she needs some figures for Friday, she'll do it anyway, then after a few weeks it's become part of their business process and they can gradually add new parts, and she's not interested in our permanent replacement. Like the frog in the pan of hot water, she's not noticing the incremental additions and manual work going into that EUC.

  • This is interesting

    I'm probably in the "I don't think Excel is the problem" camp. Excel does far more good than harm and its only the fact that it is often the only tool that is available to people that results in the crazy situation of things like it being used as ledgers.

    In my experience if you give a design environment to a department the smartest individual super users will eventually teach themselves and become highly useful. These kind of individuals will create complicated solutions whatever you do. At least you want to encourage them towards best practice for development and drive them towards your own personal development styles.

    I think this is where things like MS Acces / Fox Pro / Lightswitch etc. were/are excellent. OK you might not get tight solutions but generally they are in the right direction and you can to a certain ensure at least record integrity. These environments actually push them towards properly understanding the idea of relationships even some quite advanced Excel users have not grasped what is a totally fundamental concept. Lets face it Excel sucks for relational information.

    It's the rise of the Dev / Op - for enterprise Data centric applications (and specifically UI) I think we should be encouraging users to be given a simple design environment which pushes them towards understanding the idea of relationships. Chances are it will increase the need for DBAs and professional developers and I for one like it as with increased knowledge I only get called in for the more interesting problems and I start to get asked better more relevant questions.

    There does seem to be a constant battle between IT departments and users where some IT departments believe users should have no development tools. I m not in that camp.

  • I consider them more of an evolutionary prototype where eventually they MAY require a "proper" solution. Depends on all the factors previously mentioned (by me and others) and probably more factors besides.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • "I think this is where things like MS Acces / Fox Pro / Lightswitch etc are excellent."

    Ditto, couple Excel to Access and most of these problems disappear.

  • grovelli-262555 (1/21/2014)


    "I think this is where things like MS Acces / Fox Pro / Lightswitch etc are excellent."

    Ditto, couple Excel to Access and most of these problems disappear.

    Well, it's horses for courses, it depends what they're doing and the risk involved - If you're managing the risk profile of a multi-billion pound/dollar investment bank on a spreadsheet which it outside all normal IT governance and has never been independently tested, is not subject to change control and source control, then when the inevitable problems may well have a big effect,

    Same goes for many many uses of EUCs on a more usual scale: if it's used for inventories of office supplies in an SME, then no problem, if it's used to calculate the bonuses of sales teams, you should probably go through the normal SDLC and not let the work experience kid throw something together.

    If you read that research from the UOH: http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm

    It shows - and we all know this - that even experienced developers make mistake in code. The problem with EUC and Excel in particular, that thos problems don't get picked up because of lack of testing and/or code review - and given the nature of Excell, that individual cells contain their own code and you can't easily see logic errors at a glance - even those good practices don't get picked up. The takeaway from that research is "Spreadsheets WILL have errors and they're hard to find" and the more complex the sheet, the more those errors will compound.

  • chris.smith 91049 (1/21/2014).............. The takeaway from that research is "Spreadsheets WILL have errors and they're hard to find" and the more complex the sheet, the more those errors will compound.

    Chris, all applications will have errors, I haven't found one app that hasn't but still live in hope.

    I have heard that MS Access:

    * has been used for years to provide the fuel calculations for an air fleet of a world leading airline.

    * used to track the sales of $1 billion because it was more flexible than Oracle financials

    Yes, neither of these is best practice and I know that a lot of IT pressure was made to get these changed. But the different IT departments could only promise a deadline that was months/year away which was unrealistic for the business operation and profit margin.

    I would prefer that the logic was in sprocs and constraints but it is only a preference and not the only way of meeting business requirements.

  • I think we often forget that businesses don't exist to use our software or follow our procedures. They exist to deliver a product or service to a customer and, in so doing, make money. If our software and processes facilitate that we can expect the business to engage with us. If we obstruct that the business will turn away... and will be right to do so.

    I think Steve's final sentence was telling. We need to deliver solutions faster than the business unit can develop their own. That might be an all singing all dancing fully integrated system or it might just be sitting down with the user and helping them write the spreadsheet. I absolutely agree that we should put as many development tools as possible in the hands of users and then be there to support them when they need us to.

Viewing 15 posts - 1 through 15 (of 89 total)

You must be logged in to reply to this topic. Login to reply