Manage Learn to apply best practices and optimize your operations.

Data warehousing query tools

When it comes time to start creating reports out of the data in your warehouse you are going to need a good query tool.

When it comes time to actually start creating reports out of the data in your warehouse and to start making decisions with this data you are going to need to have a good query tool.

All tools will have the basics, but there are some high-end features that tools should have that will make your job that much easier.

Below are some of the attributes you should look for in a query tool according to Ralph Kimball's article "Features for Query Tools":

  • Cross-Browsing of Dimension Attributes - A real dimension table, such as a list of all of your products or customers, takes the form of a large dimension table with many, many attributes (fields). It is absolutely mandatory for a query tool to present, in real time, a list of the valid values in a dimension attribute (for example, product brand) and let the user choose one or more of the values to set a constraint. This basic browsing capability is now fairly standard in sophisticated query tools. Cross-browsing, on the other hand, refers to the capability of a query tool to present the valid values of the product brand, subject to a constraint elsewhere on that dimension table.
  • Open Aggregate Navigation - Aggregate navigation is the ability to automatically choose pre-stored summaries, or aggregates, in the course of processing a user's SQL requests. Aggregate navigation must be performed silently and anonymously, without the end user or the application developer being aware that the aggregations even exist. Open aggregate navigation occurs when the aggregate navigation facility is a separate module that is available for all query tool clients simultaneously.
  • Multipass SQL - Breaking a single complex request into several small requests is called multipass SQL. Multipass SQL also allows drilling across several conformed data marts in different databases, in which the processing of a single galactic SQL statement would otherwise be impossible.
  • Semi-Additive Summations - There is an important class of numeric measures in common business fact tables that are not completely additive. Anything that is a measure of intensity is generally not additive, especially across the time dimension.
  • Show Me What Is Important - your query tools must help you automatically sift through the data to show you only what is important. At the low end, you simply need to show data rows in your reports that meet certain threshold criteria.
  • Behavioral Studies - An interesting class of applications involves taking the results of a previous report or set of reports and then using these results over and over again at a later time.

See a list of query tool vendors at

For more information, check out searchCRM's Best Web Links on Data Warehousing.

Dig Deeper on Customer analytics