Combining MDX with T-SQL in One Result Set for SSRS (Hybrid Query)

• Download source - 15 KB

Introduction

This is an article about the Hybrid Query and how to combine data from a Cube using MDX and data from a relational database using T-SQL.

Background

Hybrid Query can be used if:

1. You want to combine data from cube with data from a relational database (additional data that does not exist in the cube) for reporting purposes.
2. You want to send MDX result set to users by SQL Server Agent job.
3. You are data mining and want to predict values based on data within a SQL database.
4. You want to combine Aggregated data from MDX with data in OLTP.

In this article, I will talk about the first point, and I will demonstrate with examples how to show the combined result set in SSRS.

Using the Code

The first thing we need to do is to set up a Linked Server for OLAP database from OLTP server.

1. Connect to the Database Engine in Management Studio.

   

   Expand Server Objects, right-click Linked Servers, and click New Linked Server.

   

2. Define a name for the linked server, and set the following options as shown in the screenshot below:
   • Provider: Microsoft OLE DB Provider for Analysis Services 10.0
   • Product name: MSOLAP.4
   • Data source: (the name of your OLAP server)
   • Catalog: (the name of the OLAP database)

   Then press OK.

   

3. Expand the Linked Server node to make sure it's added successfully.

   Now let's test the linked server (LINKED_OLAP_SERVER in this example) by running the following query from the OLTP server:

   SQL

   select * from openquery(LINKED_OLAP_SERVER, 'SELECT NON EMPTY _
   { [Measures].[Sales Amount] } ON COLUMNS FROM [Sales]')

   Note that the query return results which mean the Linked Server works fine! In this query, I used OPENQUERY, so what is the OPENQUERY command? The OPENQUERY command is used to executes pass-through query on specific linked server, it's used as a communication channel between two database systems, it's like a translater that allows OLTP to communicate with other database systems, to run queries against other databases such as (Oracle, Sybase and OLAP), we will talk more about OPENQUERY later.

   

4. Let's assume we have a Products Feedback table in the source database, and this table stores customer's feedback on each (if any) products in the OLAP cube. I created this table for this example.
   SQL

   CREATE TABLE dbo.ProductFeedback(
       ProductId int NOT NULL,
       [Customer's Comments] nvarchar(255) NULL
   );    
   INSERT INTO ProductFeedback(ProductId, [Customer's Comments])
       VALUES (486, 'It says oversize adult on the order and on the ...'),
                   (225, 'fits nice, buckles work good'),
                   (225, 'Perfect......'),
                   (225, 'So far, so good'); 

5. Now, we need to join dbo.ProductFeedback table with the result set from MDX query to generate combined result set.

   This can be done by OPENQUERY argument as mentioned earlier, to extract the data from Sales Cube in tabular format.

   SQL

   WITH MDX_Query
   (    
       
       [Sales Amount],
       [English Product Name],
       [Product Key]
   )
   AS
   (
       SELECT
           CONVERT(decimal(20,2), "[Measures].[Sales Amount]" ) AS [Sales Amount],
           CONVERT(nvarchar, "[Dim Product].[English Product Name]._
           [English Product Name].[MEMBER_CAPTION]")  AS [English Product Name],
           CONVERT(int, CAST("[Dim Product].[Product Key].[Product Key]._
           [MEMBER_CAPTION]" AS nvarchar))  AS [Product Key]
   
       FROM OPENQUERY(LINKED_OLAP_SERVER,
           'SELECT
               [Measures].[Sales Amount]
   
               ON COLUMNS,
               {
               CROSSJOIN([Dim Product].[English Product Name].[English Product Name].Members,
               [Dim Product].[Product Key].[Product Key].Members)
               }
               ON ROWS
   
             FROM
               [Sales]'
       )
   )
   
   SELECT
   MQ.[Product Key],
   MQ.[English Product Name],
   MQ.[Sales Amount],
   PF.[Customer's Comments]
   
   FROM MDX_Query AS MQ
   INNER JOIN
   [dbo].[ProductFeedback] AS PF
   ON PF.[ProductId] = MQ.[Product Key]

   The above query returns all products list from MDX query and also returns the Customer's feedback from OLTP database, the results of this query can be used in RDL report with datasource of type "Microsoft SQL Server".

6. Add new report, SharedDataSource should point to the OLTP database as explained below:

   

7. Add new DataSet to the report and paste the query into its designer as shown below:

   

8. Finally, run the report and check the results:

   

Points of Interest

I hope this article will be helpful for anyone who faces a problem in integrating OLTP with OLAP cubes.

Final words... if you can build a Report on MDX only then do it without this method, but sometimes you need to combine data in OLTP, in this case use the Hybrid Query.

Any comments, suggestions or thoughts are always welcome!

History

• 27^th September, 2014: Initial version