2 Replies Latest reply on Mar 10, 2010 1:05 PM by mark.williams@jivesoftware.com

    Identifying Parent/Children Relationships

      Use Case:

      I want to perform reporting limited to the sub-spaces that exist for a specific space.  For example, we have broken out Information Technology spaces (we refer to as communities).  I want to report metrics on all sub-communities under this space.

       

      Two Questions:

       

      1) Data Warehouse Tables

      I don't see any tables/fields that give me the parent/child/left/right tree relationships.  If these exist, please detail which ones I can use.

       

       

      2) SBS Operational Tables 

      I thought that I was on to something with the left/right attributes in the jivecommunity table - but these values do not seem to coorespond to the id's that I expected.  The relational diagrams don't seem to address how the tables support the parent/child relationships.  Can you please detail this out?

       

      Thanks, Tom.

        • Re: Identifying Parent/Children Relationships

          I am still interested in help on this one.

          • Re: Identifying Parent/Children Relationships

            Tom,

             

            1. Community specific attributes such as parent/child relationships is not available in 3.0. However, these attributes have been brought into analytics in 4.0. There is a new dimension table called jivedw_community which contains the lfg and rgt columns from jivecommunity.

             

            2. You are correct that it is the lft and rgt columns that are used for retrieving the descendants on a community. In order to select all descendants for a particular community you would perform a query like this:

             

             

            select communityid from jivecommunity where lft < x and rgt > y
            

             

            where x = the parent community's lft value and rgt = the parent communty's rgt value.

             

            If you want to include the starting parent community itself then you can change the comparison operators to <= and >= respectively.