SQLServer 2008: Query joining SSAS data with a table of any other database using openquery

Sometimes we may need to make a report that should contain data from our sales cube (for example) and supplement it with missing data that can only be found in the source relational schema or directly from another data source.

If the companion DDBB is in an instance of sql server, a fairly comfortable solution is to create a linked server. That allows us to do the MDX query from the SQL Server instance itself and make a JOIN to get the data we do not have in the cube. If we stop to think about it, perhaps it is not the most elegant and can even hide some lack of design, but surely worse things we have done.

Below are the four steps to follow to create the linked server and construct the trivial query to complete the annual sales total with the name of the store manager.

Steps to join SSAS data with databases with openquery

First we prepare the MDX query with the data of the cube that we need and a key field with which we can then relate the other table:

SELECT [Measures].[Sales amount] ON COLUMNS,
       [Business] [Store id].members ON ROWS
FROM [Sales]
WHERE [Time].[Year].&[2013]

We start by going to the Object Explorer and look for in our instance the section Objects of the server> Linked servers> New linked server. We complement with the data of the corresponding SSAS instance.

SQL Server SSAS linked server

Data from an SSAS instance

 

We edit the query on the Sql Server instance. Here we encapsulate the MDX query in a new SQL query with openquery. The tip here to facilitate the selection of fields is to use alias since the column header will default to the mdx format of the attribute / measure. Then we just do the JOIN as we want with the other table and we are ready.

SELECT ssas.Store_id, ssas.Sales_Amount, Store.manager
FROM (SELECT "[Business].[Store id].[MEMBER_CAPTION]" as Store_id
           , "[Measures].[Sales amount]" AS Sales_Amount
      FROM openquery (SSAS_INSTANCE, 'SELECT [Measures].[Sales amount] ON COLUMNS,
                                             [Business][Store id].members ON ROWS
                                      FROM [Sales]
                                      WHERE [Time].[Year].&[2013]')) ssas
LEFT JOIN [dbo].[Stores] store ON ssas.Store_id = store.Store_id