This week I am trying to extract data from a data cube that was created for reporting. Problem, the cube was designed so that only one node can be retrieved at a time, and so if I need a bulk retrieval, which I need, it needs to be queried a billion times, sinking the server, timing out the queries, taking hours. The IT team is trying to figure out a way to run an overnight job so that it sinks the server during quiet hours. Basically these guys designed a vault where you can only add data but never retrieve it. They should be fired.
[edit] and before someone starts to think "yeah but this is big data", the underlying populated numbers in this cube would fit in a 5MB spreadsheet.
I am not sure that the audience of hacker news realises how terrible is the reputation of IT and developers in large companies from the business side. They are seen as a world of hyper-bureaucratic, common sense-free, business sense-hostile, half-assed lemmings.
Excel is not great but one has to have a hard look at the alternative.
Many, many startups are competing against a moderately-complex Excel sheet, and they don't even know that.
This is probably its biggest flaw also — chances are you don't need all that flexibility within a scope of particular spreadsheet; and sometime you may prefer a solution that was specifically targetting this exact usecase.
In fact, I worked on a project that largely aimed to replace a bunch of internal spreadsheets because giving groups a spreadsheet to maintain is a little like handing Phaeton the reins to Apollo's chariot.
Then it's convenient that Excel both is a and has a programming language, eh?
You have to know the right point at which to make the transition. We had an Excel spreadsheet used for managing 10-digit sums of investments, held together with sticky tape, integrating SQL queries, XLL C++ addins (plural), Corba, creative use of the Excel solver and pivot tables in addition to custom data overrides (being Excel, without version control or sane error handling).
That point where operational risk exceeds the benefits of flexibility is a sign that you want to get some programmers on it - and we did, even at a cost of 10 programmer person-years or more in replacing that spreadsheet, and a horrendously slow and cumbersome process to get even trivial bugfixes or features added.
Usually decisions like building a $50m application aren't made by devs but by clueless managers.
I do agree with the sentiment though, I see a lot of wheels reinvented everywhere I look. Recently I had to deal with a scheduler and a file remover/archive that were built in house. God know how many hours were wasted on this but the whole thing would work better as a batch script and a scheduled task.
That's where project managers/change people are supposed to help, acting as an intermediary, understanding both the tech and the business. But in my experience they usually understand neither the tech nor the business and are adding more confusion, the whole thing works better without them.
But the state of corporate IT is sad to look at. I work in a large organisation where one of the executives was telling us proudly at a townhall that we have more developers than Facebook and Google together (though we are in the financial sector). Well, to say the least, we have little to show for it. Nothing works properly. Our core systems still run on programs written decades ago and we have to pull people from retirement if, after having exhausted all other possible options, we have to make a minor change. The network drives have a top speed of 1MB/s, and hang sometimes for minutes. As for the business applications. Any minor change results in ETA in months if not years and multi-millions quotes.
I remember discussing with some devs about an Excel VBA addin they were taking over from the business. They re-wrote most of the features in .net, which is very good, but somehow half of their application was still written in VBA, and I mean tons of VBA. I wondered why. They told me the library they were using only allowed to expose a function to Excel, but not to hook to or manipulate the UI. So they have written a VBA wrapper around all of their features that calls the .net excel functions.
There was a long silence after I showed them how they can get a handle to the COM interface of Excel in a single line of .net code using that library.
That's what happen when IT is treated as a cost center instead of a co-leader of innovation together with business. Bad hires, no real objectives, no liberty to innovate: do that constantly for decades and you end up in the situation you describe.
You can't really blame the IT guys that are thrown in that mess. It's almost always a top management fault.
> There was a long silence after I showed them how they can get a handle to the COM interface of Excel in a single line of .net code using that library.
Looks like they are lacking a good tech lead, why not apply yourself? :)
A lot of large corporate IT is outsourced to the likes of HP. It's not unknown for them to put in the least qualified folks they think they can get away with - which can often mean in the case of unusual infrastructure, libraries or languages that they sent someone on a basic training course the week before starting. This leads to the quality you might expect.
The two most "interesting" contracts of my career were in financial. One was trying to reverse 25+ years of bad decisions and no end of duplicated, wrong, and unsanitised garbage in the live DB along with some dependency on CICS that fortunately I was far away from. The other will remain undiscussed. :)
That explains it. I'm working in finance at the moment too, it's where you find the worst developers/management/products.
I'm betting the current situation there is caused by some combination of technical debt and the dead sea effect (also coined by spolsky I believe). Once things get to a certain point competent people leave and by attrition you're left with the ones unemployable elsewhere.
I second that opinion. There's a lack of depth of knowledge on the IT/Coding side, and people think it's just another little cog that they need to get their deals done.
So you get things like
- Complex derivatives calculated on Excel. You wonder why people make millions on this? It's because you never really know what the heck you're calculating.
- Missed deadlines that should be cron jobs. I remember a guy coming into the office, down a good few hundred grand because he'd forgotten to exercise options that had a dividend on them. I mean that's just stupid, if you had a proper system it would tell you, and the calendar would be synced with Bloomberg.
- Ridiculous budgets. A friend at a major bank is on a $27M project that is essentially a calculator that adds two numbers together. Other people are attempting to break into HFT with zero competent coders, no network other than what can be leased cheaply, and no systems engineers.
- Balls of spaghetti of unimaginable proportions. One c++ guy at a big bank told me he takes 7 hours to compile. He's writing execution code, which you'd think would be relatively snappy.
How long should it take? The last time I did anything of note in c++ was on a computer of 2000's vintage and 7 hour builds were pretty common.
If you're taking hours there's just a lot of dependencies that could probably be refactored.
Large non-tech hyper-bureaucratic common-sense-free companies are especially hostile toward good IT and dev so good people just don't work there ;)
[and even if they did, they still couldn't get anything done]
Credit goes to: https://blog.codinghorror.com/regular-expressions-now-you-ha...
Sorta OT, but this is one of the more sci-fi sentences one could say that actually makes sense.
The original mistake is that both were made interdependent. Excel tries to fix that the hard way by introducing various reference modes, a lot of "do what I mean" magic and even nested spreadsheets/databases (aka "tables").
But it still leads to scary bugs, as the video shows: if you do things the wrong way the numbers you derive from your data are wrong and you don't even see it if you don't pay attention.
While watching the video I was wondering if all those features were brilliant or insane. The answer to me is that it's insanity.
If nothing else, HTML has shown for a long time already how one can - one should - make the content (data, programming) separate from the presentation. This idea has to be back-ported to spreadsheets now.
The first half of the video is really just keyboard and drag'n'drop tips, while the second half is really just brutal hardcore programming: "match", "if", "lookup", references, indirections, "debug mode", ...
Acknowledge that spreadsheet documents really are programs. Make a spreadsheet DSL then apply ideas from Lighttable to make it as "visual" and "easy" as possible.
Or place those Excel sheets on Sharepoint.
In any case, there is this Excel alternative, Resolver One, which has what I believe it is a killer feature: you can expose the Excel sheet as a web server. (With Excel, you would need to have a Sharepoint server for that, I believe).
That way any user can iterate a lot on an initial excel sheet but then their group can just work with the web version as an "immutable structure" sheet, and if a change needs to be done, you don't need to go and contact someone with appropriate permissions to upload a new version to the server, etc.
Oh man that hits home hard. And all the sums and counts are in random cells "beside" the tables right?
They start with a manual inventory of the configuration in question, type it ad-hoc into a spreadsheet, then go through making manual changes, notes and additions until it feels good. There's lots of highlighting, different coloured text, etc like layers of sedimentation. Formulas are only used for sums and counts in an ad-hoc way.
The end plan always contains mistakes and omissions that really hurt during deployment time.
The correct way requires discipline: immutable input sheets (from machine-generated config dumps), a parameter sheet, output tables (with consistent usage of formulas with "$" notation to lock rows and columns), and cross checks with conditional highlighting.
Unfortunately this is really hard for people without programming instincts/experience. Good job security I guess. But if you can do it, it's both faster and safer than the manual free-form method.