我的数据如下所示：
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引用其他表，因此它们可用作维度。
我有一个OLAP多维数据集，其中“吞吐量”列为“度量”，其余为维度。
我想计算吞吐量指标的四分位数1和3。
我遵循了该指南：https : //electrovoid.wordpress.com/2011/06/24/ssas-quartile/以及以下文章：在Analysis Services中计算四分位数
从那些我尝试使用此MDX查询的对象中：
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函数中多次使用。”
我对OLAP和MDX相当陌生。任何想法有什么问题，我应该如何计算正确的四分位数？
我读过某个地方，我需要ID维度才能在计算四分位数时获得一个包含所有值而不是聚合值的集合...
罪魁祸首是以下代码：
SET [NonEmptyIds] AS
NonEmpty(
[ThroughputID].[ID].[id]
*[ThroughputID].[ID].[Id].ALLMEMBERS
,
{[Measures].[Throughput]} * [selection]
)
您不能在交叉联接中多次使用同一层次结构。在这里，您已经使用了[ThroughputID].[ID]两次。而是尝试以下方法：
SET [NonEmptyIds] AS
NonEmpty(
[ThroughputID].[ID].[Id].ALLMEMBERS
,
{[Measures].[Throughput]} * [selection]
)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句