[Mondrian] Presentation and doubt
ddimunzio at gmail.com
Mon Apr 20 10:06:23 EDT 2009
On Fri, Apr 17, 2009 at 4:39 PM, Julian Hyde <jhyde at pentaho.com> wrote:
> Diego Dimunzio wrote:
> Yes, you are right that post was based in your blog, It was the first link
> I found today when was looking for an post about it.
> Btw. I have some questions.
> 1- Does someone have tried to use a member reader?
> According with the documentation you have to implement “
> but this interface use “mondrian.rolap.MemberCache” which is not a public
> interface then you can’t implement the method setCache (MemberCache cache)
> We discussed custom MemberReaders on the list on 2009/4/7. I couldn't find
> the message in the archive, so I have attached the message.
> 2- I am trying to do something that I not sure if it’s possible
> I have a dimension which comes from the fact table and I added selection
> criteria to filter out the result.
> There are some issues with table filters. One is
> http://jira.pentaho.com/browse/MONDRIAN-495, but you are probably seeing
> another. (One change 5 years ago,
> http://p4webhost.eigenbase.org:8080/@md=d&c=6PU@2752?ac=10, added 3
> features and didn't add tests or documentation for any of them. Since this
> feature is not tested I am not confident that it works.)
> <Dimension name=*"Dname"*>
> <Hierarchy hasAll=*"true"* allMemberName=*"All Dname"*>
> <Table name=*"Dtable"*>
> <SQL dialect=*"mysql"*>* Dtable* .classification_name = 'Primary'</
> <Level name=*"Primary D"* column=*"dname"*/>
> It works properly, Then I would like to have a measure like this
> <Measure name=*"Actual"* aggregator=*"sum"* >
> <SQL dialect=*"mysql"*>case when *Dtable*.type=0
> Then *Dtable*.amount/CustomSqlfunction(*Dtable.currencycode*)
> else 0 end
> The measure works for all dimensions members due Mondrian add the dimension
> filter in the query it makes, but in the total (the first hierarchy)
> mondrian only use the raw measure without that filter, so the grand total is
> Do you know if there a way to make it work?
> Sounds like another issue with filters. I suggest you turn on SQL tracing
> and find out which SQL statement against Dtable is being generated without
> the WHERE clause. Post the results to this list. From the tracing we should
> be able to figure out which mondrian code is generating the SQL statement
> without including the filter.
> Mondrian mailing list
> Mondrian at pentaho.org
Hello Thanks for your response.
After debug a little by adding a break point in the line 244 of
RolapUtil.java I realize the filter I added for the dimension
<Dimension name=*"Primary Sector"*>
<Hierarchy hasAll=*"true"* allMemberName=*"All Primary Sectors"*>
<SQL dialect=*"mysql"*>classification_name = 'Primary' and classification_id
= amp_sec_scheme_id and sec_act_id = cc_id
<Level name=*"Sector Scheme"* column=*"sec_scheme_name"*/>
<Level name=*"Primary Sector"* column=*"sectorname"*/>
Is never applied, I think it happens due I have a dynamic filter in the fact
table what I use to filter the cube info
<Table name=*"cached_v_donor_funding"* >
<SQL dialect=*"mysql"*><![CDATA[cached_v_donor_funding.amp_activity_id IN
When the user change the workspace I replace this text @donorquery, using
the schema proccessor plug-in, for a pre generated sql query, this filter
works properlly for all queries.
This is the sql query that Mondrian makes for the measure the first filter
is applied but it does's apply the filter that comes from the dimension.
select cached_v_donor_funding.`sec_scheme_name` as `c0`,
cached_v_donor_funding.`sectorname` as `c1`,
when cached_v_donor_funding.transaction_type = 0 and
cached_v_donor_funding.adjustment_type = 1 then
cached_v_donor_funding.transaction_amount / getExchangeWithFixed(
cached_v_donor_funding.fixed_exchange_rate) else 0
end) as `m0`
from `cached_v_donor_funding` as `cached_v_donor_funding`
where (cached_v_donor_funding.amp_activity_id IN (SELECT distinct
(amp_activity_id) FROM cached_amp_activity WHERE 1 = 1 AND amp_activity_id
IN (SELECT amp_activity_id FROM cached_amp_activity WHERE amp_team_id IS NOT
NULL AND amp_team_id IN (70) OR amp_activity_id IN (SELECT
ata.amp_activity_id FROM amp_team_activities ata WHERE ata.amp_team_id IN
(70))))) and `cached_v_donor_funding`.`sec_scheme_name` = 'Classification
group by `cached_v_donor_funding`.`sec_scheme_name`,
In red you can see the "Dinamic" filter, Actually I am not sure if it's a
bug or something that can't be done. what do you think?
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Mondrian