SSAS Tabular model table load from query with #temp table fails

errorsssasssas-2016

We have several situations where complex queries are being used for creating/populating tables in our SSAS tabular model.

The actual use cases cannot be presented here, due to both proprietary information and pure length of queries, but the following test case illustrates the situation:

SELECT s.Servername, i.instanceID, i.InstanceName, i.ClientVersion, i.MajorVersion
  INTO #InstanceList
  FROM ServerLookup s
 INNER JOIN InstanceLookup i
         ON i.ServerID = s.ServerID

SELECT i.Servername, i.InstanceName, i.ClientVersion, i.MajorVersion, im.EntryDescription, im.MonitorID
  INTO #InstanceMonitoring
  FROM #InstanceList i
 INNER JOIN InstanceMonitor im
         ON im.InstanceID = i.InstanceID

SELECT i.ServerName, i.instanceName, m.MonitorName, m.MonitorDescription
  FROM #InstanceMonitoring i
 INNER JOIN MonitorLookup m on m.MonitorID = i.MonitorID

The query runs fin in SSMS, but when we attempt to create a table in our tabular model, it throws the following error message:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error. '.

There is no further information on the error.

I haven't been able to locate any documentation stating that temp tables can't be used in creating a tabular model table, and I'm reluctant to tell my developer community it can't be done without some information to back it up.

Best Answer

The SQL query can be converted to a stored procedure and then executed as the source SQL statement for the SSAS dimension/fact tables. As with a typical stored procedure, you can then use temp tables without any problems. A stored procedure can be called from SSAS by using an EXEC statement with the result set then used in the same manner as one from a view or table would be.

EXEC Schema.SP_Name
Related Question