JimV_ATL
asked on
Indexing Strategy for Ad Hoc queries of a Data Warehouse.
I have a table of denormalized data which is approximately 150 columns wide. It will grow to approximately 150,000 rows or so.
The front end creates ad hoc queries which allow pretty much any column to potentially be part of the where clause.
Currently, I capture the queries that are being run, and their execution time so that I can create composite indexes for frequently run and or particularly slow queries.
A successful answer would ideally include a link or two to articles from respected sources as well as personal experiences.
The front end creates ad hoc queries which allow pretty much any column to potentially be part of the where clause.
Currently, I capture the queries that are being run, and their execution time so that I can create composite indexes for frequently run and or particularly slow queries.
A successful answer would ideally include a link or two to articles from respected sources as well as personal experiences.
Have you thought of use MS Analysis Services? This would be useful if you were limiting column criteria to 1 value per column, i.e., 'WHERE State = 'CA' and AreaCode = '909'. You would probably end up with sub-second result-sets.
What's the question?
Your approach seems good.
You will need indexes to support the queries but no point in indexing every column if most are not searched.
Another approach would be to not allow ad hoc queries and force the users to request search criteria - you would then build a stored procedure to support it.
I usually do this sort of thing by giving access to a search SP which is passed the search criteria (not a where clause but the conditions on columns). I can then log all the searches from the SP and put in specific code to support the most common searches. Less common ones are supported by dynamic sql.
I did have an article from a respected source (:)) but lost it when I changed ISPs.
Your approach seems good.
You will need indexes to support the queries but no point in indexing every column if most are not searched.
Another approach would be to not allow ad hoc queries and force the users to request search criteria - you would then build a stored procedure to support it.
I usually do this sort of thing by giving access to a search SP which is passed the search criteria (not a where clause but the conditions on columns). I can then log all the searches from the SP and put in specific code to support the most common searches. Less common ones are supported by dynamic sql.
I did have an article from a respected source (:)) but lost it when I changed ISPs.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have never used ADOMD, but from what Frostbyte is saying, it sounds good. Another good tool (although not free, there is a free sample) is Crystal Analysis. It looks somewhat like the cube broswer in Analysis Manager, and includes graphs. Its somewhat of a dynamic report in that the combo boxes for selecting specific dimension criteria are part of the report file itself.
You can also install English query from the SQL Server CD. This would allow the user to input via an intranet-based web page something like "What was the revenue for California sales in Quarter 1 of 1999", and they would have the results displayed.
You can also install English query from the SQL Server CD. This would allow the user to input via an intranet-based web page something like "What was the revenue for California sales in Quarter 1 of 1999", and they would have the results displayed.