[Mondrian] RE: VisualTotals + Distinct-count measure gives wrong results

Sergey Mazin sergey.mazin at skype.net
Mon Jan 25 06:31:35 EST 2010


As it turned out there are 3 problems with VisualTotals at the moment:

1.       RolapAggregationManager.makeCompoundGroup method doesn't check
VisualTotalMembers for constraints. I think I have a fix for that.

2.       DisplayInfo for VisualTotalMembers is sometimes 0. In Excel it
means that it doesn't allow to drilldown to next level (Excel thinks there
are no any children for that level). I think I will have a fix for that also
when issue #3 is fixed.

3.       VisualTotals don't check constraints of children of their children.

Let's say we have query:

 

WITH SET [XL_Row_Dim_0] AS 
'VisualTotals(Distinct(Hierarchize({ 
Ascendants([Store].[All Stores].[USA].[OR]), 
Descendants([Store].[All Stores].[USA].[OR]), 
Ascendants([Store].[All Stores].[USA].[CA]), 
Descendants([Store].[All Stores].[USA].[CA])})))' 
SELECT NON EMPTY Hierarchize(Intersect({DrilldownLevel({ 
[Store].[All Stores] 
})}, [XL_Row_Dim_0])) ON COLUMNS 
FROM [HR] WHERE ([Measures].[Number of Employees])

 

It should return:

Axis #0: 
{[Measures].[Number of Employees]} 
Axis #1: 
{[Store].[All Stores]} 
{[Store].[All Stores].[USA]} 
Row #0: 329 
Row #0: 329

 

But it returns:

Axis #0: 
{[Measures].[Number of Employees]} 
Axis #1: 
{[Store].[All Stores]} 
{[Store].[All Stores].[USA]} 
Row #0: 616
Row #0: 329

 

SQL queries look like:

 

For {[Store].[All Stores].[USA]}

7438 [http-8080-1] DEBUG mondrian.sql  - 21: Segment.load: executing sql
[select "time_by_day"."the_year" as "c0", count(distinct
"salary"."employee_id") as "m0" from "time_by_day" as "time_by_day",
"salary" as "salary", "store" as "store", "employee" as "employee" where
"salary"."pay_date" = "time_by_day"."the_date" and "time_by_day"."the_year"
= 1997 and "salary"."employee_id" = "employee"."employee_id" and
"employee"."store_id" = "store"."store_id" and "store"."store_state" in
('CA', 'OR') group by "time_by_day"."the_year"]

 

For {[Store].[All Stores]}

7453 [http-8080-1] DEBUG mondrian.sql  - 22: Segment.load: executing sql
[select "time_by_day"."the_year" as "c0", count(distinct
"salary"."employee_id") as "m0" from "time_by_day" as "time_by_day",
"salary" as "salary", "store" as "store", "employee" as "employee" where
"salary"."pay_date" = "time_by_day"."the_date" and "time_by_day"."the_year"
= 1997 and "salary"."employee_id" = "employee"."employee_id" and
"employee"."store_id" = "store"."store_id" and "store"."store_country" =
'USA' group by "time_by_day"."the_year"]

 

For {[Store].[All Stores]} level it doesn't add {[Store].[All Stores].[USA]}
constraints.

It is very important for as to get it fixed, we are using Excel 2007 as main
tool to access cubes and it sends queries in that format.

 

Regards,

Sergey Mazin

 

From: Julian Hyde [mailto:jhyde at pentaho.com] 
Sent: Friday, January 22, 2010 9:31 AM
To: 'Sergey Mazin'; 'Mondrian developer mailing list'
Subject: RE: VisualTotals + Distinct-count measure gives wrong results

 

I'd put a breakpoint in the expression inside the visual totals function. It
should be aggregate( < set of members >).

 

Distinct-count measures are very difficult to aggregate. You have to go back
to the underlying database. We aggregate distinct-count measures correctly
in other places (e.g. if you create a compound slicer) but it's tricky.

 


  _____  


From: Sergey Mazin [mailto:sergey.mazin at skype.net] 
Sent: Thursday, January 21, 2010 8:17 AM
To: 'Mondrian developer mailing list'
Cc: jhyde at pentaho.com
Subject: VisualTotals + Distinct-count measure gives wrong results

Hi Julian,

 

http://jira.pentaho.com/browse/MONDRIAN-682

 

[Measures].[Number of Employees] - is distinct count measure. 

Example: 

WITH SET [XL_Row_Dim_0] AS 
'VisualTotals(Distinct(Hierarchize({ 
Ascendants([Store].[All Stores].[USA].[OR]), 
Descendants([Store].[All Stores].[USA].[OR]), 
Ascendants([Store].[All Stores].[USA].[CA]), 
Descendants([Store].[All Stores].[USA].[CA])})))' 
SELECT NON EMPTY Hierarchize(Intersect({DrilldownLevel({ 
[Store].[All Stores] 
})}, [XL_Row_Dim_0])) ON COLUMNS 
FROM [HR] WHERE ([Measures].[Number of Employees]) 

Query should return: 

Axis #0: 
{[Measures].[Number of Employees]} 
Axis #1: 
{[Store].[All Stores]} 
{[Store].[All Stores].[USA]} 
Row #0: 329 
Row #0: 329 

But returns empty set. 

The same query with NOT distinct measure works.

 

In distinct query cells are not being evaluated properly and no queries is
sent to database.

Could you please advise what is the best place to start investigating.

 

Best regards,

Sergey

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100125/b2c637a6/attachment.html 


More information about the Mondrian mailing list