4 Replies Latest reply: Mar 5, 2013 9:08 AM by DanieleRM RSS

    HELP: how to build a parent-child hierarchy

    DanieleRM
      Hy everyone,

      my question is very basic: i need to know what is the structure of the relational tables that OBIEE accepts upon which i can build a parent-child hierarchy. I have data that OBIEE does not accept, do you know where i can find a clear description of the structure of the tables that has to be builded for creating correctly a parent-child hierarchy?

      Thanks anyone a lot,
      Regards
        • 1. Re: HELP: how to build a parent-child hierarchy
          JaiG
          See if these help at all: http://www.adivaconsulting.com/adiva-blog/item/22-obiee-11g-hierarchical-table.html
          http://www.rittmanmead.com/2010/11/oracle-bi-ee-11g-parent-child-hierarchies-multiple-modeling-methods/

          Please award points if helpful/correct.
          • 2. Re: HELP: how to build a parent-child hierarchy
            Sasi  Nagireddy
            Hi,

            Can u be bit clear what kind of data you have? What do u mean by OBIEE does not support the data?If u need any support on creating Parent Child Hierarchy we can help u.

            Hoping for your prompt updates?

            Thanks.
            • 3. Re: HELP: how to build a parent-child hierarchy
              DanieleRM
              Basically, i have to convert the datas behind an EssBase Planning Cube into a structure of datas that allows me to build a model in OBIEE. I cannot use the direct connection to the EssBase Cube for a problem of performance. I need to know if i need to work on the tables and how should i convert them in a structure thet OBIEE accepts, because the dimensional tables are based on a parent-child hierarchy and the users wants to navigate through hierarchies in a similar way they do this in EssBase.
              So what i would know is: what is the structure of the datas that i should give to OBIEE? I have the fact table, with measures and a foreign key linked to every dimension, and the dimensional tables, which are like this: cod_parent column, cod_child column, other attributes of the dimension. Plus, these is no flag indicating that a member is a leaf, and root member has as a parent the name of the hierarchy itself.
              Do i need to build new physical tables starting from these ones?
              Thanks everyone
              • 4. Re: HELP: how to build a parent-child hierarchy
                DanieleRM
                I've found what i needed, for everyone else:

                http://docs.oracle.com/cd/E21043_01/bi.1111/e10540/dimensions.htm#BGBGACFJ

                The parent-child relationship table must include four columns, as follows:
                •A column that identifies the member
                •A column that identifies an ancestor of the member. Note: The ancestor may be the parent of the member, or a higher-level ancestor.
                •A "distance" column that specifies the number of parent-child hierarchical levels from the member to the ancestor
                •A "leaf" column that indicates if the member is a leaf member (1=Yes, 0=No)

                The column names can be user defined. The data types of the columns must satisfy the following conditions:
                •The member and ancestor identifier columns have the same data type as the associated columns in the logical table that contains the hierarchy members. Note that the example shown in Table 9-1 uses text strings for readability, but you normally use integer surrogate keys for member_key and ancestor_key, if they exist in the source dimension table.
                •The "distance" and "leaf" columns are INTEGER columns.

                Note the following about the rows in a parent-child relationship table:
                •Each member must have a row pointing at itself, with distance zero.
                •Each member must have a row pointing at each of its ancestors. For a root member, this is a termination row with null for the parent and distance values.