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?