[Mondrian] Discussion about drill through

[Andrew Petrov]

Hi all,

This discussion inspired by https://github.com/pentaho/mondrian/pull/74

All samples are from Foodmart.

First issue: missing name columns

When i use a request without return

MDX:

DRILLTHROUGH MAXROWS 200 SELECT ([Measures].[Count], [Position].[Middle
Management].[HQ Information Systems]) ON COLUMNS
FROM [HR]

SQL:

select "time_by_day"."the_year" as "Year", "time_by_day"."quarter" as
"Quarter", "time_by_day"."the_month" as "Month",
"time_by_day"."month_of_year" as "Month (Key)", "store"."store_country" as
"Store Country", "store"."store_state" as "Store State",
"store"."store_city" as "Store City", "store"."store_name" as "Store Name",
"position"."pay_type" as "Pay Type", "store"."store_type" as "Store Type",
"employee"."management_role" as "Management Role",
"employee"."position_title" as "Position Title",
"department"."department_id" as "Department Description",
"employee"."full_name" as "Employee Id", "employee"."employee_id" as
"Employee Id (Key)", "salary"."employee_id" as "Count"
from "time_by_day" as "time_by_day", "salary" as "salary", "store" as
"store", "employee" as "employee", "position" as "position", "department"
as "department"
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
"employee"."position_id" = "position"."position_id" and
"employee"."management_role" = 'Middle Management' and
"employee"."position_title" = 'HQ Information Systems' and
"salary"."department_id" = "department"."department_id"
order by "time_by_day"."the_year" ASC, "time_by_day"."quarter" ASC,
"time_by_day"."the_month" ASC, "time_by_day"."month_of_year" ASC,
"store"."store_country" ASC, "store"."store_state" ASC,
"store"."store_city" ASC, "store"."store_name" ASC, "position"."pay_type"
ASC, "store"."store_type" ASC, "employee"."management_role" ASC,
"employee"."position_title" ASC, "department"."department_id" ASC,
"employee"."full_name" ASC, "employee"."employee_id" ASC

When i use a request with return

MDX:

DRILLTHROUGH MAXROWS 200 SELECT ([Measures].[Count], [Position].[Middle
Management].[HQ Information Systems]) ON COLUMNS
FROM [HR]
RETURN [Department].[Department Description], [Employees].[Employee Id],
[Pay Type].[Pay Type], [Position].[Management Role], [Position].[Position
Title], [Store].[Store Country], [Store].[Store State], [Store].[Store
City], [Store].[Store Name], [Store Type].[Store Type], [Time].[Year],
[Time].[Quarter], [Time].[Month], [Measures].[Count]


SQL:

select "time_by_day"."the_year" as "Year", "time_by_day"."quarter" as
"Quarter", "time_by_day"."month_of_year" as "Month (Key)",
"store"."store_country" as "Store Country", "store"."store_state" as "Store
State", "store"."store_city" as "Store City", "store"."store_name" as
"Store Name", "position"."pay_type" as "Pay Type", "store"."store_type" as
"Store Type", "employee"."management_role" as "Management Role",
"employee"."position_title" as "Position Title",
"department"."department_id" as "Department Description",
"employee"."employee_id" as "Employee Id (Key)", "salary"."employee_id" as
"Count"
from "time_by_day" as "time_by_day", "salary" as "salary", "store" as
"store", "employee" as "employee", "position" as "position", "department"
as "department"
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
"employee"."position_id" = "position"."position_id" and
"employee"."management_role" = 'Middle Management' and
"employee"."position_title" = 'HQ Information Systems' and
"salary"."department_id" = "department"."department_id"
order by "time_by_day"."the_year" ASC, "time_by_day"."quarter" ASC,
"time_by_day"."month_of_year" ASC, "store"."store_country" ASC,
"store"."store_state" ASC, "store"."store_city" ASC, "store"."store_name"
ASC, "position"."pay_type" ASC, "store"."store_type" ASC,
"employee"."management_role" ASC, "employee"."position_title" ASC,
"department"."department_id" ASC, "employee"."employee_id" ASC


Screenshots (Saiku powered):

<img src="
http://habrastorage.org/storage2/e3e/ff3/428/e3eff3428afc0b8b6c184a69e7e72ae2.png
"/>
<img src="
http://habrastorage.org/storage2/6c8/72a/e32/6c872ae32884532bdc12ad4de0e1e745.png
"/>

So you can see in second case we have missing name columns. Its because we
call request.addConstrainedColumn(nameColumn, null), but forgot to call
((DrillThroughCellRequest)request)addDrillThroughColumn(nameColumn) just
like few lines above. I make this change in commit:
https://github.com/darkorion/mondrian/commit/19e2234f56c565fdd3f55288098a3b3faa46414f


Second issue: redundant columns in select

Current behavior:

MDX:

DRILLTHROUGH MAXROWS 200 SELECT ([Measures].[Count], [Position].[Middle
Management].[HQ Information Systems]) ON COLUMNS
FROM [HR]
RETURN [Position].[Position Title], [Measures].[Count]

SQL:

select "employee"."position_title" as "Position Title",
"salary"."employee_id" as "Count"
from "time_by_day" as "time_by_day", "salary" as "salary", "store" as
"store", "employee" as "employee", "position" as "position", "department"
as "department"
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
"employee"."position_id" = "position"."position_id" and
"employee"."management_role" = 'Middle Management' and
"employee"."position_title" = 'HQ Information Systems' and
"salary"."department_id" = "department"."department_id" order by
"employee"."position_title" ASC

As you can see we have department table in from clause, if you allow nulls
in your tables you can have working aggregation, but broken drillthrough
(because all tables in from use "inner join"). For example same request
without drillthrough:

MDX:

SELECT ([Measures].[Count], [Position].[Middle Management].[HQ Information
Systems]) ON COLUMNS
FROM [HR]

SQL

select "time_by_day"."the_year" as "c0", "employee"."management_role" as
"c1", "employee"."position_title" as "c2", count("salary"."employee_id") as
"m0"
from "time_by_day" as "time_by_day", "salary" as "salary", "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"."management_role" = 'Middle
Management' and "employee"."position_title" = 'HQ Information Systems'
group by "time_by_day"."the_year", "employee"."management_role",
"employee"."position_title"

As you can see for ex. "department" table not really needed.

Desired behavior: only listed in "return" clause dimension effect on result
select

MDX:

DRILLTHROUGH MAXROWS 200 SELECT ([Measures].[Count], [Position].[Middle
Management].[HQ Information Systems]) ON COLUMNS
FROM [HR]
RETURN [Position].[Position Title], [Measures].[Count]

SQL:

select "employee"."position_title" as "Position Title",
"salary"."employee_id" as "Count"
from "time_by_day" as "time_by_day", "salary" as "salary", "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"."management_role" = 'Middle
Management' and "employee"."position_title" = 'HQ Information Systems'
order by "employee"."position_title" ASC

We can do this by commenting single line like in this commit:
https://github.com/darkorion/mondrian/commit/14344d2fc1e38c25e1ff57881a1438836d592461

Regards,
Andrew.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20130517/781946ba/attachment.html