TSQL + MDX using OPENQUERY() - column names do not exist

Question

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

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:

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

Since its using the column name to have a 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.

So far I've tried these solutions:

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

Force mdx query to return column names

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

Alex_404 Alex_404 · Accepted Answer · 2018-02-21T13:01:47

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

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

TSQL + MDX using OPENQUERY() - column names do not exist

1 Answers