Hadi Fadlallah
quering OLAP cube using OPENQUERY() option

Linking relational databases with OLAP cubes

January 10, 2020 by

One of the most asked questions is how to link an OLAP cube with a relational database, or in another way how to join the result of an MDX query with a table stored in a relational database.

In this article, we will illustrate how to link a SQL Server Analysis Services instance with a relational database engine using a linked server, then we will give some examples and problems troubleshooting. Examples are conducted 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. You can do this from SSMS or using T-SQL. In this section, we will provide a step-by-step guide for both approaches.

Using SQL Server Management Studio

After opening SQL Server Management Studio, in Object Explorer go to Server Objects | Linked Servers as shown in the image below:

this image shows how to navigate to Linked servers folder in the Object explorer

Right-click on the Linked Servers folder and click on New Linked Server:

adding a new linked server from the object explorer

In the New Linked Server dialog, you need to specify the following inputs values:

  • Linked server: the name of the linked server object to be used in SQL Server
  • Provider: you have to select “Microsoft OLE DB Provider for Analysis Services <xx.x>”. (Where <xx.x> is the SQL server version)
  • Product name: for analysis services, MSOLAP must be used
  • Data source: the name of the Analysis Services instance installed
  • Catalog: the name of the analysis database that contains the OLAP cube

Configuring linked server to connect with OLAP cube

After creating the linked server, you can browse its content from Object Explorer:

showing OLAP cube content from Object explorer

The objects shown under the Analysis Service catalog are the dimensions and measures of the OLAP cube. But these objects are useless since they cannot be queried using a simple SELECT query since you need to pass an MDX expression to retrieve data from the OLAP cube.

Using T-SQL

Another method is to create the linked server using a SQL command. You have to use the master.dbo.sp_addlinkedserver and master.dbo.sp_addlinkedsrvlogin stored procedures as following:

Where @server is the linked server object name, @srvproduct is the product name, @provider is the provider, @datasrc is the data source and @catalog is the initial catalog.

The second command is used to add a default login for the linked server; it will use the current user credentials used to connect to the database engine.

Executing MDX query from T-SQL query

In order to retrieve data from an OLAP cube, you need to write a multidimensional expression (MDX) command which cannot be done using SQL statements. So, you cannot use a select statement from the linked server object to retrieve data from the cube.

To solve this problem, you need to use OPENQUERY() option to send and MDX command to the Analysis Services instance and to bring the query result into the Database Engine.

Another thing worth to mention is that using OPENQUERY() – in general – is more efficient than querying the linked server via T-SQL since it sends the command to be executed on the linked server instead of bringing all data to the Database Engine and then querying this data. To read more information about each approach you can refer to the following article: Querying remote data sources in SQL Server.

OPENQUERY() takes two parameters: (1) the linked server object and (2) the statement that we need to execute on this linked server.

You can store the result of the command passed by OPENQUERY() into a table using the SELECT INTO statement. As an example:

quering OLAP cube using OPENQUERY() option

From the image above, you can see that the column names of the result table are fully qualified and it is indicated whether it belongs to measures or to a dimension.

After that the MDX command result is stored within a table, you can simply integrate it with other relational database objects such as other tables, views, functions…

How to execute MDX queries with a length bigger than 8000?

One of the main limitations of OPENQUERY() is that it can execute a command having a length less or equal to 8000 or it will be truncated. If you need to execute a similar command you can use the EXECUTE() AT method since it can execute a command of type VARCHAR(MAX).

As shown in the example below, first you have to store the whole command within a variable of type VARCHAR(MAX) then execute it:

You may encounter the following error the first time you try to use this approach:

Server ‘SSAS_LINKEDSERVER’ is not configured for RPC

This can be solved by setting the RPC and RPC Out properties to true in the Linked Server Properties as shown in the image below:

Configruing RPC option in the Linked server properties

For more information and troubleshooting for EXECUTE() AT method, you can refer to the following TechNet Wiki page: SQL Server – Execute At Linked Server.

How to insert data into a table without knowing the result table structure?

Another issue is that if we need to insert the result of the EXECUTE() AT method, we need to know the table structure since we are not able to use SELECT INTO statement to create the table based on the query result. We can only use INSERT INTO … EXECUTE which requires that the table already exists.

To solve this problem, we must first pass an MDX command that returns a small chunk of data using OPENQUERY() to retrieve the table structure from the OLAP cube. Next, we use the TRUNCATE() statement to clear data, then we use INSERT INTO … EXECUTE to pass the long command.

Example

Executing long MDX command and storing result within a temp table

Other SSAS statements

One last thing worth to mention is that you can use the methods we illustrated in this article to execute other statements supported by SSAS such as XMLA and DMX queries.

Conclusion

In this article, we described how to link the result of an MDX query executed over an OLAP cube with data stored within a relational database. We mentioned two methods of executing MDX queries from T-SQL queries and how to store result within a relational database.

Hadi Fadlallah
Analysis Services (SSAS), OLAP cube, T-SQL

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional. On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques. Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website. View all posts by Hadi Fadlallah

168 Views