SSAS从查询问题创建表格模型表 [英] SSAS create tabular model table from query issue
问题描述
我们有几种情况,在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错误。 '。$
没有关于错误的更多信息。
我无法找到任何说明临时表不能用于创建表格模型表的文档,我不愿意告诉我的开发人员社区,如果没有一些信息来支持它,就不能这样做。
嗨JohnnyCache,
根据我在我的环境中的测试,我 当按临时表导入数据时,发现 ,它将提示错误返回:'OLE DB或ODBC错误。当我点击完成。好像它不能直接使用临时表。因此,作为解决方法
,我创建了一个商店程序来实现这一目标CREATE PROCEDURE dbo.MyTest
AS
BEGIN
设置NOCOUNT ON;
创建表#a(名称varchar(20),金额int)
插入#a值('a',10),('b',12),('c ',16)
创建表#b(名称varchar(20),id int)
插入#b值('a',1),('b',2) ,('c',3)
选择a.name,a.amount,b.id来自#aa左连接#bb on a.name = b.name
结束
最好的问候,
Zoe Zhi
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.
Hi JohnnyCache,
Based on my test in my environment, I find that when import data by temp table, it will prompt Error returned: 'OLE DB or ODBC error." when I click finish. It seems that it can't use temp table directly. So as a workaround I create a store procedure to achieve this goal
CREATE PROCEDURE dbo.MyTest AS BEGIN SET NOCOUNT ON; create table #a (name varchar(20), amount int) insert into #a values ('a', 10), ('b',12), ('c',16) create table #b (name varchar(20), id int) insert into #b values ('a', 1), ('b',2), ('c',3) select a.name, a.amount,b.id from #a a left join #b b on a.name=b.name END
Best Regards,
Zoe Zhi
这篇关于SSAS从查询问题创建表格模型表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!