Passing Queries from SQL Server to a Linked OLAP Server

If you are using Microsoft® SQL Server™ as your database management system, you can use the system stored procedure sp_addlinkedserver to add a linked OLAP server. This action enables you to execute distributed queries against OLE DB data sources that are connected to the linked OLAP server.

You can use the OPENQUERY function within a SQL Server SELECT statement to pass queries to the linked OLAP server. The passed query, which is the second argument of the OPENQUERY function, is limited to the abbreviated SELECT syntax supported by OLAP Services but can include MDX syntax. A passed query that includes MDX returns “flattened rowsets” as described in the OLE DB documentation. For more information about the SELECT syntax supported by SQL Server OLAP Services, see Supported SQL SELECT Syntax.

Syntax

For this use, the syntax is more specific than that provided in the SQL Server documentation. Note the required program ID MSOLAP.

sp_addlinkedserver  @server = 'server', @srvproduct = 'product_name',
    @provider = 'MSOLAP', @datasrc = 'data_source',
    @catalog = 'catalog'

Arguments

@server = 'server'

Is a name you create for the linked OLAP server. This name is used as the first argument of the OPENQUERY function.

@srvproduct = 'product_name'

Is the product name of the OLE DB data source to add as a linked server. You can leave the product_name value NULL, which is the default.

@provider = 'MSOLAP'

Is the program ID of OLAP Services. OLAP Services is expected to be registered on the server computer running SQL Server.

@datasrc = 'data_source'

Is the machine name of the linked OLAP server.

@catalog = 'catalog'

Is the name of the OLAP Services database containing the cube that is queried by the passed SELECT statement. Database names are displayed in the OLAP Manager tree view. In the following example, the database name is “foodmart”, and the cube name is “sales”.

Example

EXEC sp_addlinkedserver

     @server='olap_server',

     @srvproduct='',

     @provider='MSOLAP',

     @datasrc='server',

     @catalog='foodmart'

SELECT * FROM OPENQUERY(olap_server,

     'SELECT [customer], [quantity] FROM sales')