在OLAP多维数据集中，筛选属性时错误的总计总数 [英] In OLAP cube wrong Grand Total when attribute is filtered

问题描述

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

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

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

看起来不错，但是多维数据集中具有以下层次结构Company > Class > Group > Subgroup，并且如果用户尝试在过滤器中使用此层次结构-总计总计将失败(如果在此层次结构中未选中任何属性).样本:

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

在我们尝试过滤Date属性之前，我注意到了同样的问题，如果不是每月的每一天都被选择，那么它也会显示错误的总计.

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?

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

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.

我尝试删除所有MDX计算(范围)，但总计总计仍然不正确.

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

编辑

我注意到这样过滤时会发生问题:

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

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

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.

如果未过滤第3级，则表明合计总计.

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

编辑2

我试图在SSAS上进行跟踪，它返回的输出与Excel中的输出完全相同.在行上使用Salesperson维度时，它会生成以下MDX:

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

此MDX生成时没有Salesperson维度:

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

即使在行中未使用任何维度(在上面的示例中，我使用Salesperson维度)，我也注意到它显示了错误的总计.

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.

例如，它显示:

Sales Amount 
350

并且在行上使用Salesperson维度时:

And when using Salesperson dimension on the rows:

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

推荐答案

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

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.

此处是指向Microsoft KB的链接，用于确认该问题.影响版本2003-2019 !!!

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

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

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