[Mondrian] Presentation and doubt

Julian Hyde jhyde at pentaho.com
Mon Apr 20 12:51:14 EDT 2009


So, I think the bug is that a filter supplied for a DIMENSION table is only
applied at the first level of the dimension. The filter on the fact table
works OK.
 
If this is correct, please log a jira case.
 
Julian


  _____  

From: Diego Dimunzio [mailto:ddimunzio at gmail.com] 
Sent: Monday, April 20, 2009 8:00 AM
To: jhyde at pentaho.com; Mondrian developer mailing list
Subject: Re: [Mondrian] Presentation and doubt




On Mon, Apr 20, 2009 at 11:06 AM, Diego Dimunzio <ddimunzio at gmail.com>
wrote:




On Fri, Apr 17, 2009 at 4:39 PM, Julian Hyde <jhyde at pentaho.com> wrote:


 


Diego Dimunzio  wrote: 


Julian,


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
"mondrian.rolap.MemberSource
<http://mondrian.pentaho.org/api/mondrian/rolap/MemberSource.html> " 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>
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>
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'</SQL>

</Table>

<Level name="Primary D" column="dname"/>

</Hierarchy>

</Dimension>

It works properly, Then I would like to have a measure like this

<Measure name="Actual" aggregator="sum" >

<MeasureExpression>

      <SQL dialect="mysql">case when Dtable.type=0  

Then Dtable.amount/CustomSqlfunction(Dtable.currencycode)

 else 0 end

      </SQL>

</MeasureExpression>

</Measure>

 

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
wrong.

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.

 

Julian


_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian





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">

<Table name="cached_v_donor_funding">

<SQL dialect="mysql">classification_name = 'Primary' and classification_id =
amp_sec_scheme_id and sec_act_id = cc_id

</SQL>

</Table>

<Level name="Sector Scheme" column="sec_scheme_name"/>

<Level name="Primary Sector" column="sectorname"/>

</Hierarchy>

</Dimension>

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
(@donorquery)]]> 

</SQL>

</Table>

 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`,
sum(case
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.currency_code,
cached_v_donor_funding.transaction_date,
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
Sectorielle DAC-CRS'
group by `cached_v_donor_funding`.`sec_scheme_name`,
 `cached_v_donor_funding`.`sectorname`


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?




---------------------------



In fact the filter only is applied in first level of the dimension.

select `cached_v_donor_funding`.`sec_scheme_name` as `c0`
from `cached_v_donor_funding` as `cached_v_donor_funding`
where (classification_name = 'Primary' and
      classification_id = amp_sec_scheme_id and
      sec_act_id = cc_id)
group by `cached_v_donor_funding`.`sec_scheme_name`
order by ISNULL(`cached_v_donor_funding`.`sec_scheme_name`),
         `cached_v_donor_funding`.`sec_scheme_name` ASC

Then it's missing in the second level and in the measure.


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


More information about the Mondrian mailing list