[Mondrian] Presentation and doubt

[Diego Dimunzio]

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, 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?
>


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

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/e51e1d7a/attachment.html