SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Uncontrolled Code


Uncontrolled Code

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64037 Visits: 19117
Comments posted to this topic are about the item Uncontrolled Code

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87946 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
andrew.robertson
andrew.robertson
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 81
Quick someone pop round to Steve's house I think a troll has him! :-)
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7831 Visits: 3290
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.

LinkedIn Profile

Newbie on www.simple-talk.com
andrew.robertson
andrew.robertson
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 81
Now BI is an oxymoron! Please close this thread before it gets too stoopid!
Gary Varga
Gary Varga
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16681 Visits: 6534
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!!!
Yet Another DBA
Yet Another DBA
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 1234
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.
Gary Varga
Gary Varga
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16681 Visits: 6534
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!!!
chris.smith 91049
chris.smith 91049
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 50
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.
Dalkeith
Dalkeith
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 1110
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search