过滤属性时在 OLAP 多维数据集中错误总计

In OLAP cube wrong Grand Total when attribute is filtered(过滤属性时在 OLAP 多维数据集中错误总计)

问题描述

试图检查每个销售人员的销售额的用户.样本数据:

销售员销售额001 1000002 500003 750总计:2250

看起来不错，但是我们有以下层次结构 Company >类>组>多维数据集中的子组，并且如果用户尝试在过滤器中使用此层次结构 - 总计失败(如果未选中此层次结构中的任何属性).示例:

销售员销售额001 1000002 500003 750总计:350

我之前在尝试过滤日期属性时注意到了同样的问题，如果没有选择该月的每一天，它也会显示错误的总计.

您知道它发生的原因以及如何解决它吗?

销售额是物理量度(不是计算量度)，它是从 SQL 视图中选择的(每个事实都是如此).

我问过同样的问题

编辑

我注意到这样过滤时会出现问题:

从层次结构的第一级选择一个元素，从第二级选择一个元素，从第三级层次结构选择一个元素，如上图所示.

如果未过滤第 3 级，则显示良好的总计.

编辑 2

我尝试在 SSAS 上进行跟踪，它返回的输出与 Excel 中完全相同.在行上使用销售人员维度时，它生成了以下 MDX:

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,非空{([销售员].[销售员].[销售员].ALLMEMBERS)}维度属性 MEMBER_CAPTION，MEMBER_UNIQUE_NAME 来自 (SELECT ( { [Item].[Class - Group - Subgroup].[Class].&[XXX]&[1.],[项目].[类 - 组 - 子组].[组].&[XXX]&[2.]&[2.2.],[项目].[类 - 组 - 子组].[子组].&[XXX]&[2.]&[2.3.]&[2.3.1.] }) 从 (SELECT ({ [Company].[Company].&[XXX] } ) ON COLUMNS来自 [销售]))WHERE([Company].[Company].&[XXX]，[Item].[Class - Group - Subgroup].CurrentMember)单元格属性值，BACK_COLOR，FORE_COLOR，FORMATTED_VALUE，FORMAT_STRING，FONT_NAME，FONT_SIZE，FONT_FLAGS

此 MDX 生成时没有 Salesperson 维度:

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNSFROM ( SELECT ( { [Item].[Class - Group - Subgroup].[Class].&[XXX]&[1.],[项目].[类 - 组 - 子组].[组].&[XXX]&[2.]&[2.2.],[Item].[Class - Group - Subgroup].[Subgroup].&[XXX]&[2.]&[2.3.]&[2.3.1.] } ) 列FROM ( SELECT ( { [Company].[Company].&[XXX] } ) 在列FROM [Sales])) WHERE ([Company].[Company].&[XXX], [Item].[Class - Group - Subgroup].CurrentMember) 单元格属性值，BACK_COLOR，FORE_COLOR，FORMATTED_VALUE，FORMAT_STRING，FONT_NAME，FONT_SIZE、FONT_FLAGS

我注意到，即使我没有在行上使用任何维度(在上面的示例中我使用了销售人员维度)，它也会显示错误的总计.

例如它显示:

销售额350

当在行上使用销售人员维度时:

销售员销售额001 1000002 500003 750总计:350

我想换个角度来看这个问题，这里的问题不是 SQL/SSAS，而是 Excel.在带有小计和总计的数据透视表中，总计不是由多维数据集计算的，而是由客户端应用程序计算的.我经历过几次，发现这是 Excel 的一个已知问题.该解决方案通常涉及在 Excel 中创建一个新的计算字段以提供总计.如果 Excel 是访问多维数据集的其他用户的首选客户端应用程序，这尤其令人沮丧.如果有什么安慰的话，我在 Tableau 等其他工具中也经历过一两次，但原因略有不同，采用不同的解决方案.

这里是一个承认问题的 Microsoft KB 的链接.影响2003-2019版本！！！

https://support.microsoft.com/en-us/help/211470/calculated-field-returns-incorrect-grand-total-in-excel

A user trying to check the Sales Amount per Salesperson. Sample data:

Salesperson   Sales Amount    
001                   1000    
002                    500    
003                    750
Grand Total:          2250

It looks fine, but we have the following hierarchy Company > Class > Group > Subgroup in the cube and if a user tries to use this hierarchy in filters - Grand Total fails (if any attribute is unchecked in this hierarchy). Sample:

Salesperson   Sales Amount    
001                   1000    
002                    500    
003                    750    
Grand Total:           350

I've noticed the same problem before when we tried to filter Date attribute, if not every day of the month was selected it shown wrong Grand Total too.

Have you an idea why it happens and how to fix it?

Sales Amount is physical measure (not calculated measure), it is selected from SQL view (the same happens with every fact).

I've asked the same question here, but nobody could answer it.

I've tried to delete all MDX calculations (scopes), but still Grand Total was incorrect.

EDIT

I've noticed that the problem occurs when filtering like that:

1 element selected from the first level of the hierarchy, 1 element from 2nd level and 1 element from the 3rd level of hierarchy as in the image above.

If the 3rd level isn't filtered it shows good Grand Total.

EDIT 2

I've tried to trace on SSAS, it returns exactly the same output as in Excel. It generated the following MDX when using Salesperson dimension on the rows:

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, 
NON EMPTY { ([Salesperson].[Salesperson].[Salesperson].ALLMEMBERS ) } 
DIMENSION PROPERTIES MEMBER_CAPTION, 
MEMBER_UNIQUE_NAME ON ROWS FROM ( 
SELECT ( {  [Item].[Class - Group - Subgroup].[Class].&[XXX]&[1.], 
            [Item].[Class - Group - Subgroup].[Group].&[XXX]&[2.]&[2.2.], 
            [Item].[Class - Group - Subgroup].[Subgroup].&[XXX]&[2.]&[2.3.]&[2.3.1.] } 
) ON COLUMNS FROM ( SELECT ( { [Company].[Company].&[XXX] } ) ON COLUMNS 
FROM [Sales])) 
WHERE ( [Company].[Company].&[XXX], [Item].[Class - Group - Subgroup].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

This MDX generated without Salesperson dimension:

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS 
FROM ( SELECT ( { [Item].[Class - Group - Subgroup].[Class].&[XXX]&[1.], 
[Item].[Class - Group - Subgroup].[Group].&[XXX]&[2.]&[2.2.], 
[Item].[Class - Group - Subgroup].[Subgroup].&[XXX]&[2.]&[2.3.]&[2.3.1.] } ) ON COLUMNS 
FROM ( SELECT ( { [Company].[Company].&[XXX] } ) ON COLUMNS 
FROM [Sales])) WHERE ( [Company].[Company].&[XXX], [Item].[Class - Group - Subgroup].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I've noticed even if I'm not using any dimension on the rows (in samples above I've used Salesperson dimension) it shows wrong Grand Total.

For example it shows:

Sales Amount 
350

And when using Salesperson dimension on the rows:

Salesperson   Sales Amount    
001                   1000    
002                    500    
003                    750    
Grand Total:           350

I would like to take another angle to this one and suggest that it is not SQL/SSAS that is the problem here but Excel. In a pivot table with subtotals and grand totals the totals are not calculated by the cube but the client-side application. I have experienced this a few times and found this to be a known issue with Excel. The solution typically involves creating a new calculated field in Excel to provide the grand total. This is frustrating especially if Excel is the go-to client application for other users accessing the cube. If it is any consolation, I've also experienced this a time or two in other tools such as Tableau but for slightly different reasons with different solutions.

Here is a link to a Microsoft KB acknowledging the problem. Affects versions 2003-2019!!!

https://support.microsoft.com/en-us/help/211470/calculated-field-returns-incorrect-grand-total-in-excel

这篇关于过滤属性时在 OLAP 多维数据集中错误总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！