Passing Queries from SQL Server to a Linked Analysis Server

The linked server feature of Microsoft® SQL Server™ 2000 allows you to execute queries against OLE DB data sources that are hosted on remote computers. There are no special requirements for using this feature with SQL Server 2000 Analysis Services, but there are some important points to note when configuring the two systems.

Security

Analysis Services uses security that is integrated with Microsoft Windows NT® 4.0 and Windows® 2000 to identify user accounts. It cannot be configured to recognize accounts created for use with SQL Server authentication. However, the MSSQLServer service on the linked server can be configured to log on using an account that has sufficient permissions to access Analysis Services.

Using the OPENQUERY Function

For best results with pass-through queries from SQL Server to Analysis Services, use the Transact-SQL function OPENQUERY to execute SQL commands between servers. OPENQUERY sends the commands of the query directly to the Analysis server, which then returns flattened rowsets (as described in the OLE DB documentation) that contain the requested data. The syntax of the passed query is not limited to the abbreviated SQL SELECT options supported by Analysis Services, but can also include Multidimensional Expressions (MDX) commands.

Executing SQL Queries using the OPENQUERY Function

The OPENQUERY function accepts two parameters: the name of the linked server and the text of the query to pass.

Examples

A. Returning Total Sales Grouped by Customer Gender

This query returns the total sales grouped by customer gender:

select * from openquery( LINKED_OLAP, 'select [Customer Gender:Gender], 
sum([measures:unit sales]) from sales group by [Customer Gender:Gender]')

B. Returning Total Sales Grouped by Gender and Education

This query returns the total sales grouped by customer gender and education level:

select * from openquery( LINKED_OLAP, 
'select [Customer Education Level:Education], [Customer Gender:Gender], 
sum([measures:unit sales]) from sales 
group by [Customer Education Level:Education], 
[Customer Gender:Gender]' )

Because there is a limitation in Analysis Services that causes GROUP BY and DISTINCT queries to produce multiple rows that satisfy the grouping and/or distinct functions (instead of just one), it may be necessary to copy the rows to a temporary table and reduce them further.

The following examples show how SQL Server can be used to merge the results of a query from Analysis Services.

C. Performing a DISTINCT Operation

In this query, SQL Server performs an additional DISTINCT operation on the data retrieved by Analysis Services:

select distinct * from openquery( LINKED_OLAP, 
'select distinct [Customer Location:Country], 
[Customer Location:State Province], 
[Customer Location:City] 
from sales' )

D. Using the ORDER BY Command

In this query, the ORDER BY command sorts the values retrieved by Analysis Services:

select distinct * from openquery( LINKED_OLAP, 
'select distinct [Customer Location:Country!name], 
[Customer Location:State Province!name], 
[Customer Location:City!name] 
from sales' ) 
order by
[Customer Location:Country!name], 
[Customer Location:State Province!name], 
[Customer Location:City!name]

E. Guaranteeing the Correctness of the GROUP BY Command

In this query, SQL Server guarantees the correctness of the GROUP BY command (because Analysis Services might not coalesce all of the returned rows):

select [Customer Location:Country!name], [Customer Gender:Gender!name], 
sum([measures:unit sales]) 
from openquery( LINKED_OLAP, 
'select [Customer Location:Country!name], [Customer Gender:Gender!name], 
sum([measures:unit sales]) from sales 
group by [Customer Location:Country!name], 
[Customer Gender:Gender!name]') 
group by [Customer Location:Country!name], [Customer Gender:Gender!name]

F. Using a WHERE Clause

This query combines all elements of the preceding examples and includes a WHERE clause:

select 
[Customer Location:Country!name], 
[Customer Gender:Gender!name], 
[Product:Product Family!name], 
[Product:Product Department!name], 
[Product:Product Category!name], 
[Product:Product Subcategory!name], 
sum([measures:unit sales]) 
from openquery( LINKED_OLAP, 
'select 
[Customer Location:Country!name], 
[Customer Gender:Gender!name], 
[Product:Product Family!name], 
[Product:Product Department!name], 
[Product:Product Category!name], 
[Product:Product Subcategory!name], 
sum([measures:unit sales]) 
from sales 
where 
[Product:Product Family!name] = ''Food'' and 
[Product:Product Department!name] = ''Baked Goods' '
group by 
[Customer Location:Country!name], 
[Customer Gender:Gender!name], 
[Product:Product Family!name], 
[Product:Product Department!name], 
[Product:Product Category!name], 
[Product:Product Subcategory!name] ') 
group by 
[Customer Location:Country!name], 
[Customer Gender:Gender!name], 
[Product:Product Family!name], 
[Product:Product Department!name], 
[Product:Product Category!name], 
[Product:Product Subcategory!name] 
order by 
[Customer Location:Country!name], 
[Customer Gender:Gender!name], 
[Product:Product Family!name], 
[Product:Product Department!name], 
[Product:Product Category!name], 
[Product:Product Subcategory!name]

Executing MDX Queries Using the OPENQUERY Function

Because the OPENQUERY function causes SQL Server to pass the text of the query directly to Analysis Services, you can use MDX syntax for complex multidimensional queries. The result sets from MDX queries appear as flattened rowsets. For more information about how a multidimensional result set is mapped to a tabular rowset in OLE DB for OLAP, see the OLE DB documentation.

Example

The following example submits an MDX query using the OPENQUERY function.

select * from openquery 
( LINKED_OLAP, 'select { measures.[unit sales] } on columns,
 non empty nest( nest( [customer location].[country].members, 
[gender].members ), [product category].[bread].children ) on rows 
from sales ')

Avoiding Four-Part Naming

It is possible to access the data of a cube directly from SQL Server using queries with four-part naming. (The four parts are linked-server-name, catalog, schema, and table.) However, this option is not recommended because SQL Server attempts to copy the contents of the entire fact table and then perform the calculations for aggregating the data itself, substantially increasing the query response time.