Openbravo Issue Tracking System - Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0028025ModulesAnalyticspublic2014-10-30 10:522021-11-04 14:11
Reportermtaal 
Assigned ToAugustoMauch 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionopen 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Regression date
Regression introduced by commit
Regression level
Review Assigned To
Support ticket
OBNetwork customer
Regression introduced in release
Summary0028025: Log warning: The level [organization].[organizationLevel] makes use of the 'parentColumn' attribute, but a parent member for key
Description- happens when using closure tables, which we use for our records which have an ad_tree hierarchical structure

Complete log warning:
The level [organization].[organizationLevel] makes use of the 'parentColumn' attribute, but a parent member for key B843C30461EA4501935CB1D125C9C25A 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.
Steps To ReproduceThis is caused by the fact that Mondrian joins the organization table with the c_orderline table (which is the fact table in this case) to get the organization members. The c_orderline table only has records for the leaf organizations and not for the parents. Therefore Mondrian only finds the leaf organizations and not the parent organizations.

Note the resulting data seems okay.

This is an example of the query used to read members of a hierarchy, see the join with c_orderline:

select
    "alias1_222"."em_obanaly_treeparentid" as "c0",
    "alias1_222"."ad_org_id" as "c1",
    "alias1_222"."em_obanaly_treeordinal" as "c2"
from
    "ad_org" as "alias1_222",
    (select * from c_orderline alias1_220 where alias1_220.ad_org_id in ('E443A31992CB4635AFCAEABE7183CE85','0','DC206C91AA6A4897B44DA897936E0EC3','7BABA5FF80494CAFA54DEBD22EC46F01','BAE22373FEBE4CCCA24517E23F0C8A48','19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A','2E60544D37534C0B89E765FE29BC0B43')) as "alias1_221"
where
    (alias1_222.ad_org_id in ('E443A31992CB4635AFCAEABE7183CE85','0','DC206C91AA6A4897B44DA897936E0EC3','7BABA5FF80494CAFA54DEBD22EC46F01','BAE22373FEBE4CCCA24517E23F0C8A48','19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A','2E60544D37534C0B89E765FE29BC0B43'))
and
    "alias1_221"."ad_org_id" = "alias1_222"."ad_org_id"
group by
    "alias1_222"."em_obanaly_treeparentid",
    "alias1_222"."ad_org_id",
    "alias1_222"."em_obanaly_treeordinal"
order by
    "alias1_222"."em_obanaly_treeparentid" ASC NULLS FIRST,
    "alias1_222"."em_obanaly_treeordinal" ASC NULLS LAST]
Proposed SolutionPossibly change the internals of the Mondrian by implementing/overriding the MemberReader.

Related Mondrian issues:
http://jira.pentaho.com/browse/MONDRIAN-1511 [^]
http://jira.pentaho.com/browse/MONDRIAN-1328 [^]

Related posts on the internet:
http://stackoverflow.com/questions/20412942/mondrian-parent-child-hierarchy-not-working [^]
http://lists.pentaho.org/pipermail/mondrian/2012-December/004233.html [^]

Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2014-10-30 10:52mtaalNew Issue
2014-10-30 10:52mtaalAssigned To => AugustoMauch
2014-10-30 11:07ngarciaIssue Monitored: ngarcia
2014-11-01 15:08hgbotCheckin
2014-11-01 15:08hgbotNote Added: 0071322
2014-11-01 15:11mtaalNote Added: 0071327
2021-11-04 14:11mtaalNote Added: 0132850
2021-11-04 14:11mtaalStatusnew => closed

Notes
(0071322)
hgbot   
2014-11-01 15:08   
Repository: erp/pmods/org.openbravo.client.analytics
Changeset: 2033db29550359912f16335a97ea1f4d00cda6a7
Author: Martin Taal <martin.taal <at> openbravo.com>
Date: Sat Nov 01 15:08:06 2014 +0100
URL: http://code.openbravo.com/erp/pmods/org.openbravo.client.analytics/rev/2033db29550359912f16335a97ea1f4d00cda6a7 [^]

Fixes issue 28026, fixes issue 27052, related to issue 28025, Mondrian was not querying correctly for parent child members
The issue is fixed by patching Mondrian. Mondrian was not reading members of a parent-child hierarchy (like organization) in the correct order.
Mondrian reads members ordered by parent id and ordinal column but assumes that parents are always read (present in the query) before a child.
When ordering on parent id this assumption is not enforced as we use UUID's which can be ordered in many ways.

This is a Mondrian bug which was solved by:
- patching Mondrian to only use the ordinal value.
- The ordinal logic was adapted to ensure that parents are read before children while AD_TREE sequence is still followed

The tree closure and ordinal computation has been adapted so that it is redone when the analytics module is updated. For this the analytics version
is stored in a preference.

---
M src-db/database/sourcedata/AD_REF_LIST.xml
M src/org/openbravo/client/analytics/OBOlapClosureTreeHandler.java
A README_MONDRIAN_JAR
A lib/runtime/mondrian-3.8.0.0-209-PATCHED.jar
A src-patched-mondrian/mondrian/rolap/SqlTupleReader.java
R lib/runtime/mondrian-3.8.0.0-209.jar
---
(0071327)
mtaal   
2014-11-01 15:11   
The pushed changes solve practically all of the cases that the log warning was shown. There are minor cases left which are no problem afaics. This happens when the data is defined on child level and only childs are shown as column header.

Will wait (several months) with closing the issue until this indeed seems to have been solved satisfactory.
(0132850)
mtaal   
2021-11-04 14:11   
out of date