What's the utility of encouraging users to learn SQL and possibly the ability to execute it?

Question

Facing a situation where I can't possibly get all the features that are requested into each version of the LOB I support, I've evolved a number SQL query templates that users have and send to me, which I then execute.

This then led to a password protected SQL execution dialog which users with write access can execute for themselves with an understanding that if they make the wrong typo they could do damage. While this makes me nervous, the upside of having the functionality outweighs the risk in this situation.

Then there is the change in how people view the functionality. It seems less magical when they understand more about how the database works. On the other side of this, however, new users can potentially walk into a culture where the lingo includes something totally foreign to them.

Any advice on weather this direction has relative utility?

Answer 1

Decide on a reporting suite of software asap. (SQL server reporting services, for example)

Let your users tell you queries and parameters they need often and actually make repeatable reports out of them. If some of your users have gotten a good hold of SQL, think about setting up a development database for them to make reports against.

I worked for a company that didn't have a reporting suite, but had a lot of need for reports and it was a huge mess. Any reasonably sized business is going to have monthly/weekly reports.

Trying to build them yourself into your application (when you have a really critical report you HAVE to build for the user) becomes a nightmare. Then later it's a maintenance nightmare. Not buying a reporting suite but having lots of reports is taking on technical debt if you ask me.. you will end up writing so much boilerplate that you could buy off the shelf and/or you're doing risky things like letting your users execute SQL.

Letting your users run their own queries is asking for issues. Deadlock is the first thing that comes to mind.

Answer 2

You can start with a limited knowledge of sql, but you may need to do some of the heavy lifting for them. I've worked at many companies that did this with databases for in-house and third party applications. Formal training was provided. Don't authorize users until they are prepared.

Here are some things to consider:

1. Create a reporting version of your database instead of using the production transaction database if possible. Discuss how timely the data need to be. There are probably very few custom queries that rely on up to the minute data. A virtual server that can be resurected once they blow it up can be very handy.
2. Don't allow direct access to tables. Create some views or whatever database objects are appropriate for the users. Take care of any complex joins and try to force using some type of parameter as much as possible to limit the amount of data retrieved.
3. Make access read-only.
4. Use a report writing tool. Much of their needs will be aggregating data at various levels determined by your business with different filters and sorting.

Start with a small number of available fields. If you leave it up to them, they'll want them all because they feel if they don't give you a list of 500 fields today, it will take too long to process a request for new fields.

Brush up on your database knowledge or work with a DBA who can help regulate the amount of resourses these queries will be allowed to use. Again, be stringent, but let users know you can quickly handle new requests with this system much quicker than before, so there is no need to demand everything all at once.

Answer 3

It depends on the culture.

There is something to be gained from having users skilled in SQL. But if they go about it in a half ass way they can also get misleading results. You have to really understand what they actually want.

NEVER give the user the ability to hurt the company.

If you want to do this you can set up a reporting server that subscribes to the production db. Or you can set up some views and only give them access to them.

In either case you can mold the data to make it easier to use. For example create month, year, day columns instead of just date columns. Denmoormalize the data to eliminate the need for joins. etc. Then only.

Answer 4

IMO: Too dangerous.

As others have mentioned, even simple read-only access can break the database in horrible ways - deadlocks, performance, etc.; in principle, you are giving your users an efficient way of DoS-ing the database server. All a user needs to do is full-outer-join the largest table in the DB on itself, using a non-indexed column. Sure, you can put up all sorts of restrictions and safeguards, but this is tricky, and probably not worth the hassle.

Instead, ask yourself why this setup is a perceived necessity. What makes users demand such a never-ending flow of yet new queries? Are there any recurring patterns at all that you could distill out of all those queries? Then design a solution that allows users to flexibly generate the reports they want. Possible solutions include off-the-shelf reporting suites; a semi-sandboxed DSL that exposes the domain data model in a queryable way, but without the full destructive power of SQL; or a custom UI that users can use to compose the desired queries in a desktop-application-GUI-like way.