3 Replies Latest reply on Mar 27, 2014 2:39 AM by astan

    Communities project - Data mapping for Comment on Jive 7 (JIVE_ANALYTICS)

    georgianateler

      Hi Jive team,

       

      I am part of MyOracleSupport (MOS) Analytics team and I am working on data mapping for Communities project.

      I need you help to validate/find the mapping for the following fields on Jive 7 (JIVE_ANALYTICS schema).

       

      I did my research on DEV environment.

       

      1) COMMENT

      1.1) Created by : we need to find the user that created the comment.

       

      I found 2 queries that return the same results for the User that created the Comment :

       

      Query 1:

      select u.username

      from jive_analytics.JIVEDW_COMMENT c, jive_analytics.JIVEDW_USER u

      where c.user_id = u.user_id

      and c.comment_id in

      (757029,

      757030,

      757060,

      757059,

      757076);

       

      Returns :

      vijayagr

      jive7admin

      HolgerTamm-Oracle

      HolgerTamm-Oracle

      JeffL-Oracle

       

      Query 2:

      select u.username

      from jive_analytics.JIVEDW_ACTIVITY_FACT f, jive_analytics.JIVEDW_USER u, jive_analytics.JIVEDW_OBJECT o

      where f.direct_dw_object_id = o.dw_object_id

      and f.user_id = u.user_id

      and f.direct_object_type = 105 --comment

      and f.activity_type = 20 --create

      and o.object_id in

      (757029,

      757030,

      757060,

      757059,

      757076);

       

      Returns the same results :

      vijayagr

      jive7admin

      HolgerTamm-Oracle

      HolgerTamm-Oracle

      JeffL-Oracle

       

      a) Can you please confirm that we can use both queries to find the User that Created the comment ?

      b) In JIVEDW_COMMENT table, user_id is the id of the user that created the comment, not of the user that updated the comment - correct ?

       

      1.2) Last Updated by : we need to find the user that updated last a comment.

       

      Can you please confirm that we can use the following query ?

       

      select u.username

      from jive_analytics.JIVEDW_ACTIVITY_FACT f, jive_analytics.JIVEDW_USER u

      where f.user_id = u.user_id

      and f.activity_ts =

      (select max(f.activity_ts)

      from jive_analytics.JIVEDW_ACTIVITY_FACT f, jive_analytics.JIVEDW_USER u, jive_analytics.JIVEDW_OBJECT o

      where f.direct_dw_object_id = o.dw_object_id

      and f.user_id = u.user_id

      and f.direct_object_type = 105 --comment

      and f.activity_type = 40 --modify

      and o.object_id = 757164 --JIVEDW_OBJECT.object_id, which corresponds to JIVEDW_COMMENT.comment_id

      );

       

      Appreciate your help!

       

      Thank you,

      Georgiana

        • Re: Communities project - Data mapping for Comment on Jive 7 (JIVE_ANALYTICS)
          georgianateler

          Hi josh.richau

           

          Can you please help us with the questions above ?

          We are under time constraints to finish the data mapping for Communities project and we would really appreciate the responses !

           

          Thank you,

          Georgiana

          • Re: Communities project - Data mapping for Comment on Jive 7 (JIVE_ANALYTICS)

            Your second query for Comment's created-by is more accurate. Jivedw_comment could update it's user_id if another user updated a comment. Last-Updated-By query looks correct.

              • Re: Communities project - Data mapping for Comment on Jive 7 (JIVE_ANALYTICS)
                astan

                Hi josh.richau

                 

                It seems the answer from Lynn Jepsen is not 100% correct.

                According with Lynn, "Jivedw_comment could update it's user_id if another user updated a comment". With my research, it seems this is not correct.

                Please, confirm that jivedw_comment.user_id is the user who creates the comment and it is not updated anymore.

                 

                Running this query:

                (A) select u.username

                from jive_analytics.JIVEDW_ACTIVITY_FACT f, jive_analytics.JIVEDW_USER u, jive_analytics.JIVEDW_OBJECT o

                where f.direct_dw_object_id = o.dw_object_id

                and f.user_id = u.user_id

                and f.direct_object_type = 105 --comment

                and f.activity_type = 20 --create

                and o.object_id = 757164;

                 

                returns "myprofile_test109". This query shows the user who created the comment using the main JIVEDW_ACTIVITY_FACT and JIVEDW_OBJECT tables.

                 

                On the other hand, I am running:

                (B) select u.username

                from jive_analytics.JIVEDW_COMMENT c, jive_analytics.JIVEDW_USER u

                where c.user_id = u.user_id

                and c.comment_id = 757164;

                 

                and I get the same result like above: "myprofile_test109"

                 

                Then, running the query Lynn mentioned is correct (and I agree with her) that it is suppose to give the username of who updated last time the comment:

                (C) select u.username

                from jive_analytics.JIVEDW_ACTIVITY_FACT f, jive_analytics.JIVEDW_USER u

                where f.user_id = u.user_id

                and f.activity_ts =

                (select max(f.activity_ts)

                from jive_analytics.JIVEDW_ACTIVITY_FACT f, jive_analytics.JIVEDW_USER u, jive_analytics.JIVEDW_OBJECT o

                where f.direct_dw_object_id = o.dw_object_id

                and f.user_id = u.user_id

                and f.direct_object_type = 105 --comment

                and f.activity_type = 40 --modify

                and o.object_id = 757164

                );

                 

                returns "Sandy-Oracle"

                 

                Therefore,"Sandy-Oracle" is the user who updated last time comment_id = 757164 and "myprofile_test109" is the user who created the same comment.

                 

                Please, confirm the following:

                Queries (A) and (B) returns the user who created the comment and query (C) returns the user who updated last time the comment.

                 

                Thank you,

                Adrian