Open-ended querying of DB (??)

I am working on a project with a couple of hundred tables and views. There is one primary table around which all other tables revolve with the primary table having somewhere around 42 to 45 million records. Some of the related tables can have many times this number of records. The data is static in that it only changes at specific intervals (once a month or so) when more records are added and some "old" records are removed.

My problem is that users need to query these tables without any major restrictions. I mean, the front end will allow users to create almost any selection criteria form the many columns in the DB. The selection of values will be controlled via drop-downs and lists from where the user will select the criteria. The other consideration is that the grouping can be for any value and up to ten levels deep. Our users are not sophisticated enough to create their own SQL-like queries so we have to guide them through the process of selecting the values…

I am trying to create stored procedures that will allow this behavior but I continue to run into the problem of how to properly join the tables when the combinations are almost endless. My conclusion is that although it seems this seems to be a case where D-SQL is a good choice, there are some draw-backs I am not sure we can afford, specifically the security issues. Furthermore, the creation of a D-SQL "code-generator" seems like almost impossible task and time is of the essence.

Has anyone here had any experience with a similar situation that can shed some light?I think without dynamic SQL, you're stuck with creating a view for every possible combination of joins - not really an option. I think you'll need to use it. Is it possible to provide the highest priority querying and slowly add more tables as you have a chance to build it? If a user isn't sophisticated enough to build SQL, will they be aware enough to even understand what joining tables does for them?

I've done 'build-your-own' query-generating stuff before, but never to that scope. That could be a tough one.|||A Friend of mine was reading an article in visualstudiomagazine.com that might be of some assistance to you. Check out the online version http://www.fawcette.com/vsm/2004_10/magazine/columns/databasedesign/

Hth,|||Scott, thanks for the heads-up. I will post my impressions on the article later on.|||PD, I have no choice but to look for a solution. Our users currently have a system that is based on a "flat-file" format where creating the queries to produce the reports is fairly easy. When we decided to re-write the system as a web based application, the decision was made to move to SQL Server and take advantage of its power. We normalized the files, added records for a wider time frame and have created a beautiful data structure. Unfortunately now we are confronted with the issues of querying the data in a multitude of ways.

We are considering de-normalizing the data a little, using Analysis Services, English Queries, and others. Given the confidential nature of the data, security is a must and using D-SQL may be a problem. We can live with a system where the user makes his selection from lists and drop-downs and we construct the query behind the scenes; we do not expect them to know SQL.|||Beleive it or not there are actual tools out there that exist to perform this task. Mind you some may seem expensive... but they work, and the amount of development time saved to get something that works and may be useful in other areas in your organization may warrant the costs.

The tools fall into the Business Intelligence area of software, and are typically called EII or something to that nature. EII stands for Enterprise Information Integration.

Another way to explain it, you need a tool, typically made to fetch data from multiple sources, apply english names to them, then you specify how the individual "views of data" could be related to another. Next the tool gives you a GUI interface and/or exposes an API allowing you to say "I want data from 'a' and 'b' filtered on 'c' and grouped on 'd'."

Nimble Technologies made such a tool. My company, Actuate Corporation, has acquired them to use their technology within our own software. I'm not sure if we did an outright takeover, or just bought largely into their company or what, but Nimble may or may not exist. In either case, they had competitors, so the tools do exists out there.

I would suggest looking up Business Intelligence tools, or publications that frequently discuss such software. Publications geared towards CIOs and IT management would be another area to look.