January 21, 2014 at 6:24 am
FunkyDexter (1/21/2014)
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.
I absolutely disagree - we don't have a hope in hell of developing faster than users can knock something up in Excel or Access - as long as we're following best practice and designing systems, testing them and reviewing them, practising standard change control practices etc. So it's not a fair comparison.
Obviously, those take up more time than if you don't have to do them, but quite often the quality threshold is much lower for EUC and they release then test and fix things on the fly - often introducing new errors. A professional dev team would never dream of doing that for very good reasons. In almost every field of human endeavour, a quick shabby and substandard version can be done quicker, and the challenge to build a better version doesn't usually have the time constraint that it must be as fast as the lowe quality one.
Putting development tools into the hands of users may seem attractive at first - it gives them results fast and takes the pressure off dev - and for very very constrained tools, that's fine - I remember a time when IT would be responsible for producing presentations and I think Sharepoint is a massive step forward in user empowerment. However, Excel is an extremely powerful tool and is often used as a fundamental step in important business processes, but in common usage doesn't have the checks and balances that you'd hope a formal SDLC process would build in.
The challenge is pointing out to proponents of EUCs what the problems in their approach are, and be flexible enough to know when to leave them alone to do their own thing.
However, like I said before, often the problem is one of short-termism - an end user doesn't give a toss about the big picture or future-proofing, they're under pressure, they're overworked and they're looking at a fix for that problem. However, they're probably ignorant of the risks inherent in their approach. A good company will have policies regarding the use of EUCs and try to limit the amount of it, reduce risk from it and/or ensure that there's an appropriate level of oversight and control of their development.
January 21, 2014 at 6:26 am
Excel can be great in the right hands, your worst nightmare in the wrong hands.
Yes, it is no surprise that it costs money to build a data warehouse, or create more controlled processes when reporting results.
But when everyone top to bottom can use the same data source and logic to get to the same results, a couple of wonderful things happen.
With more eyes on the data, rules are tweaked, or incoming data gets cleaner.
Less time is spent questioning results, more time is spent top to bottom working towards improving them.
Business Rules need to be in formal processes, whether in Excel, or in a data warehouse.
Excel can be a great tool to prototype with, and is a user friendly way to work with data.
Many of the workbooks I created for the business led to expansion of measures and dimensions in the data warehouse.
Take a look at PowerPivot, then how it can be in a published workbook in SharePoint (control).
Then with SharePoint 2013, you can actually use this data model and enhance it.
So it does start bridging some gaps when used in certain ways.
I've been on both sides of the fence - Excel programmer, and BI Developer.
So I have had exposure so some of the best and worst that can happen in both worlds.
January 21, 2014 at 6:49 am
The auditing (or not) of these Excel application that are created by various people around the business is what is the most dangerous thing. I have done a fair bit of Excel development in my time and in one large retail organisation, buyers were planning their ranges on spreadsheets that they had created themselves or inherited from the previous buyer for that department and presenting their forecasts based on this data. When developing a company-wide replacement, we kept the front-end in Excel, in a locked-down state, and provided a database back-end for it with all the formulas and calculations agreed by management. When these agreed formulas were compared to what was in the original spreadsheets created by the buyers there was quite some variance!
We often talk about several versions of the truth in databases throughout a business, Excel is the ultimate tool for this to happen. As well as people working in silos with separate spreadsheets of the same data giving differing results, I have also seen people spending time reconciling spreadsheets against each other which seems to me like a complete waste of time.
As an IT professional, I would love to see data provided in a user-friendly consistent manner all the time from a trusted source, but the ubiquity of the "Export To Excel" option on a large number of applications makes it so easy for users to grab some data and start manipulating it that I don't see that genie ever being put back in the bottle.
January 21, 2014 at 7:12 am
chris.smith 91049 (1/21/2014)
FunkyDexter (1/21/2014)
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.
I absolutely disagree - we don't have a hope in hell of developing faster than users can knock something up in Excel or Access - as long as we're following best practice and designing systems, testing them and reviewing them, practising standard change control practices etc. So it's not a fair comparison.
Sometimes being involved from the start requires compromise. A tactical solution (e.g. Excel) can be a valid choice until a strategic solution can be delivered. This also allows for the time to do the job like a craftsman!!! (using the non-gender specific of man)
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 21, 2014 at 7:20 am
Being a developer, I usually agree that a "proper" application with a "proper" database is better than an Excel solution. There are times, though, where an Excel solution makes sense. Excel does not suck; people do. It's the people putting together the solution, be it a "proper" one or an Excel one, that determine if it's good.
Tom
January 21, 2014 at 7:39 am
OCTom (1/21/2014)
Excel does not suck; people do. It's the people putting together the solution, be it a "proper" one or an Excel one, that determine if it's good.Tom
"Guns don't kill people: People kill people" - However, like a person with a gun can do a lot more damage, a user with Excel can suck a lot harder.
January 21, 2014 at 8:11 am
Jeff Moden (1/20/2014)
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.
That's different. Code should be checked to prevent human error, since it will run many, many more times.
January 21, 2014 at 8:14 am
but we can try to make a difference by producing software quicker
lol why didn't I think of that!
January 21, 2014 at 8:15 am
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.
One reason we're stuck with Excel (and similar solutions).
However, if the spreadsheet gets changed, or altered, as can easily happy in a fluid situation, there's no check on that. Risk in increased, though perhaps negligibly.
January 21, 2014 at 8:19 am
chris.smith 91049 (1/21/2014)
...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 specifying 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.
Good points and I'd agree with this. However I'd also say this is partly an IT problem that we need a way to get apps done quicker, and easier migrations for users.
January 21, 2014 at 8:31 am
Good article, Steve. I don't see how to get around this one. Where I work there aren't a lot of people who even know how to open up Excel, let alone use it. However, our upper management does, and they write their own spreadsheets, develop their own formulas and I'm certain make some unknown amount of business decisions based upon them. And I've never seen the formulas they write, to verify if those formulas are valid or not. The power of Excel is both it's beauty and it's potential danger. If my boss can write her own calculates in Excel (she can) to do something trivial, then I'd rather she did it rather than wait for me to write an app to do it for her. However, you article does really give me pause - perhaps I should pay more attention to what she's calculating.
BTW, as an aside, thank you for writing about the "race to the bottom". I've never heard of that, but can definitely see that in action.
Kindest Regards, Rod Connect with me on LinkedIn.
January 21, 2014 at 8:34 am
Yet Another DBA (1/21/2014)
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.
Absolutely all apps have errors, but if you run through a cycle, and you have version control, you can more easily find or prevent errors on changes. If you're adhoc in improving your Excel/Access solution, you could easily cause issues.
The fact that you track sales or fuel with anything for years doesn't mean it's correct. The airlines could be improperly calculating fuel, and it be on a scale that they consider to be waste or loss.
Does it matter then? Perhaps not. However, a wrong copy/paste of a cell or change or a formula could affect bonuses, sales reporting, etc. and easily covered up by a single person.
I'm not saying we shouldn't use Excel, but be aware of problems, and perhaps look for other solutions. Not throw up our hands and accept this. The solution certainly isn't an 8 week/month development cycle, but perhaps we can hook into Excel, write tests/checks, maybe even build auditing or warnings into add-ins. Possibly something else.
January 21, 2014 at 8:36 am
I learned the hard way that, sometimes the accounting department will hide or strikethrough records when they really intend for them to be deleted. To a non-technical user, it seems logical that, if they strike-through an entire row or a cell within a column, then that means it's deleted.
Of course, these "deleted" records still exists when the Excel sheet is imported into a SQL Server table by a scheduled SSIS process. :exclamation:
Dammit, why did the ETL process import the record; can't the guys in IT plainly see that the rows were "deleted"? :angry:
Oh, well, I guess it's time to "fix" the ETL process. 😛
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 21, 2014 at 8:44 am
I think Steve's final sentence was telling. We need to deliver solutions faster than the business unit can develop their own
I think its awesome how sqlservercentral has solved a problem that the rest of the industry has been struggling with for quite a while now, who knew it was something as simple as "program faster"!!!! Props to you all, I would have never expected that this little website would produce the mythical silver bullet that we've all been looking for all these years!!!!
January 21, 2014 at 8:49 am
patrickmcginnis59 10839 (1/21/2014)
but we can try to make a difference by producing software quicker
lol why didn't I think of that!
I'm sure you did. The problem is neither of us knows how to do it.
Viewing 15 posts - 16 through 30 (of 89 total)
You must be logged in to reply to this topic. Login to reply