0

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.