尝试在MDX中计算四分位数 [英] Trying to calculate quartiles in MDX

问题描述

我的数据如下:

ID                                   |PersonID  |CompanyID  |DateID  |Throughput |AmountType
33F467AC-F35B-4F24-A05B-FC35CF005981 |7         |53         |200802  |3          |0
04EE0FF0-511D-48F5-AA58-7600B3A69695 |18        |4          |201309  |5          |0
AB058AA5-6228-4E7C-9469-55827A5A34C3 |25        |69         |201108  |266        |0

具有大约一百万行.列名* ID引用其他表，因此它们可用作维度.

with around a million rows. The columns names *ID refers to other tables, so they can be used as dimensions.

我有一个OLAP多维数据集，其中吞吐量"列为度量"，其余为维度.

I have an OLAP cube with the column Throughput as Measure and the rest as dimensions.

我想计算吞吐量指标的四分位数1和3.

I want to calculate Quartile 1 and 3 of the Throughput measure.

我遵循了此指南: https://electrovoid.wordpress.com /2011/06/24/ssas-quartile/ 连同这篇文章:在Analysis Services中计算四分位数

I followed this guide: https://electrovoid.wordpress.com/2011/06/24/ssas-quartile/ together with this post: Calculating Quartiles in Analysis Services

从那些我尝试使用此MDX查询的地方开始:

From those I tried to use this MDX query:

WITH
SET selection as ([Dates].[Year].&[2014],[Dates].[Month].&[1])  

SET [NonEmptyIds] AS
 NonEmpty(
      [ThroughputID].[ID].[id]
   *[ThroughputID].[ID].[Id].ALLMEMBERS
  ,
  {[Measures].[Throughput]} * [selection]
 )
 SET [ThroughputData] AS 
ORDER
    (    
        [NonEmptyIds],  
        [Measures].[Throughput], 
        BASC
     )
MEMBER [Measures].[RowCount] AS COUNT (ThroughputData)
MEMBER [Measures].[i25] AS ( .25 *  ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i25Lo] AS FIX([i25])   - 1
MEMBER [Measures].[i25Rem] AS ([i25] - FIX([i25]))
MEMBER [Measures].[n25Lo] AS (ThroughputData.Item([i25Lo]), [Throughput])
MEMBER [Measures].[n25Hi] AS (ThroughputData.Item([i25Lo] + 1), [Throughput])
MEMBER [Measures].[Quartile1] AS [n25Lo] + ( [i25Rem] * ( [n25Hi] - [Throughput] ))

SELECT
selection ON 0,
[Measures].[Quartile1]
ON 1
FROM (SELECT [Dates].[Y-H-Q-M].MEMBERS ON 0 FROM [Throughput])

但是我得到:'查询(6，7)ID层次结构在Crossjoin函数中多次使用.'

But I get: 'Query (6, 7) The ID hierarchy is used more than once in the Crossjoin function.'

我对OLAP和MDX还是陌生的.任何想法出了什么问题，我应该如何正确计算四分位数?

I am quite new to OLAP and MDX. Any ideas what's wrong and how I should calculate the quartiles correct?

我读到某个地方，我需要ID维度才能在计算四分位数时获得一个包含所有值而不是聚合值的集合...

I read somewhere that I needed the ID dimensions to be able to get a set with all values instead of aggregated values when calculating Quartiles...

推荐答案

罪魁祸首是以下代码:

SET [NonEmptyIds] AS
 NonEmpty(
      [ThroughputID].[ID].[id]
   *[ThroughputID].[ID].[Id].ALLMEMBERS
  ,
  {[Measures].[Throughput]} * [selection]
 )

您不能在交叉联接中多次使用相同的层次结构.在这里，您已经使用[ThroughputID].[ID]两次.相反，请尝试以下操作:

You can't use the same hierarchy more than once in the cross-join. Here you have used [ThroughputID].[ID] twice. Instead try the below:

SET [NonEmptyIds] AS
 NonEmpty(
      [ThroughputID].[ID].[Id].ALLMEMBERS
  ,
  {[Measures].[Throughput]} * [selection]
 )

这篇关于尝试在MDX中计算四分位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！