Modules:Analytics ParentChild

Contents 1 Mapping time/date columns in Mondrian 1.1 Time Member Table (preferred) 1.2 Translate Date(Time) column to a Member with hierarchies using DB Functions 2 Parent - Child Relations and mapping to Mondrian 2.1 Updating parent-child information 2.2 Parent-Child Relations: use explicit Parent/Ordinal Columns 2.3 Ordering in Parent-Child: the Ordinal Column

Mapping time/date columns in Mondrian

Time/date columns deserve a special mapping in analytics. This because often you want to analyze information for a year, a quarter, a month or a set of these dimensional members.

Mondrian provides a specific way of mapping time dimensions, this to support year, quarter, month levels. See the Mondrian documentation for more information.

Openbravo supports two distinct ways of mapping time/date columns to a time dimension:

Time Member Table (preferred)

This approach uses the time member table, with a specific column, when generating the Mondrian mapping, for each date/time column, Openbravo will check if there is a special column referring to the time dimension table. If there is such a column then Openbravo will use this column and the referred time dimension to create a special Mondrian time dimension.

Results in the following Mondrian mapping:

 
<Dimension name="salesOrder-orderDate" type="TimeDimension" caption="Sales Order - Order Date" foreignKey="c_order_id">
  <Hierarchy hasAll="true" primaryKey="c_order_id" primaryKeyTable="c_order">
    <Join leftKey="em_obanaly_dateordered" rightKey="obanaly_time_dimension_id" rightAlias="alias1_24">
      <Table name="c_order" alias="alias1_23">
        <SQL>alias1_23.ad_org_id in ('E443A31992CB4635AFCAEABE7183CE85','0','DC206C91AA6A4897B44DA897936E0EC3','7BABA5FF80494CAFA54DEBD22EC46F01',
'BAE22373FEBE4CCCA24517E23F0C8A48','19404EAD144C49A0AF37D54377CF452D','B843C30461EA4501935CB1D125C9C25A','2E60544D37534C0B89E765FE29BC0B43')</SQL>
      </Table>
      <Table name="obanaly_time_dimension" alias="alias1_24"/>
    </Join>
    <Level name="Year" table="alias1_24" column="theyear" uniqueMembers="true" levelType="TimeYears" type="Numeric"/>
    <Level name="Quarter" table="alias1_24" column="quarter" uniqueMembers="false" levelType="TimeQuarters" type="Numeric"/>
    <Level name="Month" table="alias1_24" column="month_of_year" uniqueMembers="false" levelType="TimeMonths" type="Numeric"/>
    <Level name="Day" table="alias1_24" column="day_of_month" ordinalColumn="yyyymmdd" uniqueMembers="false" levelType="TimeDays" type="Numeric"/>
  </Hierarchy>
</Dimension>

Openbravo will automatically update the time dimension foreign key columns using a separate process. See the configuration section in this wiki page for more information.

It is better to use the TimeDimension reference than the next provided solution (DB functions). The time dimension table:

• has entries for all dates, so no 'holes' in time members when reporting on them
• is more efficient as no db functions are needed

Translate Date(Time) column to a Member with hierarchies using DB Functions

This approach uses database functions to translate a date/time column to year, quarter and month, this is done automatically by Openbravo, see here for an example of a generated mapping:

 
<Dimension name="salesOrder-accountingDate" caption="Sales Order - Accounting Date" foreignKey="c_order_id">
  <Hierarchy hasAll="true" primaryKey="c_order_id" primaryKeyTable="c_order">
    <Table name="c_order" alias="alias1_12"/>
    <Level name="Year" table="alias1_12" column="dateacct" uniqueMembers="true" levelType="TimeYears" type="Numeric">
      <KeyExpression>
        <SQL>extract(year from alias1_12.dateacct)</SQL>
      </KeyExpression>
    </Level>
    <Level name="quarter" column="dateacct" table="alias1_12" uniqueMembers="false" levelType="TimeQuarters" type="Numeric">
      <KeyExpression>
        <SQL>extract(quarter from alias1_12.dateacct)</SQL>
      </KeyExpression>
    </Level>
    <Level name="month" table="alias1_12" column="dateacct" uniqueMembers="false" type="Numeric" levelType="TimeMonths">
      <KeyExpression>
        <SQL>extract(month from alias1_12.dateacct)</SQL>
      </KeyExpression>
    </Level>
    <Level name="day" table="alias1_12" column="dateacct" ordinalColumn="dateacct" uniqueMembers="false" type="Numeric" levelType="TimeDays">
      <KeyExpression>
        <SQL>extract(day from alias1_12.dateacct)</SQL>
      </KeyExpression>
    </Level>
  </Hierarchy>
</Dimension>

Parent - Child Relations and mapping to Mondrian

Parent - child hierarchies are important for analytics as it is allows aggregation of measures accross business hierarchies.

Openbravo allows defining hierarchical structures through the standard Openbravo tree concept.

In core Openbravo the following entities can be organized in tree structures:

• Organization
• Business Partner Category
• Product Category
• Project
• Element Value (Account Structures)

Within Mondrian it is possible to explicitly model parent - child hierarchies.

Openbravo Analytics uses the Openbravo tree structures and maps these structures to Mondrian in a generic way. This is for example visualized in the generic OB Analytics view shown below.

Aggregating information across multi-level parent-child hierarchies can be a performance intensive operation. To optimize this, Mondrian has the concept of closure tables. Openbravo Analytics implements the Mondrian closure tables through its own Tree Closure table. This table is automatically updated when the Openbravo tree table is updated.

Updating parent-child information

Parent-child information is stored in a separate analytics table and columns (see next sections). These tables/columns need to be updated whenever the parent-child relations change in the business master data. To do this run the process as described here.

Parent-Child Relations: use explicit Parent/Ordinal Columns

Mondrian supports the so-called closure table to aggregate data over parent - child hierarchies. However we have noticed that the closure table implementation of Mondrian is unstable. Therefore the advice is to always create an explicit parent-ordinal column pair as is described in this section.

Also when doing direct sql querying it is however better (from a performance perspective) to have a direct parent column in the entity table. Openbravo supports this through the special parent id column. When building the closure table, Openbravo will detect if the entity has a 'obanalyTreeparentid' column/property. This is a column with a name which ends with 'obanalyTreeparentid'. If the entity has such a column then this column will be updated with the parent id determined from the tree and this parent column will be used in the Mondrian mapping. For keeping track of the ordinal (the order in the tree) you should also create a column ending on 'obanalyTreeordinal'.

So to make use of trees in OB Analytics one should take the following actions:

• create 2 columns (for ordinal and parentid) in the database in the source table for the tree (for example for the organization tree these columns are present in the ad_org table). The columns need to have the name as specified above (ending on 'obanalyTreeparentid' and 'obanalyTreeordinal'.
• also create/add the 2 columns in the application dictionary definition. For example here is the AD definition for the parent id for ad_org.

Also, after creating the columns then rerun the update tree and time dimension process.

Ordering in Parent-Child: the Ordinal Column

Entities which are not part of a parent - child tree will be ordered within their level using their first identifier column.

If an entity is part of a parent - child tree and the level is mapped using this tree (in case it is not a snowflake mapping, see the limitations chapter below), then the ordering within the tree is being used. The Openbravo - Mondrian integration will try to maintain the ordering defined in the tree (when adding an instance to a tree, using drag and drop). This tree order is stored witin the entity record itself using a column with a pre-defined name. To maintain the ordering in the entity tree the table/entity needs to have a column with a name which ends with the following string: obanalyTreeordinal. The analytics module will automatically update this tree ordinal column when a change is detected in the ad_tree table.

Category: Analytics