How to use Openquery to make a join between a SSAS cube and a table from any other database

By il_masacratore on Thu, 12/01/2016 - 11:56
Databases

Imagine you need a report with data from an OLAP sales cube, and also with data from a table of the relational data source, or from a table of an external or remote database.

If the relational database belongs to a SQL Server instance, you can create a linked server. With a linked server you can construct a MDX query in the SQLServer instance where you have the cube, joining with an external database to complete the data with information from tables of the relational database. Perhaps this is not the most smart solution, and could be better to redesign something in the ETL process, but sometimes could be a practical or temporary solution to a specific need.

Steps to make a MDX join with data from an OLAP cube and a table from another database

Next, the three steps to create the linked server and create a simple query to complete a total anual amount of sales with the name of the manager of the shop, joining the 'sales' cube with a 'store' table:

  • Prepare the query MDX with the data from the SSAS cube, including a field that we will use as a foreign key in the join with the table of the relational database
SELECT [Measures].[Sales amount] ON COLUMNS,
 [Business].[Store id].members ON ROWS
FROM [Sales]
WHERE [Time].[Year].&[2013]

 

  • In the Objects Explorer of SSMS search in the tree of the relational database instance the section 'Linked Servers', and press the right button and select in contextual menu 'New linked server'. Complete the fields with connection info to the SSAS instance to create the linked server.

Datos de una instancia SSAS 

 

  • Edit the query that you will execute in the relational database. This query encapsulate the previous MDX query using openquery and the linked server created in previous step.

The tip to be able to select fields of the cube is to use alias, because by default the column header have the MDX format of the attribute or measure of the cube. Next, just do the join with the table using the field selected before to use as foreign key .

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

 

That's all!

Prueba Semrush gratis 14 días!