[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