tsql - 我不了解 OPENQUERY 函数

Question

我需要帮助理解 OPENQUERY-Structure。

我试图理解为什么以下查询（在 Microsoft SQL Server Management Studio 2016（版本 13.0.16106.4）中）只会产生错误

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;

而另一个查询没有

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;

当我执行第一个时，我总是收到错误消息“无效的列名'[KPIValue]'”，我不知道为什么。

我一直试图通过稍微玩一下语法来了解查询的结构，看看我是否能辨别出一种模式，因为到目前为止我无法在网上找到正确的解释。

例如我不知道为什么我需要在行中使用双引号

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

或者为什么“as ABC”似乎无关紧要。

如果我在 SSRS 中执行任何一个 MDX 查询，我都会得到一个结果集。

标签： tsqlreporting-servicessql-server-2016openquerymdx-query