[Mondrian] Presentation and doubt

Diego Dimunzio ddimunzio at gmail.com
Tue Apr 21 10:45:46 EDT 2009


I think there is already and issue related to this one
http://jira.pentaho.com/browse/MONDRIAN-495
Thanks.

On Mon, Apr 20, 2009 at 1:51 PM, Julian Hyde <jhyde at pentaho.com> wrote:

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


-- 
Diego Andres Dimunzio
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20090421/5a4d727c/attachment.html 


More information about the Mondrian mailing list