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…”.
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?
If a column name has brackets, you need to escape them. You can see how if you use function
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
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]