SQL Queries

Creating and Using Data Warehouses

Creating and Using Data Warehouses

SQL Queries

End users seldom access data warehouse data directly using Structured Query Language (SQL) queries. Analytical SQL queries can be quite complex, requiring database expertise to create correctly. The volume of data in a data warehouse is often so large that sophisticated SQL techniques are needed to achieve useful performance. A SQL query that joins three or four dimension tables to a fact table containing millions of rows and uses aggregate functions such as SUM to summarize and group the results can impose a significant load on any relational database and often yields performance that is not acceptable for online analysis.

SQL queries are often created by database experts for use with predefined reports that are executed on a regular basis during periods of low activity. Auxiliary summarization tables can be created and used to optimize the performance of these queries; such tables must be initially designed and populated when the data warehouse is loaded, and then updated every time the data warehouse is updated.

The use of SQL queries is one of the oldest methods of accessing data warehouse data. There are many books and training courses available that offer very sophisticated techniques for configuring data warehouse information and designing effective SQL queries that address complex analytical tasks.

Microsoft® SQL Server™ 2000 provides sophisticated query processing and optimization techniques and a powerful language, Transact-SQL, to address the needs of the data warehouse implementation. For more information, see Accessing and Changing Relational Data Overview.