January 21, 2014 at 12:49 pm
I think the same issue can be had anywhere. It might be less likely to happen if reports are developed by the BI experts because we know how to test and validate the code. Someone whose primary job is not report generation might just throw a bunch of stuff together and use the results unchecked.
In my opinion, anything you do, even if it's in Excel, needs to be sanity checked. Talk to the subject matter experts and ask if what the report is telling you sounds reasonable. We have this issue sometimes in spread sheets at my business. You tell the inventory department that their usage for the month is 3 million, and they will tell you that that's not possible because the revenue doesn't match that figure and it should be more like 4.5 million. If you're just blindly accepting whatever number your report spits out, you're going to be in trouble.
I think the lesson isn't that people are using tools where it's easy to make a mistake. I think it's an issue of people being in too much of a rush to get the rest of their work done that they don't test their solutions.
January 21, 2014 at 2:19 pm
The article stressed the obvious. Excel, like any program that manages data, can produce errors if not done correctly. You can say that about access or any other software.
I think the writer didnt emphasize that Excel is so widely used because it is easier for the mass. Perhaps other programs should work on their user interface so that they are commonly used. For those that argued that Excel is not a database or should not be used to manage data, I would argue that you would find more Excel software than a database software loaded in their customers computers.
Sorry, applications are there to make work easier. Sloppy development will happen in any application.
January 21, 2014 at 3:27 pm
Yes I agree Excel is not good for enterprise solutions.
</vent on> However, Access is even worse.....!!! </vent over....>
🙂
January 21, 2014 at 3:55 pm
Anything on a spreadsheet must be the truth. Computers can't lie, and a computer said it. Ergo...
I agree with much of the thread. When it come to 'analysis' of any kind, it cannot be fixed with a tool of any kind.
Right up there with "Lies, Damn Lies, and Statistics" and logical fallacies.
There simply have to be controls. Interesting that we hold developers (and DBAs doing similar work) to high standards (QA, code reviews, 3rd party verification, audits) but not your average corporate decision maker.
January 21, 2014 at 5:40 pm
dave hants (1/21/2014)
Yes I agree Excel is not good for enterprise solutions.</vent on> However, Access is even worse.....!!! </vent over....>
🙂
Either one is only as good as the brain behind the keyboard.
Kind of like drivers behind the wheel. 🙂
I've seen both good and bad.
Unfortunately the bad too often gets swept under the rug.
If only business realized how much value they can be in defining and molding a solution.
I can only help them if they can describe what they really need.
My value isn't in teaching them how to write code, or be really creative in Excel.
One guy I really enjoyed working with had me doing 6 sigma charts in Excel.
He was all about a simple, repeatable process.
Very plain looking charts, no fancy backgrounds, but heavy on upper and lower control limits.
Sadly he didn't last long.
January 21, 2014 at 6:59 pm
FunkyDexter (1/21/2014)
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!!!!
🙂 I didn't say "program faster", I said "deliver solutions faster". There's a very key difference. And I even went on to say that the solution could be as simple as helping the user write the spreadsheet. Imbue them with a bit of care and rigour. And, yes, explain the risks to them.
Heh, I can't help but read your post as "deliver problems faster", especially in the context of some of the other comments, like Steves phone list and those unnamed dbas who holds their accounting departments in less than the highest esteem.
I did get a laugh from today's editorial so theres that! 5 stars for pure entertainment value alone!
January 22, 2014 at 2:47 am
I disagree with what a couple of people have said - that Excel itself isn't a problem - as a development tool it's terrible, not easily source-controlled, distributed code in cells and VBA makes it easy to create, but very hard to find errors and no strong typing.
Also, it lends itself to more semi-automatic/semi manual processes, which always involve more risk.
These risks are always underestimated by business users in their mental calculation of the pros/cons of doing it themselves quick and dirty or having the dev team do it properly & slowly.
January 22, 2014 at 5:58 am
Heh, I can't help but read your post as "deliver problems faster",
I know what you mean but think of it this way: the problem is going to get delivered anyway, with or without you. By being part of the problem you actually can be part of the solution... or at least, part of a smaller problem.
January 22, 2014 at 6:07 am
chris.smith 91049 (1/22/2014)
I disagree with what a couple of people have said - that Excel itself isn't a problem - as a development tool it's terrible, not easily source-controlled, distributed code in cells and VBA makes it easy to create, but very hard to find errors and no strong typing.Also, it lends itself to more semi-automatic/semi manual processes, which always involve more risk.
These risks are always underestimated by business users in their mental calculation of the pros/cons of doing it themselves quick and dirty or having the dev team do it properly & slowly.
It all depends on whose hands Excel is in.
It's like throwing the keys to a Corvette to someone.
They may be afraid to start it, or want to find out what it can do.
If the latter, results will vary.
Some of the glamor of 'look what I created' plays a part in many of the problems.
And there can be pressure from above - boss wants it yesterday, won't even take 5 minutes to describe to IT.
So management usually has to be on board first in order for some sanity in the use of Excel.
I have automated many of the 'semi' workbooks as users got tired of them.
Usually a result of scope creep.
And usually the time saved - sometimes 1 or 2 hours daily - was swept under the rug.
After all, perception is do it in Excel, it only takes a few seconds.
Newer versions of Office and SharePoint actually can be used for source control fairly effectively.
Although probably not many shops do this.
The thing many do not think about, is many times a person switch jobs and work for a competitor.
And may take data with them.
January 22, 2014 at 6:10 am
FunkyDexter (1/22/2014)
Heh, I can't help but read your post as "deliver problems faster",
I know what you mean but think of it this way: the problem is going to get delivered anyway, with or without you. By being part of the problem you actually can be part of the solution... or at least, part of a smaller problem.
I help with spreadsheets, but thats not the message I'm going to take from the linked article. Careless spreadsheet use can impact the business, CHECK YOUR WORK.
January 22, 2014 at 6:11 am
Jim P. (1/21/2014)
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.I volunteer on another website often. One thing we constantly have to repeat to the askers: EXCEL IS NOT A DATABASE!
Do I have to repeat that here as well?
I disagree.
Excel can be considered a database but not a Database Management System (DBMS). Often, particularly in technology circles, we treat the term database as though it is synonymous with DBMS but that is not technically true.
End users use Excel as a storage mechanism for data but they don't have the management aspect of their data available to them. This is the technical crux of the matter. The business crux of the matter is that business units want the freedom to be productive.
It is us in IT whose responsibility is to highlight how a solution can be provided with as many of the risks mitigated without stripping the users of their productivity.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 22, 2014 at 7:28 am
Jim P. (1/21/2014)
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.I volunteer on another website often. One thing we constantly have to repeat to the askers: EXCEL IS NOT A DATABASE!
Do I have to repeat that here as well?
Excel is a type of database, in the same way that Indexed Sequential Files, FoxPro, Hadoop, and MongoDB are databases. Excel is not much of a DBMS, and it's definately not a (R)DBMS (just like ISAM and Hadoop arn't either).
However, there is no conceptual reason why Excel couldn't (optionally) enforce schema and data quality constraints. It could be as simple as a set of rules overlaying and working in conjunction with the existing free-style DataGrid interface. It seems it would require relatively little development effort, provided by Microsoft as an add-on feature.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 22, 2014 at 7:53 am
Eric M Russell (1/22/2014)
Jim P. (1/21/2014)
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.I volunteer on another website often. One thing we constantly have to repeat to the askers: EXCEL IS NOT A DATABASE!
Do I have to repeat that here as well?
Excel is a type of database, in the same way that Indexed Sequential Files, FoxPro, Hadoop, and MongoDB are databases. Excel is not much of a DBMS, and it's definately not a (R)DBMS (just like ISAM and Hadoop arn't either).
However, there is no conceptual reason why Excel couldn't (optionally) enforce schema and data quality constraints. It could be as simple as a set of rules overlaying and working in conjunction with the existing free-style DataGrid interface. It seems it would require relatively little development effort, provided by Microsoft as an add-on feature.
Done that. Microsoft Office as a platform is something that MS was pushing back in the very early 90s with early Office releases.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 22, 2014 at 9:07 am
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.
I think it would be awesome if we could just put a module in Excel that lets it connect to an RDBMS that already has those sorts of features!
January 22, 2014 at 10:00 am
patrickmcginnis59 10839 (1/22/2014)
Eric M Russell (1/21/2014)
One feature that would be very useful in Excel is the option to create strongly typed columns or regions. That, and also column level NOT EMPTY, DEFAULT, CHECK constraints. FOREIGN KEY constraints could work across sheets, similar to VLOOKUP.I think it would be awesome if we could just put a module in Excel that lets it connect to an RDBMS that already has those sorts of features!
Office Document Security has been around for around 10 years.
Excel can connect to many data sources, many of which have constraints and security.
In theory, you could take Excel off the desktop, and publish to SharePoint, and use Excel Services.
But you might need something very sturdy to hide behind.
Viewing 15 posts - 46 through 60 (of 89 total)
You must be logged in to reply to this topic. Login to reply