Handling uncontrolled database development 8

[rosieb]

I’m in database hell!!

I work in a local government IT Dept where I have been given responsibility for Access databases as part of my job (the main part is data and process analysis and development).

We have c250 users, about 175 with full versions of Access, and I’ve traced about 450 separate Access databases on the network, I know there are others on local drives “because it’s safer than the network”. Many of these are obviously obsolete or copies, but I don’t know which!

Referring to an earlier post, thread655-606833 , I have Platypi, Chimerae, plus a whole lot of other types (including Snarks, Amobae and probably Krakens, Grendels and Cockatrices). Many are de-normalized and badly designed; all are undocumented. Worst of all, a significant number are “mission critical”. In many cases the originator has left the organization. Any problems are, of course, an IT problem (ie, MY problem). Any lack of an immediate solution is seen as just proving that IT is incompetent and unhelpful.

I’m trying to bring the situation under control. Ideally I’d take full Access away from all users and insist that all databases be developed by IT, but the world don’t work that way. I’m all for user empowerment, but in this reality – it’s dangerous.

I’m pursuing a number of avenues, but one thought is to develop some guidelines for anyone creating new databases (normalization, get the design checked by IT, split the database, only work on a development copy, documentation, etc.). A sort of kite-marking. Then, we only support databases which comply.

I want it to look reasonable, but hopefully bring home to some of our so-called experts (quote “I’m not going on a course, there’s nothing they could teach me”) that it’s not as easy as they think.

So, any ideas on what I should include? Any suggestions gratefully received.

Rosie

PS I’m told the budget doesn’t run to medieval instruments of torture, shame.

 

[Kjonnnn]

Well actually i've been here 3 years... had 3 server crashes.

One crash, the back up never gave an error message that it wasn't working. Fortunately, it was only NOT work for 4 days prior to the crash.

 

[rosieb]

Fredericofonseca
Nice idea, another one to look into. Tho’ it does come back to the autonomy thing, the ones I’d really want to lock out would be the ones who had to be “exceptions”.

ChrisHunt
You’re absolutely right.

It’s my impression that the whole IT infrastructure here had suffered years of neglect, so we’re going thru’ a major upgrade programme but budget restrictions mean it has to be phased. So yes, I do take your point about use of local drives. User education is one of my passions, but getting formal general IT training organised is hard, after all everyone knows that Windows apps are “intuitive” so people don’t need training – I try to do “guerrilla” training when I can, and work on confidence building (so many users worry that they could “break” the system by pressing the wrong button).

Many of the databases are really just spreadsheets run under Access and would actually be better done in Excel. Your point about access to IT resources is a good one, we probably do need more resource.

BUT [rant]I am sure that some of my “meddlers” prefer to “play” with Access rather than doing their real work – it really ticks me off to hear that someone has spent a week producing something really basic which doesn’t work and would have taken me a day to do properly, then they want me to tidy it up (I usually end up rewriting). These particular individuals usually aren’t really interested in learning, they take on a project, have good play, get bored, pass it on, get someone else to fix it, claim it as their own work, and move on to the next disaster – where they still use a text field as a primary key![/rant]

I really sympathise with, and enjoy helping, the really interested, motivated ones – helping them isn’t a chore, we were all there once (and some of us still are).

What I’d ideally like to do is really tie things down, then gradually open it up to real expert users, those willing to study and demonstrate competence. User /IT co-operation – Nirvana.

Rosie

 

[flyboytim]

Rosieb sept 24 said:

" it’s very hard to make them appreciate the importance of data validity, I strongly suspect that some of the data coming out is inaccurate (there is no culture of testing or validation, just an assumption that if it comes out of a computer it must be right). "

Too true.
Beyond data validity there is also data accuracy and completeness.
If a dataset has missing data inconsistently stored with different values instead of nulls, and unstructured data values in the first place, then the value of any analysis is suspect.
If there are no standards applied to the logical validity of query statements used, then the results are also suspect.
Even if the above checks are done and suspect data rejected, the final output has dubious validity unless it can be shown to account for sampling bias which is invariably present on data that has been censored due to poor quality data collection or lack of consistency in recording.

We used to call it "GIGO" - garbage in, garbage out.

If the individual who is responsible for his own data analyses his own data, with which he is exceptionally familiar, there are other biases which the investigator will fall prey to, in an attempt to prove a belief in the findings. Science is full of examples of inadvertent bias by enthusiastic proponents.

 

[dilettante]

Just like everyone thinks he/she is a programmer, today they're all DBAs now too!

I don't see this trend stopping or even slowing anytime soon. It is very alarming though. I've seen one really awful online web application where CC#s were gathered up at the web server, and once a day this file was FTPed from the server to a user's PC. This user then performed the CC transactions manually via terminal emulation from the file of transaction data!

Later the thing was semi-automated, but they still kept the ugliest, stupidest part of the whole thing:

The people DLing the file and plinking in the transactions on a PC, on their own, decided to routinely import this CC info into an Access database where they accumulated months worth of the traffic. Yep, they thought they were being really wonderful. Can you say auditor's nightmare? I cringe at the thought of how many employees had at least read access to the file share they stored (store?) this MDB file on.


Recently I read something on the order of "the next generation of corporate blah-blah." It could have been Microsoft but I doubt it, more likely one of those "FubCo" type names for an outfit with a million clueless consultants, a couple mediocre software products, a flashy PowerPoint presentation, and a mission from God to suck your treasury dry.

The idea was to tie together all of these trashy desktop databases as some corporate resource.

I'll see if I can track the reference down. It had me laughing for days. Probably associated with someone's XML Medicine Show tool.