Indexing Strategy for Ad Hoc queries of a Data Warehouse.

Posted on 2003-03-10
Medium Priority
Last Modified: 2008-01-16
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.
Question by:JimV_ATL
  • 2

Expert Comment

ID: 8104440
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.
LVL 18

Expert Comment

ID: 8104470
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.

Accepted Solution

Frostbyte_Zero earned 1000 total points
ID: 8104964
I agree with RiverGuy. MS Analysis Server and use of OLAP Cubes would definitely be the best direction. With OLAP you define dimensions and measures. Measures are strictly numerical data that provide the data the user may be interested in analyzing. Dimensions are hierarchical data sructures that define some categorical business need. For instance, Country-State-County-City-ZipCode is a hierarchy of geographical locations. Year, Quarter, Month, Week, Day may provide time dimensions. Best of all your data is already denormalized! OLAP likes to use this type of data best.

What is your front-end? If you are using ASP, you could consider using ADOMD. That is a Multidimensional Extension of ADO. These are some links to usef ADO-MD syntax:



ADOMD uses MDX syntax. MDX is a syntax similar to SQL for OLAP Cubes:


A simpler approach would be to use MS Excel Pivot Table Services. Excel has a built in Pivot Table function that can connect directly to OLAP cubes either as a stand-alone application or used as a Web Component.



I have written a few applications with ADO MD using data going back to 4 years ago and utilizing 10 million rows or more. Designing your cube is the hardest part. But the beauty is, once you define it right you never have to go back to it. Just process it once in a while to keep the data current and the user can slice and dice the data anyway they want. Then you can add some data mining techiniques and monitor that instead. You will find you can get a better feel for what the user really wants after a few short weeks.

Good luck.

Expert Comment

ID: 8105089
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.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question