SSAS从查询问题创建表格模型表

问题描述

我们有几种情况，在SSAS表格模型中使用复杂查询来创建/填充表格。



实际用例无法在此处显示，到期对于专有信息和纯粹的查询长度，以下测试用例说明了这种情况：



    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

     来自#InstanceList我是
      INNER JOIN InstanceMonitor im

              ON im.InstanceID = i.InstanceID



    SELECT i.ServerName，i.instanceName，m.MonitorName，m.MonitorDescription

     来自#InstanceMonitoring i

      INNER JOIN MonitorLookup m on m.MonitorID = i.MonitorID



查询在SSMS中运行fin，但是当我们尝试在表格模型中创建表时，它会引发以下错误：



   无法保存对服务器的修改。返回错误：'OLE DB或ODBC错误。 '。$


没有关于错误的更多信息。



我无法找到任何说明临时表不能用于创建表格模型表的文档，我不愿意告诉我的开发人员社区，如果没有一些信息来支持它，就不能这样做。

解决方案

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:

    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.

解决方案

这篇关于SSAS从查询问题创建表格模型表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！