[Mondrian] Adding Grouping Set support for Distinct Count measures

Ajit Vasudeo Joglekar ajogleka at thoughtworks.com
Mon Jan 28 15:16:00 EST 2008


Julian,

I wanted to verify few things before answering your second question

> Would this work with distinct-count measures applied to aggregate 
members?
> (For example unit sales over [CA plus OR].)

I hope I understand your question correctly. Since Unit Sales is a non 
distinct measure I would try to answer both scenarios

1) Aggregation of a distinct count measure for select members

Example mdx:

MEMBER [Store].[COG_OQP_USR_Aggregate(Store)] AS
'AGGREGATE({[Store].[All Stores].[USA].[CA], [Store].[All 
Stores].[USA].[OR]})', SOLVE_ORDER = 8

SELECT {[Measures].[Customer Count]} ON AXIS(0),
{[Store].[All Stores].[USA].[CA], [Store].[All Stores].[USA].[OR], 
[Store].[COG_OQP_USR_Aggregate(Store)]}
ON AXIS(1)
FROM [Sales]

A grouping set sql loads all data

SELECT "store"."store_state"                            AS "c0",
        "time_by_day"."the_year"                        AS "c1",
        COUNT(DISTINCT "sales_fact_1997"."customer_id") AS "m0",
        grouping("store"."store_state")                 AS "g0"
FROM "store" "store"                       ,
        "sales_fact_1997" "sales_fact_1997",
        "time_by_day" "time_by_day"
WHERE "sales_fact_1997"."store_id"  = "store"."store_id"
    AND "store"."store_state"      IN ('CA', 'OR')
    AND "sales_fact_1997"."time_id" = "time_by_day"."time_id"
    AND "time_by_day"."the_year"    = 1997
GROUP BY grouping sets 
(("store"."store_state","time_by_day"."the_year"),("time_by_day"."the_year"))

2) Aggregation of a normal (non distinct count) measure for select members

It is possible to get this working since it is very similar to case 1). 
There is a issue here though. Lets say for whatever reason user wants to 
aggregate

Aggregate([Store].[All Stores].[USA].[CA], [Store].[All 
Stores].[USA].[CA], [Store].[All Stores].[USA].[OR]) over [Measures].[Unit 
Sales]. The expected value here is (2 * CA + OR) for a non distinct count 
measure. The sql generated like above will not result in correct 
aggregation value

Hope this answers your question

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


More information about the Mondrian mailing list