Unlock Insights: Distributed Queries with OLAP Server on SQL Server

Table of Contents

This article delves into the methodology of executing distributed queries in SQL Server to retrieve data from an OLAP Server. This capability is crucial for organizations leveraging Online Analytical Processing (OLAP) cubes for business intelligence and require seamless data access across different database systems. Understanding how to effectively query OLAP servers from SQL Server empowers users to gain comprehensive insights by integrating transactional and analytical data. This guide provides a detailed walkthrough of various techniques and considerations for optimal performance.

Summary¶

This document outlines the process of performing SQL Server distributed queries to access data residing within an OLAP Services (now known as Analysis Services) cube. Microsoft SQL Server’s architecture is designed to support querying across diverse OLE DB providers. This inherent flexibility allows users to interact with OLAP servers directly from their SQL Server environment. To facilitate these distributed queries, SQL Server offers several Transact-SQL functions and methodologies. The primary methods for querying OLAP data from SQL Server include utilizing the OPENQUERY and OPENROWSET Transact-SQL functions, as well as employing queries that leverage four-part names in conjunction with linked server configurations. These techniques enable SQL Server to act as a client to the OLAP server, retrieving and integrating multidimensional data into relational database operations.

For instance, establishing a linked server connection is a common approach. The sp_addlinkedserver stored procedure configures a link to the OLAP server, specifying the OLE DB provider, data source, and catalog. Once the linked server is established, functions like OPENQUERY can be used to execute queries against the OLAP cube. These queries, while formulated in a SQL-like syntax, are often translated into Multidimensional Expressions (MDX) to effectively interact with the OLAP cube structure. It’s important to note that the syntax supported for querying OLAP Services is a subset of standard SQL, primarily focused on selecting level and measure names. Queries that include MDX expressions will typically return flattened rowsets, a format detailed in OLE DB documentation, suitable for relational database consumption. For a deeper understanding of the specific SQL SELECT syntax supported by SQL Server OLAP Services, consulting the “Supported SQL SELECT Syntax” topic within the OLAP Services Books Online is recommended.

To enable seamless communication between SQL Server and OLAP servers, the MSOLAP OLE DB provider must be installed on the machine hosting the SQL Server instance. This provider acts as the bridge, translating SQL Server requests into a language understood by the OLAP server and vice versa. The MSOLAP OLE DB provider is typically installed as part of the OLAP client components during the SQL Server installation process. Ensuring this provider is correctly installed and configured is a prerequisite for successful distributed queries against OLAP cubes.

OPENROWSET and OPENQUERY Example¶

The following Transact-SQL code examples demonstrate the practical implementation of distributed queries targeting an OLAP server, utilizing both the OPENQUERY and OPENROWSET functions. It is crucial to replace the placeholder data source names and catalog names with values that accurately reflect your specific environment and OLAP server configuration. These examples are designed to be adaptable, allowing you to quickly integrate them into your existing SQL Server scripts with minimal modification. Understanding these examples will provide a solid foundation for constructing more complex and customized distributed queries.

------------------------------------------
--OPENROWSET for OLAP Server
------------------------------------------

SELECT a.*
FROM OpenRowset('MSOLAP','DATASOURCE=myOlapServer; Initial Catalog=FoodMart;',
'SELECT Measures.members ON ROWS,
[Product Category].members ON COLUMNS
FROM [Sales]') as a
go

-- Example of MDX with slicing --

SELECT a.*
FROM OpenRowset('MSOLAP','DATASOURCE=myOlapServer; Initial Catalog=FoodMart;',
'SELECT
 { Time.Year.[1997] } ON COLUMNS,
NON EMPTY Store.MEMBERS ON ROWS
FROM Sales
WHERE ( Product.[Product Category].[Dairy] )') as a

--------------------------------------------------
-- Linked Server Examples with OPENQUERY
--------------------------------------------------

EXEC sp_addlinkedserver
    @server='olap_server',
    @srvproduct='',
    @provider='MSOLAP',
    @datasrc='server',
    @catalog='foodmart'
go

-- MDX in OPENQUERY --

SELECT *
FROM OPENQUERY(olap_server,
'SELECT
{ Time.Year.[1997] } ON COLUMNS,
NON EMPTY Store.MEMBERS ON ROWS
FROM Sales
WHERE ( Product.[Product Category].[Dairy])' )

SELECT *
FROM OPENQUERY(olap_server, 'SELECT [customer], [quantity] FROM sales')

When executing the final SQL query example shown above, you might encounter an error message. This error typically arises because the SQL syntax supported by OLAP servers is a limited subset, primarily designed for measure and level names, not arbitrary column names like ‘customer’. The error message will typically resemble:

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider ‘MSOLAP’ reported an error. [OLE/DB provider returned message: Column name ‘customer’ is invalid. Only level or measure names can be specified.]

To rectify this query, you need to adjust it to select valid measure or level names from the OLAP cube. For instance, replacing [customer] and [quantity] with a valid measure like [unit sales] would resolve the error:

SELECT *
FROM OPENQUERY(olap_server, 'SELECT [unit sales] FROM sales')

However, it’s important to understand that even with corrected syntax, passing SQL statements in this simplified form to an OLAP server can sometimes lead to performance issues. Specifically, you might experience slow query execution, and in some environments, timeout errors may occur. This is often because the OLAP server is optimized for MDX queries rather than SQL-style queries, and translating and executing these SQL queries can be resource-intensive. A timeout error might manifest as:

OLE DB provider ‘MSOLAP’ reported an error. [OLE/DB provider returned message: Cannot open database ‘foodmart’] [OLE/DB provider returned message: OLAP server error: The operation requested failed due to timeout.]

This timeout error can be indicative of the OLAP server struggling to process the SQL query efficiently, particularly if the cube is large or complex, or if the network latency is high. Therefore, while SQL-like syntax is supported, it’s often more performant and reliable to utilize MDX directly when querying OLAP servers from SQL Server.

Linked Server Examples with Four-Part Names¶

The Transact-SQL code examples in this section illustrate the use of a linked server in conjunction with four-part names to query an OLAP cube. Building upon the previous examples, we assume a linked server named Olap_server has already been established. Four-part naming convention is a standard SQL Server method for referencing objects in linked servers, providing a familiar syntax for users accustomed to relational database queries. However, it’s crucial to understand the performance implications when applying this technique to OLAP cubes.

Select [Store:Store Name]
from Olap_server.FoodMart..[sales]
WHERE [Store:Store State]='WA'
go

Select [Product:Product Category], count ([Store:Store Name])
from Olap_server.FoodMart..[sales]
WHERE [Store:Store State]='WA'
GROUP BY [Product:Product Category]

While using four-part names to query OLAP cubes via a linked server is syntactically straightforward and may function correctly, it’s important to be aware of potential performance bottlenecks. These queries can sometimes take a considerable amount of time to return results to the client application. The four-part name syntax, while a native SQL Server concept, has inherent limitations when applied to the multidimensional nature of OLAP queries. SQL Server might interpret these queries in a way that necessitates reading a substantial portion, or even the entire fact table, from the OLAP server to perform operations like GROUP BY. This process can consume significant system resources on both the SQL Server and the OLAP server, leading to prolonged query execution times.

Microsoft’s best practice recommendation strongly advises against relying heavily on four-part names for complex OLAP queries. Instead, it is recommended to favor sending MDX statements directly through either the OPENROWSET or OPENQUERY functions, as demonstrated in the earlier examples. This approach offers several advantages. Firstly, it allows SQL Server to pass the command directly to the linked OLAP provider without attempting to parse and potentially misinterpret the query in a relational context. Secondly, it enables the use of the full power and flexibility of MDX, or the optimized subset of SQL that the OLAP provider natively supports. Finally, the rowset returned by the OPENQUERY function can be seamlessly integrated with other SQL operators within SQL Server, facilitating further data manipulation and analysis.

For basic MDX queries and GROUP BY operations that are designed to return relatively small datasets (such as those fitting within a single screen display), the expected response time should be minimal. Ideally, the result set should be generated in under 10 seconds, and often within 5 seconds, regardless of the overall size of the OLAP cube. If queries consistently exceed these performance benchmarks, it may be necessary to optimize the OLAP cube itself. One effective strategy is to build more aggregations within the cube structure using the usage-based analysis wizard provided in Analysis Services. Aggregations pre-calculate and store summarized data, significantly speeding up query performance by reducing the need for real-time calculations on large datasets.

Performance Tips¶

Optimizing the performance of distributed queries between SQL Server and OLAP Server is critical for efficient data retrieval and analysis. Here are several key performance tips to consider when working with these types of queries:

• Connection Management: SQL Server, by default, establishes two connections to the OLAP provider for each distinct query execution. One of these connections is intelligently cached and reused for subsequent queries. Consequently, re-running the same or similar query a second time often results in significantly faster execution due to connection reuse. This connection pooling mechanism helps reduce the overhead associated with establishing new connections for every query.

• Data Aggregation Strategy: To enhance query speed, consider grouping by additional dimensions in your queries. By adding more dimensions to the GROUP BY clause, you effectively reduce the granularity of the data being retrieved. This results in smaller result sets, which are processed and transferred more quickly. Essentially, retrieving summarized data is inherently faster than retrieving highly detailed data.

• ROLAP Considerations: A worst-case performance scenario can arise when the OLAP cube is stored using Relational OLAP (ROLAP) storage mode, especially if aggregations are not implemented. In ROLAP, the OLAP server, when faced with a query, might need to query back to the underlying relational database (which could be SQL Server itself) to fetch the fact table rows in real-time. Initiating a SQL Server distributed query in such a ROLAP environment can create a circular dependency and severely degrade performance. In these situations, it’s generally advisable to avoid SQL Server distributed queries altogether and consider alternative data access strategies.

• Direct OLAP Querying: If your primary need is to retrieve a result set directly from an OLAP server or a cube file, consider bypassing SQL Server distributed queries entirely. Instead, explore running SQL Server queries or Multidimensional Expressions (MDX) queries directly against the OLAP server or cube file. This can be achieved using OLE DB C++ applications or ADO (ADO*MD) applications. Direct querying eliminates the overhead of SQL Server acting as an intermediary and can often yield faster response times.

• In-Process Provider Loading: SQL Server is configured to load certain OLE DB providers in-process, which generally offers better performance. However, the MSOLAP provider, by default, is configured to load out-of-process. Microsoft strongly recommends modifying the options for the MSOLAP provider to load as in-process. This configuration change can significantly improve the performance of your OLAP queries. To modify this setting, follow these steps within SQL Server Management Studio:

  1. Navigate to Security -> Linked Servers in Object Explorer.
  2. Right-click Linked Servers and select New Linked Server.
  3. In the New Linked Server dialog, choose OLE DB Provider for OLAP Services from the “Provider Name” dropdown.
  4. Click on the Options tab on the left-hand side.
  5. Check the Allow InProcess checkbox.
  6. Click OK to save the changes.

  By allowing the MSOLAP provider to load in-process, you can minimize inter-process communication overhead and potentially achieve substantial performance gains for your distributed OLAP queries.

References¶

[Placeholder for relevant references if needed in a real article]

---

Feel free to share your experiences or ask any questions you have about performing distributed queries with OLAP Server on SQL Server in the comments below!