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:
Right-click on the Linked Servers folder and click on New Linked Server:
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
After creating the linked server, you can browse its 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:
|
1 |
EXEC master.dbo.sp_addlinkedserver @server = N'SSAS_LINKEDSERVER', @srvproduct=N'MSOLAP', @provider=N'MSOLAP', @datasrc=N'MYLAPTOP\MSSQL', @catalog=N'AdventureWorks2014' |
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.
|
1 |
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSAS_LINKEDSERVER',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL |
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:
|
1 2 3 |
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]') |
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:
|
1 2 3 4 5 |
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 |
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:
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
--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], [Product].[Product Key].&[4], [Product].[Product Key].&[5], [Product].[Product Key].&[6], [Product].[Product Key].&[7], [Product].[Product Key].&[8], [Product].[Product Key].&[9], [Product].[Product Key].&[10], [Product].[Product Key].&[11], [Product].[Product Key].&[12], [Product].[Product Key].&[13], [Product].[Product Key].&[14], [Product].[Product Key].&[15], [Product].[Product Key].&[16], [Product].[Product Key].&[17], [Product].[Product Key].&[18], [Product].[Product Key].&[20], [Product].[Product Key].&[19], [Product].[Product Key].&[21], [Product].[Product Key].&[22], [Product].[Product Key].&[23], [Product].[Product Key].&[24], [Product].[Product Key].&[25], [Product].[Product Key].&[26], [Product].[Product Key].&[27], [Product].[Product Key].&[28], [Product].[Product Key].&[29], [Product].[Product Key].&[30], [Product].[Product Key].&[31], [Product].[Product Key].&[32], [Product].[Product Key].&[33], [Product].[Product Key].&[34], [Product].[Product Key].&[35], [Product].[Product Key].&[36], [Product].[Product Key].&[37], [Product].[Product Key].&[39], [Product].[Product Key].&[38], [Product].[Product Key].&[40], [Product].[Product Key].&[41], [Product].[Product Key].&[42], [Product].[Product Key].&[43], [Product].[Product Key].&[44], [Product].[Product Key].&[45], [Product].[Product Key].&[46], [Product].[Product Key].&[47], [Product].[Product Key].&[48], [Product].[Product Key].&[49], [Product].[Product Key].&[50], [Product].[Product Key].&[606], [Product].[Product Key].&[605], [Product].[Product Key].&[604], [Product].[Product Key].&[603], [Product].[Product Key].&[602], [Product].[Product Key].&[601], [Product].[Product Key].&[600], [Product].[Product Key].&[599], [Product].[Product Key].&[597], [Product].[Product Key].&[598], [Product].[Product Key].&[596], [Product].[Product Key].&[595] } ) ON COLUMNS FROM [Adventure Works DW2014])' INSERT INTO #TBLTEMP EXECUTE (@str) AT SSAS_LINKEDSERVER --Retrieving data SELECT * FROM #TBLTEMP |
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.
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
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023