ssas - 如何抑制 MDX 查询中的单个单元格值？

Question

我有一个 MDX 查询返回 2 个不同的值、查找总数和查找失败次数(以及其他一些东西，但这是重要的部分)。

    with
    member [Pct Success] as 'iif(isempty([Num Lookup]) or [Num Lookup]=0, null, 
        100 * (coalesceempty([Num Lookup] - [Num Failed], 0) / [Num Lookup]))'
select
    {
        [Measures].[Pct Success],
        [Measures].[Num Lookup],
        [Measures].[Num Failed]
    } on 0,
[Calendar].[Date].children on 1
from
    [Cube]

现在我要做的是获得另一个成功测量值，但我希望这个测量值能够抑制任何低于特定阈值的 [Num Failed] 单元格。具体来说，如果我至少有 4 次成功查找(Num Lookup - Num Failed > 3 && Num Lookup > 4)，那么我想使该单元格的 [Num Failed] = 0(或者我想复制 [Num Failed] = 0 因为我仍然需要显示原始的 % Success 度量)。

基本事实看起来像这样(只是列的相关子集 - Line 列是为了便于引用，它不在实际事实中):

 Line | CalendarKey | Num Failed | Num Lookup 1    | 20130601    | 2          | 8 2    | 20130601    | 5          | 8 3    | 20130601    | 1          | 8 4    | 20130601    | 0          | 7 5    | 20130601    | 7          | 8 6    | 20130602    | 2          | 6 7    | 20130602    | 1          | 7 8    | 20130602    | 5          | 10 9    | 20130602    | 7          | 9 etc.

What I expect to see for results based on those facts above is:

         | % Success | % Filt Success | Num Filt Failed | Num Failed | Num Lookup20130601 | 61.53     | 69.23          | 12              | 15         | 3920130602 | 53.13     | 71.88          | 9               | 15         | 32

In the above fact sample, lines 1, 3, 4, 7 & 8 all would have a filtered failed value of 0, which gives us the sample output listed above.

My initial thought is to use another member in the with clause as the copy of [Num Failed] and then a Cell Calculation to do the suppression, but I can't get the Cell Calculation to work correctly - it either doesn't modify the value, or it errors out during evaluation.

Here's the non-working version that "should" return what I'm looking for, but doesn't:

with
    member [Measures].[Num Threshold Failure] AS [Num Failed]
    Cell Calculation [Data Filter] For '[Measures].[Num Threshold Failure]' AS 'NULL', Condition = 'CalculationPassValue((([Measures].[Num Lookup] - [Measures].[Num Failure]) < 4) AND ([Measures].[Num Lookup] > 4), 1)'
    member [Pct Success] as 'iif(isempty([Num Lookup]) or [Num Lookup]=0, null, 100 * (coalesceempty([Num Lookup] - [Num Failed], 0) / [Num Lookup]))'
    member [Pct Filtered Success] as 'iif(isempty([Num Lookup]) or [Num Lookup]=0, null, 100 * (coalesceempty([Num Lookup] - [Num Threshold Failure], 0) / [Num Lookup]))'
select
    {
        [Measures].[Pct Success],
        [Measures].[Pct Filtered Success],
        [Measures].[Num Threshold Failure],
        [Measures].[Num Failed],
        [Measures].[Num Lookup]
    } on 0,
    { [Calendar].[Date].children } on 1
from
    [Cube]

Answer 1

我不明白你的问题的每一个细节，但据我所知，以下应该回答，或者至少接近:

with
member [Pct Success] as iif([Measures].[Num Lookup]=0,
       null, 
       100 * (coalesceempty([Measures].[Num Lookup] -[Measures]. [Num Failed], 0) / [Measures].[Num Lookup]))
member [Filtered Failed] as iif([Measures].[Num Lookup] - [Measures].[Num Failed] > 3 and [Measures].[Num Lookup] > 4),
        0,
        [Measures].[Num Failed])
member [Bottom Filtered failed] as Sum(Leaves(),
       iif([Measures].[Num Lookup] - [Measures].[Num Failed] > 3 and [Measures].[Num Lookup] > 4),
        0,
        [Measures].[Num Failed]))
member [Pct Filtered Success] as iif([Measures].[Num Lookup]=0,
       null, 
       100 * (coalesceempty([Measures].[Num Lookup] -[Measures]. [Filtered Failed], 0) / [Measures].[Num Lookup]))
select
    {
        [Measures].[Pct Success],
        [Measures].[Num Lookup],
        [Measures].[Num Failed],
        [Measures].[Num Filtered Failed],
        [Measures].[Bottom Filtered Failed],
        [Measures].[Pct Filtered Success]
    } on 0,
    [Calendar].[Date].children on 1
from [Cube]

顺便说一句:除非您的目标是 MDX 的 SQL Server 2000 方言，否则您不需要在 WITH 子句中用单引号包含成员定义。并且，根据这个 blog post of the former lead developer of the MDX processor ，您可以将空和空的检查简化为只检查零。

编辑:

正如您所说，用户希望在假设分析中使用多个不同的公差，如果您的立方体不是很大，并且不同公差的数量只是少数，您可以做什么，您可以预先计算 what- if cases，从而利用 Analysis Services 的快速响应时间来获取聚合值。

要做到这一点，您将按照以下步骤进行:建立一个小维度表，比如 dim_tolerance，包含 e。 G。数字 0 到 10，或数字 0、1、2、3、5、8、10 和 12，或任何有意义的数字。然后构建一个新的事实表，引用与当前维度相同的维度，加上新的维度，并用根据 dim_tolerance 值指示计算的单个度量 [num failed filtered] 填充它。然后，您可以从主要事实表中删除 [num failed] 度量(因为它与公差为 0 的 [num failed filtered] 相同)。使新维度中的属性不可聚合，默认值为 0。