TSQL + MDX使用OPENQUERY（） - 列名不存在

I have a view against a OPENQUERY() which gets data from a SSAS cube. MDX query looks like this:

我有一个OPENQUERY()视图,它从SSAS多维数据集获取数据。 MDX查询如下所示:

WITH MEMBER [Measures].[Measure1] AS 
      (--calculation)
SELECT
    {[Measures].[Measure1]}
ON 0,
    NON EMPTY ([Dim1].[Dim_key].[Dim_key], [Dim2].[Dim_key].[Dim_key])
ON 1
FROM [Cube]
WHERE ([Dim3].[Hierarchy].[Level].[Member])

My problem is that when the WHERE filter results in 0 rows the view does not work, with error:

我的问题是,当WHERE过滤器导致0行时,视图不起作用,但有错误:

Invalid column name '[Dim1].[Dim_key].[Dim_key].[MEMBER_CAPTION]'.

Since its using the column name to have a GROUP BY

由于它使用列名称具有GROUP BY

How can I force it return at least one row? Or always return the column names? I cannot remove the NON EMPTY since whole set takes about 1 min to load.

我该怎么强迫它至少返回一行?或者总是返回列名?我无法删除NON EMPTY,因为整个设置大约需要1分钟才能加载。

So far I've tried these solutions:

到目前为止,我已尝试过这些解决方案:

MDX - Always return at least one row even if no data is available

MDX - 即使没有可用数据,也始终返回至少一行

Force mdx query to return column names

强制mdx查询返回列名

but it seems like it does not work since I have a where condition on another dimension.

但似乎它不起作用,因为我有另一个维度的where条件。

1 个解决方案

#1

Managed to figure it out. Added this measure which essentially replicates Dim1 members returned on the rows:

管理好弄清楚。添加了此度量,它基本上复制了行上返回的Dim1成员:

WITH MEMBER [Measures].[Measure1] AS 
        (--calculation)
     MEMBER [Measures].[Dim_Key] AS
        [Dim1].[Dim_key].CurrentMember.Member_Key
SELECT
    {[Measures].[Measure1]
     ,[Measures].[Dim_Key]}
ON 0,
    NON EMPTY ([Dim1].[Dim_key].[Dim_key], [Dim2].[Dim_key].[Dim_key])
ON 1
FROM [Cube]
WHERE ([Dim3].[Hierarchy].[Level].[Member])

So even if there are no rows I get the new measure back as one of the columns. If there are rows I get two additional columns (which have row tags) but I just don't use them in the view

因此,即使没有行,我也会将新度量作为其中一列返回。如果有行,我会得到两个额外的列(有行标签),但我只是不在视图中使用它们

#1