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 new records are added and 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 which 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. Although they are very "data-aware"; they know their data and can express very complex data requests.

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 is 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 an almost impossible task and time is of the essence. I am also looking at Data Analysis Services (Data Warehousing).

Has anyone here had any experience with a similar situation that can shed some light?