One of the most frequently asked questions is how to link an OLAP cube with a relational database in SQL Server. In this article, we will explore how to accomplish this by using a linked server. We will provide step-by-step instructions and examples using the AdventureWorksDW2014 sample database.

Creating a Linked Server

To connect an SSAS instance to the database engine, you need to create a linked server. This can be done using SQL Server Management Studio (SSMS) or T-SQL.

Using SQL Server Management Studio:

1. Open SQL Server Management Studio and navigate to Object Explorer.
2. Expand the Server Objects folder and right-click on Linked Servers.
3. Select New Linked Server.
4. In the New Linked Server dialog, specify the following values:
• Linked server: the name of the linked server object to be used in SQL Server.
• Provider: select “Microsoft OLE DB Provider for Analysis Services “.
• Product name: use “MSOLAP” for analysis services.
• Data source: the name of the Analysis Services instance installed.
• Catalog: the name of the analysis database that contains the OLAP cube.
5. After creating the linked server, you can browse its content from Object Explorer.

Using T-SQL:

You can also create the linked server using T-SQL commands. Use the sp_addlinkedserver and sp_addlinkedsrvlogin stored procedures as shown in the example below:

EXEC master.dbo.sp_addlinkedserver
  @server = N'SSAS_LINKEDSERVER',
  @srvproduct = N'MSOLAP',
  @provider = N'MSOLAP',
  @datasrc = N'MYLAPTOP\MSSQL',
  @catalog = N'AdventureWorks2014'

EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname = N'SSAS_LINKEDSERVER',
  @useself = N'False',
  @locallogin = NULL,
  @rmtuser = NULL,
  @rmtpassword = NULL

Executing MDX Queries

In order to retrieve data from an OLAP cube, you need to write a multidimensional expression (MDX) command. You cannot use a simple SELECT statement from the linked server object. To solve this problem, you can use the OPENQUERY() function to send an MDX command to the Analysis Services instance and retrieve the query result into the Database Engine.

Here is an example of executing an MDX query using OPENQUERY() and storing the result in a temporary table:

SELECT * INTO #TBLTEMP
FROM OPENQUERY(SSAS_LINKEDSERVER, 'SELECT NON EMPTY { [Measures].[Order Quantity], [Measures].[Discount Amount] } ON COLUMNS, NON EMPTY { ([Product].[Product Key].[Product Key].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works DW2014]')

Once the MDX command result is stored within a table, you can integrate it with other relational database objects such as tables, views, and functions.

Executing MDX Queries with a Length Bigger than 8000

One limitation of OPENQUERY() is that it can only execute a command with a length less than or equal to 8000 characters. To execute a longer command, you can use the EXECUTE() AT method, which can handle commands of type VARCHAR(MAX).

Here is an example of executing an MDX query with a length bigger than 8000 using the EXECUTE() AT method:

DECLARE @str VARCHAR(MAX)
SET @str = 'SELECT NON EMPTY { [Measures].[Order Quantity], [Measures].[Discount Amount] } ON COLUMNS,NON EMPTY { ([Product].[Product Key].[Product Key].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works DW2014]'
EXECUTE (@str) AT SSAS_LINKEDSERVER

If you encounter the error “Server ‘SSAS_LINKEDSERVER’ is not configured for RPC,” you can solve it by setting the RPC and RPC Out properties to true in the Linked Server Properties.

Inserting Data into a Table without Knowing the Result Table Structure

If you need to insert the result of the EXECUTE() AT method into a table, you must know the table structure in advance. To solve this problem, you can first retrieve a small chunk of data using OPENQUERY() to retrieve the table structure from the OLAP cube. Then, you can use the TRUNCATE TABLE statement to clear the data and use INSERT INTO … EXECUTE to pass the long command.

Here is an example:

-- Using OPENQUERY() to retrieve the table structure
SELECT * INTO #TBLTEMP
FROM OPENQUERY(SSAS_LINKEDSERVER, 'SELECT NON EMPTY { [Measures].[Discount Amount], [Measures].[Order Quantity] } ON COLUMNS, NON EMPTY { ([Product].[Product Key].[Product Key].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM (SELECT ({ [Product].[Product Key].&[606] }) ON COLUMNS FROM [Adventure Works DW2014])')

-- Clear data from created table
TRUNCATE TABLE #TBLTEMP

-- Execute the MDX command
DECLARE @str VARCHAR(MAX)
SET @str = 'SELECT NON EMPTY { [Measures].[Order Quantity], [Measures].[Discount Amount] } ON COLUMNS, NON EMPTY { ([Product].[Product Key].[Product Key].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM (SELECT ({ [Product].[Product Key].&[1], [Product].[Product Key].&[2], [Product].[Product Key].&[3], ... }) ON COLUMNS FROM [Adventure Works DW2014])'
INSERT INTO #TBLTEMP
EXECUTE (@str) AT SSAS_LINKEDSERVER

-- Retrieving data
SELECT * FROM #TBLTEMP

Other SSAS Statements

The methods described in this article can also be used to execute other statements supported by SSAS, such as XMLA and DMX queries.

Conclusion

In this article, we have explored how to link an OLAP cube with a relational database in SQL Server. We have discussed the creation of a linked server, executing MDX queries, handling queries with a length bigger than 8000, and inserting data into a table without knowing the result table structure. By following these steps, you can integrate the power of OLAP cubes with the flexibility of a relational database.