[Mondrian] PC hierarchy and visibility of MemberReader and MemberSource
damien hostin
damien.hostin at axege.com
Thu Nov 29 06:33:12 EST 2012
Thanks for answering
As MemberReader and MemberSource were mentionned here
http://mondrian.pentaho.com/documentation/schema.php#Member_reader I
thought it was a kind of plugin, but I understand it is an internal plugin.
My main problem is to sort members so that parents are always read
before children, to avoid such warn message (below) and to see in JPivot
the dimension correctly instead of a list of all members :
2012-11-29 11:59:19,597 http-8080-Processor23 [WARN ] -
mondrian.rolap.SqlTupleReader$Target - The level
[Structure].[Structure] makes use of the 'parentColumn' attribute, but a
parent member for key 920.000.00 is missing. This can be due to the
usage of the NativizeSet MDX function with a list of members form a
parent-child hierarchy that doesn't include all parent members in its
definition. Using NativizeSet with a parent-child hierarchy requires the
parent members to be included in the set, or the hierarchy cannot be
properly built natively.
We are using parent-child hierarchies driven by left and right
boundaries. So I can get acheive that goal with sorting the hierarchy on
the left boundary column, all parents will be read before their
children, this should work.
The sql generated for reading the hierarchy look like:
select "adedst"."rfoade_s_rfodstide" as "c0",
"adedst"."rfoade_i_rfodstide" as "c1", "adedst"."rfoadegch" as "c2" from
"adedst" as "adedst" where (rfoade___rforefide = 'REF' and
rfoade___rfovdeide='VDE' and
rfoadervs=1)
group by "adedst"."rfoade_s_rfodstide", "adedst"."rfoade_i_rfodstide",
"adedst"."rfoadegch"
order by "adedst"."rfoade_s_rfodstide" ASC NULLS FIRST,
"adedst"."rfoadegch" ASC NULLS LAST
So it handle well the SQL filter (table adedst contains multiple
hierarchies, so I need to filter), but adds the order by rfoadegch after
sorting the parent column (which I agree is ok with the documentation)
Actually I just need to remove the first order by column... The reason
why I need to modify some code.
But I understand that the foodmart PC hierarchy works because of a
trick, the fact that the parentColumn is a integer and the elements well
sorted and well identified. So I was wondering if I was missing
something, because it is not always possible to rely on data.
Thx,
Damien
Le 28/11/2012 19:54, Julian Hyde a écrit :
> MemberReader and MemberSource are internals. Modify them at your own risk.
>
> A parent-child hierarchy based on a table with a <SQL> sub-element looks dubious. The <SQL> element hasn't been tested exhaustively. I wouldn't be surprised if Mondrian ignores it in parent-child hierarchies. If it works for you, fine.
>
> Your issue seems to be how members of parent-child hierarchies are ordered within their parent. Have you tried using the ordinalColumn attribute of Level?
>
> FYI, I changed some stuff in Mondrian 4 (lagunitas branch) about ordering of parent-child hierarchies. I think I decided that the default ordering should be by name, not by id. But you can override by setting the Attribute's orderByColumn or providing a nested OrderBy element. (Note that these XML elements only exist in Mondrian 4.)
>
> Julian
>
>
> On Nov 27, 2012, at 6:43 AM, damien hostin <damien.hostin at axege.com> wrote:
>
>> Hello,
>>
>>
>> XML for the dimension looks like:
>>
>> <Dimension name="Structure">
>> <Hierarchy hasAll="false" name="Structure" caption="Structure"
>> primaryKey="rfoade_i_rfodstide" primaryKeyTable="adedst"
>> defaultMember="STRC" nullParentValue="AXS_ROOT">
>> <Table name="adedst">
>> <SQL dialect="generic">rfoade___rforefide = 'REF' and
>> rfoade___rfovdeide='VDE' and
>> rfoadervs=1 </SQL>
>> </Table>
>> <Level name="STRC" table="adedst" column="rfoade_i_rfodstide"
>> parentColumn="rfoade_s_rfodstide" uniqueMembers="true">
>> </Level>
>> </Hierarchy>
>> </Dimension>
>>
>>
>> And the MDX :
>>
>> select NON EMPTY [DefaultMeasures] ON COLUMNS, NON EMPTY
>> [DefaultDimensions] ON ROWS
>> from [cana_cube_1354026362876]
>> where [DefaultFilters]
>>
>> And the namedset in the schema for the MDX :
>>
>> <NamedSet name="DefaultMeasures">
>> <Formula>{[Measures].[Montant]}</Formula>
>> </NamedSet>
>>
>> <NamedSet name="DefaultDimensions">
>> <Formula>Crossjoin({[Temps.Temps par mois].[2009]),
>> ([Temps.Temps par mois].[2010]}, {([Structure].DefaultMember,
>> [Natures].DefaultMember)})</Formula>
>> </NamedSet>
>>
>> <NamedSet name="DefaultFilters">
>> <Formula>{([Stade].DefaultMember,
>> [Processus].DefaultMember, [rfovsn].DefaultMember)}</Formula>
>> </NamedSet>
>>
>>
>> Closure table are not mandatory, but I think we can't skip them if we
>> want good performances with parent-child hierarchies. I though it would
>> be easier to debug without.
>>
>> I can give more information, but I'm not sure what could help.
>>
>>
>> Thanks,
>>
>> Damien
>>
>> Le 27/11/2012 14:54, Kurtis Walker a écrit :
>>> Damien,
>>> Can you send an example of the MDX you are trying to run and the XML for the dimension in your schema?
>>>
>>> Kurt
>>>
>>> On Tuesday, November 27, 2012 05:15:12 AM damien hostin wrote:
>>>> Hello,
>>>>
>>>> I am trying to use parent-child hierarchy, but it has some restriction.
>>>>
>>>> It looks like we need to give a list sorted in such a way that parent
>>>> always come before their child, even if we are not using Closure Tables.
>>>> But in the same time, an order by clause is generated on the parent and
>>>> the child column.
>>>>
>>>> In foodmart.xml it is OK because parent and child are integer, and
>>>> sorted correctly. It is also OK because the hierarchy is always the
>>>> same, there is no real-life end user that modify it.
>>>>
>>>> In my case, sorting the hierarchy on parent and child doesn't garanted
>>>> to give mondrian the correct order. This can't work but it is possible
>>>> to change the code. I could have patch mondrian to handle PC hierarchies
>>>> with my "custom" order by clause, but regression tests find 35 failures
>>>> and 1 error with original code running with derby.
>>>>
>>>> As it is possible the give MemberReader or MemberSource own
>>>> implementation I decided to use that cleaner way. But
>>>> mondrian.rolap.MemberSource uses a MemberCache class that is not visible
>>>> outside mondrian's package, and mondrian.rolap.MemberReader is package
>>>> visible too. So I must implements my custom MemberReader/Source inside
>>>> mondrian project, with my 36 regression.
>>>>
>>>> Is it a volunteer restriction or can the visibility be change to allow
>>>> outside implementation ? I'm a bit messy with those problems.
>>>>
>>>>
>>>> Thanks,
>>>>
>>>> Damien Hostin
>>>> _______________________________________________
>>>> Mondrian mailing list
>>>> Mondrian at pentaho.org
>>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
More information about the Mondrian
mailing list