Pattern for SQL data mining app

Question

We have an app that is used for data mining on our client database.

Typical uses include getting a list of clients and their email addresses, running reports about user transactions between certain dates and returning clients that live in a specific area.

High level functional requirements are:

1. Each data mining query needs to be able to exported to excel.
2. Parameters need to be supported (so that we can filter results etc).
3. Other apps would need to be able to access these queries as well as their results.

It has been implemented as follows:

• A database function is created for each SQL query.
• There is also a table that contains details of each function as well as required parameters as their types.
• There is a web front end where the SQL queries are maintained, and can be run and exported.
• A web service exists that other apps can use to run queries. The method to run a query returns a dataset and gets passes parameters and their values in XML.

Our current solution works but there are some problems with maintaining the hundreds of DB functions (would it be necessary to use functions - couldn't we just store SQL in tables?). I also have some security concerns around SQL injection as our front end basically allows the users to input any SQL.

Has anyone had to develop something similar and what approach did you use? What was your reasoning for using that approach?

Answer 1

I wouldn't call this data mining per say.

Anyhow the closet thing that I've implemented in a project can be described as such:

I called it "Custom Reports" (a single module for a project [its a sql db with multiple front ends, heterogamous system that is integrated with multiple other systems (SMS, CISCO, File Server, Print Servers) etc]).

Anyway the idea behind the custom report is to allow the user to create almost any report (using a GUI report builder), then let the user set up as many scheduled (re-occurring) reports as needed. These are picked up a service application that delivers it via email as Excel/CSV to the email address that is set up.

Its fairly easy, its basically a rudimentary ORM, the query creates dynamic sql that is cached and executed.

The GUI in essence edits meta data (eg tables, fields, filters, boolean logic etc), a single Class takes this meta data and creates the SQL query, and the "parameters" are sanitized.

Edit:

My reasoning for this approach was that, if I didn't do it this way, then the web application would have a never ending series of CRUD "Report" pages, that would do very repetitive things. Or A single CRUD page that would end up calling hundreds of stored procs, that would have to be all maintained and created.

In Practice this method has cut the need for 95% of all the reports I would have needed to create. Of course there are a few complex queries that can't be done (unless I modify the Meta to SQL Class) this way. But that's fine, those few queries its just easier to bang out a quick hand coded SQL...