Link DAX OpenQuery Output to SQL Server Temp Table

Question

I want to record measures from a Tabular model, as KPIs, and store the values in a table in SQL Server.

I have created a linked server from my SQL Server instance to my SSAS instance. I have written a stored procedure to execute the DAX code via OPENQUERY, with the intention of storing these in a temp table, before loading them in to the KPI table. I am using a temp table because I am querying multiple tabular models.

My problem occurs when I try to update my temp table with values from my OPENQUERY output. My OPENQUERY output is currently within a CTE, and I was hoping to do a simple join to the temp table, but because the output from the DAX query returns each column name within [ ], when I try to join on one of the OPENQUERY columns I receive the error "Invalid column name...".

E.g.

UPDATE temp
SET temp.[Current Contract Count] = cte.[Contract Count]
FROM #ServiceZoneKPIs AS temp
INNER JOIN tabular_cte AS cte
ON cte.[Copy of Service Zone Code] = temp.[ServiceZoneAlternateKey]

The error occurs because 'Copy of Service zone' does not exist in the OPENQEURY output; the output column name is [Copy of Service Zone].

I may well be missing a simple trick here?

How can I join an OPENQUERY output, returning tabular model data, to my T-SQL temp table?

Answer 1

If a column name has brackets, you need to escape them. You can see how if you use function QUOTENAME().

SELECT QUOTENAME('[awful column name]') 
-- Result: [[awful column name]]]

Note the extra closing brackets. You should try adding an extra pair of enclosing brackets together with an additional closing one for your column names that include brackets.

Assuming that your problem is tabular_cte's columns:

UPDATE temp
SET temp.[Current Contract Count] = cte.[[Contract Count]]]
FROM #ServiceZoneKPIs AS temp
INNER JOIN tabular_cte AS cte
ON cte.[[Copy of Service Zone Code]]] = temp.[ServiceZoneAlternateKey]