0 Replies Latest reply on Apr 4, 2014 6:49 AM by georgianateler

    Communities Project - JIVEDW_OBJECT, JIVEDW_OBJECT_USER tables in Jive7 (JIVE_ANALYTICS schema)

    georgianateler

      Hi Jive Team,

       

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

      We are mapping our business requirements to JIVE_ANALYTICS schema in Jive7 (http://docs.jivesoftware.com/schemas/7_0/analytics/).

       

      I have some questions regarding JIVEDW_OBJECT (documented in the Analytics schema) and JIVEDW_OBJECT_USER table (NOT documented in the Analytics schema).

      The following examples are on DEV environment.

       

      Q1) Can you please confirm the significance of each field in JIVEDW_OBJECT_USER table ?

      OBJECT_ID       -- the id of an object (comment, message, thread) and a foreign key to JIVEDW_COMMENT.comment_id, JIVEDW_MESSAGE.message_id, JIVEDW_THREAD.thread_id

      OBJECT_TYPE -- the type of an object and a foreign key to JIVEDW_OBJECT_META.object_type

      USER_ID          -- the user who created the object ?

       

      We are using JIVEDW_OBJECT_USER table in our mappings, in relation with JIVEDW_ACTIVITY_FACT, JIVEDW_COMMENT, JIVEDW_THREAD, JIVEDW_DOCUMENT tables and we need to know if we can trust the information in JIVEDW_OBJECT_USER.

       

       

      Q2) I took some examples to verify if JIVEDW_OBJECT_USER.user_id is the user who created the objects.

       

      Example 1: Find the user who created a thread (thread_id = 1294112)

       

      Step 1) select * from jivedw_thread where thread_id = 1294112; -- the thread exists

      Step 2) select object_type from jivedw_object_meta where lower (object_name) = 'thread'; --returns object_type = 1

      Step 3) select * from jivedw_object_user where object_id = 1294112 and object_type = 1; --we obtain the user_id = 592702  -- need to confirm if this is the user who created the thread

      Step 4) select * from jivedw_object where object_id = 1294112 and object_type = 1; --we obtain the dw object id for that object, which is dw_object_id = 7772592

      Step 5) select activity_type from jivedw_activity_meta where lower (activity_name) = 'create'; --returns activity_type=20

      Step 6) select * from jivedw_activity_fact where direct_dw_object_id = 7772592 and activity_type = 20; -- we find a "create" activity performed by the same user_id as in JIVEDW_OBJECT_USER table, user_id = 592702

       

      Conclusion : On this example, the supposition that JIVEDW_OBJECT_USER.user_id is the user who created the thread is confirmed.

      Questions : Can you please validate also this example ?

       

      Example 2: Find the user who created a thread (thread_id = 920804)


      Step 1) select * from jivedw_thread where thread_id = 920804;   --the thread exists

      Step 2) select * from jivedw_object_user where object_type = 1 and object_id = 920804; --we obtain user_id = 9375174 -- need to confirm if this is the user who created the thread

      Step 3) select * from jivedw_object where object_id = 920804 and object_type = 1; -- we obtain dw_object_id = 7629269

      Step 4) select * from jivedw_activity_fact where direct_dw_object_id = 7629269; --we find 4 activities with activity_type = 10 ("view"), performed by a different user 9092872. No row for "create" activity

      Step 5) select activity_name from jivedw_activity_meta where activity_type = 10; --returns "view"

       

      Conclusion: The supposition is NOT confirmed (we do not find corresponding information in JIVEDW_ACTIVITY_FACT table).

      Questions:

      - In Step 4) shouldn't we find a row for the creation of the thread? Is this a data problem on DEV environment ?

      - In this case, how can we determine the user who created the thread ?

       

      Example 3: Find the user who created a thread (thread_id = 2084)

       

      Step 1) select * from jivedw_thread where thread_id = 2084;   --the thread exists

      Step 2) select * from jivedw_object_user where object_type = 1 and object_id =2084; --we obtain user_id = 3058 -- need to confirm if this is the user who created the thread

      Step 3) select * from jivedw_object where object_id = 2084 and object_type = 1; -- the thread does NOT exist in the table => we can not find dw_object_id

      Step 4) select * from jivedw_activity_fact where user_id = 3058; --we find no rows for this user

       

      Questions:

      - In Step 3) shouldn't we find a row for the  thread? Is this a data problem on DEV environment ?

      With the query select * from jivedw_object where object_id = 2084; -- we find 2 rows, but for the object_type = 41 (representing a 'tag') and object_type = 2020 (representing a 'User container')

      select * from jivedw_object_meta; 41  -- tag;  2020 --User container

       

      - In Step 4) shouldn't we find at least 1 row for the creation of the thread by user_id = 3058 ? Is this a data problem on DEV environment ?

      - In this case, how can we determine the user who created the thread ?

       

       

      Q3) My understanding is that JIVEDW_OBJECT holds the correspondence between object_id and dw_object_id (datawarehouse object id) for all objects, but this is not verified by the number of rows in each table.

       

      select object_type, object_name from jivedw_object_meta where lower (object_name) in ('thread', 'message', 'space', 'comment');

      1     Thread

      2     Message

      14   Space

      105 Comment

       

      For threads :

      select count(*) from JIVEDW_OBJECT where object_type = 1; --returns 122542 rows

      select count(*) from JIVEDW_THREAD;                                  --returns 596561 rows

       

      For messages :

      select count(*) from JIVEDW_OBJECT where object_type = 2; --returns 2118533 rows

      select count(*) from JIVEDW_MESSAGE;                              --returns 2118516 rows

       

      For spaces :

      select count(*) from JIVEDW_OBJECT where object_type = 14; --returns 1223 rows

      select count(*) from JIVEDW_COMMUNITY;                             --returns 1222 rows

       

      For comments

      select count(*) from JIVEDW_OBJECT where object_type = 105; --returns 47 rows

      select count(*) from JIVEDW_COMMENT;                                 --returns 1906 rows

       

      - Please explain the relation between JIVEDW_OBJECT and JIVEDW_THREAD, JIVEDW_MESSAGE, JIVEDW_COMMUNITY, JIVEDW_COMMENT tables.

      - Also, explain the difference between the number of rows for threads, messages, spaces, comments. Is this a data problem in DEV environment ?

       

       

      Please let me know if I can provide you more information about my question !

       

      Thank you,

      Georgiana