July 28, 2003 at 5:59 am
We have a fairly good development group in terms of good practices (and all work for me, so I can fix it where we do not).
We also have a growing "power user" group who do their own queries. I've been fighting the "get rid of Access" battle for a long time, and am starting to win (a couple of corrupt and lost Access databases helps).
I'm looking for ways to turn smart analysts with no database design experience into cooperating and useful database developers. The goal is that instead of building some departmental subsystem in Access in isolation, they do a lot of the work and with I.T.'s coopertion we put the databases on SQL Server and maintain and design them properly.
To get reasonable turnaround time, this means we need to educate them to do as much as we can, make them aware of our standards, etc.
I could use advice in any areas, but specifically could use pointers in:
- For smart business analysts with no database training, a good book to properly illustrate good practices of normalization and other basic database practices.
- Experiences people may have had with similar initiatives - what worked, what did not?
- One of the biggest things keeping them tied to Microsoft Access is the visual query designer there. The query builder in Excel stinks, mostly due to Excel's poor integration (e.g. runs each query at least twice). Several have become convinced that writing SQL directly is better, but most haven't made that leap. Money is a big object. Are there good cheap tools that might help?
July 31, 2003 at 12:00 pm
This was removed by the editor as SPAM
August 2, 2003 at 9:56 am
I also have a group that does their own queries. I'd love to get them into QA to avoid some of the blocking issues, but Access not only has the great query designer, but it let's them define their own tables in the same scope as the SQL tables so they can do mini "backups", work on data locally, etc. Plus they use a ton of module code that just wouldnt port easily or well to TSQL.
I think I'd separate the goals of database design and power querying. I don't have a problem with them running the queries, but someone on the dev team or myself does db design and changes. Saves headaches.
Where I need to spend more time is getting them to write better queries and to work smarter, not brute force. I've got one Access user who has started to use pass through queries and has seen a tremendouse reduction in time required for some operations. SQL to Access joins typically suck for performance. He has also realized the value of calling procs from Access, so potentially I could see teaching them how to code procs (I would still proof and apply them).
Dont know if that helps.
Andy
August 3, 2003 at 10:19 am
well, glad to know there's someone else out there dealing with this.
One thing we did do was create a "sandbox" database, whereby people could create their own objects (primarily tables and stored procedures). They get tagged with their user name so we know who created what, and weekly we report on usage and when last-changed so we can encourage responsible sizing and cleanup.
That's helped a lot on query performance as now the tables they had been putting in Access (e.g. uploads of what information they wanted to join up with SQL) is available on the server.
Thanks for the thoughts.
August 3, 2003 at 4:16 pm
Not a bad idea, though you still risk (no worse than before of course) the affect they can have on your server.
One thing we talked about but have not done is a subset of that, set up staging tables so that when they want to append a 100k records they can do it one chunk, then have a process that moves them into production a few k at a time.
Andy
August 3, 2003 at 4:26 pm
Coming back to some of my more difficult issues, it's correctness. While it is easy to say "it is not my responsibility to make their data analyses correct", that's bad for the company.
A lot of their shortcomings appears in the form of bad design leading to bad data. The most common problem is bad joins, for example forgetting to use a necessary part of the primary key. For example, we have lots of compound primary keys, and often the last piece provides uniqueness but only in relatively few records. Forget it, the join runs, but the results are wrong, often subtly wrong.
We have experimented with documentation tools (home grown) whereby we document the tables and also their common joins. So we'll have an online tool where they can look up "TITLE" and see not only its columns and meanings, but will see
TITLE T / HISTORY H join:
on t.UnitCd = h.UnitCd and t.TitleTK=h.TitleTK
TITLE T / ISSUE I join:
on t.UnitCd=i.UnitCd and t.TitleTK=i.TitleTK
etc. Some much more complex. But so far it seems of little help, I think because they just "know" the right answer and do not look.
Another thing we've done that has helped a lot is that our Intranet web pages, which we develop to display complex data (and hopefully do the queries correctly), all have a "show query" button, which they can use to display the SQL in a form that can be cut and pasted into Query Analyzer. Those willing to use QA find it convenient, but until I can break the "Access" barrier most ignore the SQL (since it won't run in Access).
You know, if the wise people at Microsoft had used real SQL instead of fake stuff in Access, life would be a lot easier where you have to put up with both.
August 4, 2003 at 4:45 am
Totally agree that the Access SQL was a wrong step. I see in Access 2003 (and maybe earlier) there is a setting to generate ANSI SQL. I think there are only a couple differences, I think you could write a little code to convert TSQL to Access.
We don't show the queries that easily, but we often make it available if you view source where it shows up as a comment.
How about a bunch of views to package the joins better?
Andy
August 4, 2003 at 4:55 am
Re views - We've done some of that. There's just a LOT of combinations of tables, and the ones they screw up are things like customer records which can be combined (at least through some chain of joins) with probably 50 different tables.
That's a good example. We try to make the customer number unique across locations, but historically it was not. it has a location code as part of the key. Sometimes people forget it, so 90% of the customers join correctly but the other 10% are wrong. We have views with it joined to some key tables, but not all.
Maybe we need more.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply