Defining Named Queries in a Data Source View (Analysis Services)

A named query is a SQL expression represented as a table. In a named query, you can specify an SQL expression to select rows and columns returned from one or more tables in one or more data sources. A named query is like any other table in a data source view with rows and relationships, except that the named query is based on an expression.

A named query lets you extend the relational schema of existing tables in a data source view without modifying the underlying data source. For example, a series of named queries can be used to split up a complex dimension table into smaller, simpler dimension tables for use in database dimensions. A named query can also be used to join multiple database tables from one or more data sources into a single data source view table.

When you create a named query, you specify a name, the SQL query returning the columns and data for the table, and optionally, a description of the named query. The SQL expression can refer to other tables in the data source view. After the named query is defined, the SQL query in a named query is sent to the provider for the data source and validated as a whole. If the provider does not find any errors in the SQL query, the column is added to the table.

Tables and columns referenced in the SQL query should not be qualified or should be qualified by the table name only. For example, to refer to the SaleAmount column in a table, SaleAmount or Sales.SaleAmount is valid, but dbo.Sales.SaleAmount generates an error.

To view instructions about how to create a named query, see How to: Add, View, Modify, or Delete a Named Query Using Data Source View Designer. For information about how to use the query definition features in Data Source View Designer, see Create/Edit Named Query Dialog Box (SSAS).

Note   When defining a named query that queries a SQL Server 2000 or SQL Server 7.0 data source, a named query that contains a correlated subquery and a GROUP BY clause will fail. For more information, see Internal Error with SELECT Statement Containing Correlated Subquery and GROUP BY in the Microsoft Knowledge Base.

See Also

Concepts

Working with Data Source Views (Analysis Services)
Defining Named Calculations in a Data Source View (Analysis Services)

Other Resources

Working with Data Source Views How-to Topics (SSAS)

Help and Information

Getting SQL Server 2005 Assistance

Change History