November 1, 2004 at 7:06 am
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?
November 1, 2004 at 7:56 am
Imho, letting users have the "freedom" of creating/generating their own ad-hoc queries demand a certain level of expertize from the users. If they don't have this, they shouldn't be allowed this choice either.
Give them a set of pre-defined queries instead. (which is also a good way to avoid dynamic SQL)
/Kenneth
November 1, 2004 at 8:11 am
Kenneth,
I may have under-represented my users abilities. They are very sophisticated in their knowledge of their data and how to use it, these are mostly statistician types, and they can express very complex data requests. What I meant to say is that because they are not familiar with the back-end structure, it would be impossible for them to express the queries in a consistent and comprehensive SQL-like format which in turn could be passed to the back end as a string. There are too many tables and relationships for the average user to know how to properly phrase the expressions.
Giving them “a set of pre-defined queries instead” is not an option. They need to query the data in a million different ways and their specific needs are constantly changing.
At times I have considered the possibility of de-normalizing the database in order to remove the “complexity” but this DB is way too big and complex to do this with. At the moment I am investigating the SQL Analysis Services to see if that is a possible route.
Thanks for your comments,
Vicar
November 2, 2004 at 10:20 am
This definitely sounds to me like a job for OLAP. Once you have an OLAP representation of the database, there are a number of cube-browsers that savvy non-techies (sounds like your typical user) can use to slice and dice the data however they want.
That said, it sounds like setting up and administering your OLAP db might require a new hire. But the benefits are probably worth it; it really sounds like exactly what you need to me.
November 2, 2004 at 12:44 pm
Have you considered a series of VIEWS to provide a denormalized presentation to the user, keeping the mechanics of table relations in the background?
...
-- FORTRAN manual for Xerox Computers --
November 3, 2004 at 6:29 am
Tab, can you throw a few cube-browser namers my way? I am new to OLAP and am not familiar with all the tools.
Jay, views is the way we were thinking of "de-normilizing" the DB; it is too complex a structure to return it to its original "flat-format" (old Fox db).
Thanks
Vicar
November 3, 2004 at 10:20 am
Well, of course there's the cube-browser that comes built in with Analysis Services. The authors of the book I'm currently reading (which I won't recommend) are fond of Intellibrowser by Hungry Dog Software. I haven't done any playing with them yet, but I think I'd tend to use the native MS product until I found a reason not to.
November 3, 2004 at 10:31 am
Thanks for your response. My company, like you, has an unwritten rule of using native tools until a better one is found but we always look at what's available to get ideas and usage strategies. Keep me posted if you find any other books you recommend. Even though you do not recommend it, what are you reading now?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply