I'm not understanding the OPENQUERY Function

141 views Asked by At

I need help understanding the OPENQUERY-Structure.

I am trying to understand why the following Query (in Microsoft SQL Server Management Studio 2016 (Version 13.0.16106.4)) only produces an error

WITH MDXQuery
(
  [AKPIValue]  
)
as
( 
  SELECT     
    convert(decimal(20,2) ,"[KPIValue]") as AKPIValue    
  FROM OPENQUERY(SSAS2,
    'WITH
       MEMBER [KPIValue] AS 10 --(([Measures].[AverageOverTime], [Signal].[Type].[E-Current]))
    SELECT NON EMPTY
    {
      [KPIValue]
    }ON COLUMNS
    ,
    {
      [Time].[Week Calendar].[Day]} * {[Device].[Devices].&[{873AADF4-3D7A-4F25-A6BD-EC1E0A7077C6}]
    } 
    dimension Properties MEMBER_CAPTION, MEMBER_KEY ON ROWS
    FROM 
    (
      SELECT
      {
        [Time].[Month].&[2019-12-01T00:00:00]:[Time].[Month].&[2020-03-01T00:00:00]
      } ON COLUMNS     
      from [Measurements]
    )' 
  )
)


SELECT
  MDXQuery.*
From 
  MDXQuery;

while another query doesn't

with MQ
(
  [LastActivity]
  ,[LurtActivity]
)
as
(
SELECT 
  convert(nvarchar(max),"[Measures].[LastActivity]") as ABC
  ,convert(nvarchar(max),"[Measures].[LurtActivity]") as ABCDE
FROM OPENQUERY(SSAS2,
  'with 
     member [Measures].[LastActivity] as Tail(nonempty([Time].[Minute].[Minute].MEMBERS,[Measures].[Count]),1).Item(0).Name
     member [Measures].[LurtActivity] as 10
   select 
     NON EMPTY 
     {
       [Measures].[LastActivity]
       ,[Measures].[LurtActivity]
     } ON COLUMNS
   from [Measurements]' 
))

Select MQ.* from MQ;

When I execute the first one I always get the error Message "Invalid column name '[KPIValue]'" and I don't know why.

I've been trying to understand the structure of the query by playing with the syntax a bit to see if I could discern a pattern, as I was unable to find proper explanations online so far.

For example I don't know why I need to use double quotation marks in the line

convert(nvarchar(max),"[Measures].[LastActivity]") as ABC

Or why the "as ABC" seems to be irrelevant.

If I execute either of the MDX-Queries in SSRS I get a Resultset back.

0

There are 0 answers