[Mondrian] What happenes during a query using mondrian?

Matt Campbell mcampbell at pentaho.com
Fri Feb 26 08:40:27 EST 2016


Hi Longxing,

Mondrian will evaluate many functions in-process, so it’s possible that the same SQL will execute for similar MDX (assuming the cells aren’t in cache).  Mondrian will retrieve the cell data, and then determine after the fact which tuples to project onto the axis.  That said, native topcount<http://mondrian.pentaho.com/documentation/api/mondrian/olap/MondrianProperties.html#EnableNativeTopCount> is enabled by default, and I would expect it to limit the tuples loaded from the database with a query like you have below.  IIRC correctly it doesn’t put a LIMIT in the SQL, though, it just cursors through the first N records and stops when it reaches the specified topcount.

If you’re really interested in the nitty gritty details, a good place to start is debugging through RolapResult.  There’s even a handy summary of the evaluation algorithm in that class:
https://github.com/pentaho/mondrian/blob/lagunitas/src/main/java/mondrian/rolap/RolapResult.java#L109

-matt

From: <mondrian-bounces at pentaho.org<mailto:mondrian-bounces at pentaho.org>> on behalf of 魏龙星 <wlxwolves at gmail.com<mailto:wlxwolves at gmail.com>>
Reply-To: Mondrian list <mondrian at pentaho.org<mailto:mondrian at pentaho.org>>
Date: Friday, February 26, 2016 at 3:03 AM
To: Mondrian list <mondrian at pentaho.org<mailto:mondrian at pentaho.org>>
Subject: [Mondrian] What happenes during a query using mondrian?



---------- Forwarded message ---------
From: 魏龙星 <wlxwolves at gmail.com<mailto:wlxwolves at gmail.com>>
Date: 2016年2月26日周五 下午4:01
Subject: What happenes during a query using mondrian?
To: <mondrian at pentaho.org<mailto:mondrian at pentaho.org>>


I know mondrian converts MDX queries into relational queries and return the result. But is there any details about this process?

I use the sample cube HR. Here is the MDX:
WITH
SET [~ROWS] AS
    TopCount({[Time].[Time].[Month].Members}, 3, [Measures].[Org Salary])
SELECT
NON EMPTY {[Measures].[Org Salary]} ON COLUMNS,
NON EMPTY [~ROWS] ON ROWS
FROM [HR]

And  this is the SQL generated by the MDX. I find it in log:
select
    "time_by_day"."the_year" as "c0",
    "time_by_day"."quarter" as "c1",
    "time_by_day"."the_month" as "c2",
    "time_by_day"."month_of_year" as "c3",
    sum("salary"."salary_paid") as "c4"
from
    "salary" as "salary",
    "time_by_day" as "time_by_day"
where
    "time_by_day"."the_year" = 1997
and
    "salary"."pay_date" = "time_by_day"."the_date"
group by
    "time_by_day"."the_year",
    "time_by_day"."quarter",
    "time_by_day"."the_month",
    "time_by_day"."month_of_year"
order by
    CASE WHEN sum("salary"."salary_paid") IS NULL THEN 1 ELSE 0 END, sum("salary"."salary_paid") DESC,
    CASE WHEN "time_by_day"."the_year" IS NULL THEN 1 ELSE 0 END, "time_by_day"."the_year" ASC,
    CASE WHEN "time_by_day"."quarter" IS NULL THEN 1 ELSE 0 END, "time_by_day"."quarter" ASC,
    CASE WHEN "time_by_day"."the_month" IS NULL THEN 1 ELSE 0 END, "time_by_day"."the_month" ASC,
    CASE WHEN "time_by_day"."month_of_year" IS NULL THEN 1 ELSE 0 END, "time_by_day"."month_of_year" ASC


I change top 3 to top 10 and I got the same SQL. And the SQL has nothing like "limit". But it just returned 3 or 10 items. It works, but how?

So I am wondering what happened during a query? I searched and didn't get any useful information. Can anybody help?

Thank you.

Longxing
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20160226/1cec5f19/attachment-0001.html 


More information about the Mondrian mailing list