ssas - 关于 SSAS 和 MDX 的现任成员

Question

我将向您展示我对 MDX 请求的想法，这些想法是错误的，因为我没有得到预期的结果，但为什么呢？

选择语句如下：

SELECT {nballPatsTransfusedOneSpe , nballPatsOneSpe,nballPatsTransfusedOfSameSpes ,nballPatsOfSameSpes ,localRate}  ON 0
FROM [BDD PBM]

只有一个变量是有趣的：localRate。

它被定义为：

member localRate as ([Dim misc].[Speciality Id].&[{754EFA13-AA1B-4E57-AB73-893AF7C52127}], rate)

我认为 localRate 是一个包含 2 个显式信息的元组：一个用于专业 ID，另一个作为度量（即使它不属于 [Measures]），立方体的隐式维度是按照标准规则计算的（默认成员，所有成员，第一个成员）。两者都产生一个数字，因为 rate 产生一个整数。而且我认为在请求结束之前，“Speciality Id”属性的默认成员是上面显示的 Guid（十六进制数），它可能出现在“Dim misc”中的任何地方（在属性层次结构中，或在所有用户层次结构）。

率定义为：

MEMBER rate AS (existing([Dim misc].[SpePats2].CurrentMember, allPatsOneSpe )).count

它通过 localRate 中给出的专业过滤患者列表 (allPatsOneSpe)，然后计算行数。在全球范围内，它返回给定专业的患者数量。我不能在行中使用“专业 ID”和列中的费率设置请求，因为我必须使用一组专业 ID 计算百分比，所以我必须有一个专业列表和一个会员费率，给定一个专业 id，返回分子除以分母（此处仅给出分母）。

allPatsOneSpe 如下：

SET allPatsOneSpe AS Descendants([Dim misc].[SpePats].currentmember,2)

这肯定是错误所在。这些想法对吗？

谢谢你。

这是整个请求：

with

MEMBER spename AS [DimTransfusion].[TransfusionPatients].[Speciality Id].&[{7XXXXXX7-AA1B-4E57-AB73-89XXXXXXXX27}].FirstChild.MemberValue
SET spesSame AS filter([Dim misc].[SpePats].[Speciality Id].MEMBERS
,[Dim misc].[SpePats].CurrentMember.FirstChild.MemberValue = spename)--filter
SET spesSameTransfusion AS filter([DimTransfusion].[TransfusionPatients].[Speciality Id].MEMBERS
,[DimTransfusion].[TransfusionPatients].CurrentMember.FirstChild.MemberValue = spename)--filter

SET allPatsOfSameSpes AS Generate(spesSame, Descendants([Dim misc].[SpePats].CurrentMember,2))
SET allPatsTransfusedOfSameSpes AS Generate(spesSameTransfusion , Descendants([DimTransfusion].[TransfusionPatients].CurrentMember,3))
MEMBER nballPatsOfSameSpes AS allPatsOfSameSpes.count
MEMBER nballPatsTransfusedOfSameSpes AS allPatsTransfusedOfSameSpes.count

SET allPatsOneSpe AS Descendants([Dim misc].[SpePats].CurrentMember,2)
SET allPatsTransfusedOneSpe AS Descendants([DimTransfusion].[TransfusionPatients].[Speciality Id].[{XXXXXXX7-AA1B-4E57-AB73-8XXXXXXXX}],2)
MEMBER nballPatsOneSpe AS allPatsOneSpe.count
MEMBER nballPatsTransfusedOneSpe  AS allPatsTransfusedOneSpe.count
MEMBER rateOneSpe AS nballPatsTransfusedOneSpe  / nballPatsOneSpe 

MEMBER [Measures].rate AS --(existing([Dim misc].[SpePats2].CurrentMember, filter(allPatsTransfusedOneSpe ,true ) )).count
-- /
     (existing([Dim misc].[SpePats2].CurrentMember, allPatsOneSpe )).count



SET OrderedData AS ORDER(
NONEMPTY(spesSame , [Measures].rate),[Measures].rate, BASC)
MEMBER [Measures].[RowCount] AS COUNT (OrderedData)

MEMBER [Measures].[i0p02] AS ( .02*  ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i0p02Lo] AS FIX([i0p02]) - 1
MEMBER [Measures].[i0p02Rem] AS ([i0p02] - FIX([i0p02]))
MEMBER [Measures].[n0p02Lo] AS (OrderedData.Item([i0p02Lo]), [Measures].[rate])
MEMBER [Measures].[n0p02Hi] AS (OrderedData.Item([i0p02Lo] + 1), [Measures].[rate])
MEMBER [Measures].[PCT0p02] AS [n0p02Lo] + ( [i0p02Rem] * ( [n0p02Hi] - [n0p02Lo] ))


MEMBER [Measures].[i0p2] AS ( .2*  ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i0p2Lo] AS FIX([i0p2]) - 1
MEMBER [Measures].[i0p2Rem] AS ([i0p2] - FIX([i0p2]))
MEMBER [Measures].[n0p2Lo] AS (OrderedData.Item([i0p2Lo]), [Measures].[rate])
MEMBER [Measures].[n0p2Hi] AS (OrderedData.Item([i0p2Lo] + 1), [Measures].[rate])
MEMBER [Measures].[PCT0p2] AS [n0p2Lo] + ( [i0p2Rem] * ( [n0p2Hi] - [n0p2Lo] ))

MEMBER [Measures].[i0p5] AS ( .5*  ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i0p5Lo] AS FIX([i0p5]) - 1
MEMBER [Measures].[i0p5Rem] AS ([i0p5] - FIX([i0p5]))
MEMBER [Measures].[n0p5Lo] AS (OrderedData.Item([i0p5Lo]), [Measures].[rate])
MEMBER [Measures].[n0p5Hi] AS (OrderedData.Item([i0p5Lo] + 1), [Measures].[rate])
MEMBER [Measures].[PCT0p5] AS [n0p5Lo] + ( [i0p5Rem] * ( [n0p5Hi] - [n0p5Lo] ))
--MEMBER [Measures].[PCT0p5] AS Median(OrderedData,  [Measures].[Weight])

MEMBER [Measures].[i0p8] AS ( .8*  ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i0p8Lo] AS FIX([i0p8]) - 1
MEMBER [Measures].[i0p8Rem] AS ([i0p8] - FIX([i0p8]))
MEMBER [Measures].[n0p8Lo] AS (OrderedData.Item([i0p8Lo]), [Measures].[rate])
MEMBER [Measures].[n0p8Hi] AS (OrderedData.Item([i0p8Lo] + 1), [Measures].[rate])
MEMBER [Measures].[PCT0p8] AS [n0p8Lo] + ( [i0p8Rem] * ( [n0p8Hi] - [n0p8Lo] ))

MEMBER [Measures].[i0p98] AS ( .98*  ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i0p98Lo] AS FIX([i0p98]) - 1
MEMBER [Measures].[i0p98Rem] AS ([i0p98] - FIX([i0p98]))
MEMBER [Measures].[n0p98Lo] AS (OrderedData.Item([i0p98Lo]), [Measures].[rate])
MEMBER [Measures].[n0p98Hi] AS (OrderedData.Item([i0p98Lo] + 1), [Measures].[rate])
MEMBER [Measures].[PCT0p98] AS [n0p98Lo] + ( [i0p98Rem] * ( [n0p98Hi] - [n0p98Lo] ))


member Ecart1 as [Measures].[PCT0p2]-[Measures].[PCT0p02]
member Ecart2 as [Measures].[PCT0p5]-[Measures].[PCT0p2]
member Ecart3 as  [Measures].[PCT0p8] - [Measures].[PCT0p5]
member Ecart4 as [Measures].[PCT0p98] -[Measures].[PCT0p8]

member localRate as ([Dim misc].[Speciality Id].&[{77-AA1B-4E57-AB73-27}], [Measures].rate)



SELECT{ PCT0p02, Ecart1, Ecart2, Ecart3, Ecart4  ,PCT0p98 ,localRate } on 0
--SELECT {nballPatsTransfusedOneSpe , nballPatsOneSpe,nballPatsTransfusedOfSameSpes ,nballPatsOfSameSpes ,localRate}  ON 0
--SELECT {} ON 0, {OrderedData} ON 1 
FROM [BDD PBM]
--[Dim misc].[SpePats].[Hospital Id].&[{274DED7-8605-EA4E741DF116}].&[CH de ABCD].&[{26FBAA2E661}]

--WHERE [Dim misc].[SpePats].&[{7-AA1B-4E57-AB73-89127}]

标签： ssasmdx