[Mondrian] Presentation and doubt

[Diego Dimunzio]

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, 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'</
> 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?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20090420/933efb32/attachment.html