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.