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.