1 Reply Latest reply on Mar 24, 2014 3:24 PM by georgianateler

    Communities project - Data mapping for Document 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) DOCUMENT

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

       

      a) I used the 2 queries bellow and obtained the same results for document_id = 758282

       

      Query 1:

      select u.username, 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 u.user_id = f.user_id

      and f.direct_object_type = 102 -- document

      and f.activity_type = 20 -- create

      and o.object_id = 758282; --object_id, which is the same as JIVEDW_DOCUMENT.document_id

       

      We obtain the following results : jive7admin3   06-FEB-14 06.46.18.547000000 AM

       

      Query 2:

      select u.username, d.creation_ts

      from jive_analytics.jivedw_document d, jive_analytics.JIVEDW_OBJECT_USER o, jive_analytics.JIVEDW_USER u

      where d.document_id = 758282

      and d.document_id = o.object_id

      and o.object_type = 102  --document

      and o.user_id = u.user_id;

       

      We obtain the following results : jive7admin3    06-FEB-14 06.46.18.444000000 AM

       

      Can you please :

      - confirm if we can use both queries to obtain same results ;

      - explain the difference for date of creation in JIVEDW_DOCUMENT vs JIVEDW_ACTIVITY_FACT table ;

      - confirm that the correct creation date for the document is CREATION_TS in JIVEDW_DOCUMENT table.

       

      b) I used the same 2 queries for more documents, and I did NOT find same results with the 2 queries :

       

      Query 1:

      select u.username, 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 u.user_id = f.user_id

      and f.direct_object_type = 102 -- document

      and f.activity_type = 20 -- create

      and o.object_id in (2353,2356,2361,2368);

       

      No rows returned.

       

      Query 2:

      select u.username, d.creation_ts

      from jive_analytics.jivedw_document d, jive_analytics.JIVEDW_OBJECT_USER o, jive_analytics.jivedw_user u

      where d.document_id in (2353,2356,2361,2368)

      and d.document_id = o.object_id

      and o.object_type = 102  --document

      and o.user_id = u.user_id;

       

      We obtain the following results :

      Vaibhav Bhawsar     30-OCT-08 02.36.50.000000000 AM

      Krishna Bihari         30-OCT-08 02.52.16.000000000 AM

      Alpana Kumari        30-OCT-08 03.31.04.000000000 AM

      Timothy Boles        30-OCT-08 04.05.26.000000000 AM

       

      Seen these examples : which of the 2 queries is safe to use in order to obtain the correct results for all documents ?

       

       

      1.2) Last Updated by : we need to find the user that last updated the document :

       

      a) Can you please confirm that we can use the query bellow ?

       

      select u.username

      from jive_analytics.JIVEDW_USER u, jive_analytics.JIVEDW_ACTIVITY_FACT f

      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_OBJECT o

      where f.direct_dw_object_id = o.dw_object_id

      and f.direct_object_type = 102 -- document

      and f.activity_type = 40 --modify

      and o.object_id = 758282 -- JIVEDW_OBJECT.object_id, which corresponds to JIVEDW_DOCUMENT.document_id

      );

       

      Please let me know if I can use a better query to obtain the same information.

       

      b) When running the same query at point 1.2) a) for the document_id = 758303, I observed that we obtain 2 usernames (jive7admin, jive7admin):

      This is because in JIVEDW_ACTIVITY_FACT, we have 2 activities of modification on the document 758303, performed by the same user at the same time (13-FEB-14 08.32.44.317000000 PM).

       

      select * from JIVEDW_ACTIVITY_FACT where activity_ts = '13-FEB-14 08.32.44.317000000 PM';

      --returns 2 rows, different only by the information in "metadata" column

       

      Can you please explain if :

      - it is acceptable to have 2 actions with the same activity_ts in JIVEDW_ACTIVITY_FACT table ? I saw that activity_ts column is not unique, but can you confirm that there is no data issue on DEV environment ?

      - in my example, this means that the same user performed 2 modifications in the same time and clicked the "save" button only once ?

      - if in JIVEDW_ACTIVITY_FACT we have 2 actions on the same object, with the same activity_ts, the user_id is necessary the same ?

        This is to say, we can not have the situation when 2 different users perform modifications on the same object, at the same time ?

       

      1.3) Date Last Movement : we have to track information about document movements (from a space to another).

       

      Can you please confirm that we can find the date of the last movement with the following query ?

       

      select max(f.activity_ts)

      from jive_analytics.JIVEDW_ACTIVITY_FACT f, jive_analytics.JIVEDW_OBJECT o

      where f.direct_dw_object_id = o.dw_object_id

      and f.activity_type = 70 --move

      and f.DIRECT_OBJECT_TYPE = 102  --document

      and o.object_id = 758282; -- JIVEDW_OBJECT.object_id, which corresponds to JIVEDW_DOCUMENT.document_id

       

      On DEV environment there is no data for document movements, but can you please let me know if this query should work ?

       

       

      1.4) Last Moved By

      Can you please confirm that we can find the user that performed the last movement on a document with the following query ?


      select u.username

      from jive_analytics.JIVEDW_USER u, jive_analytics.JIVEDW_ACTIVITY_FACT f

      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_OBJECT o

      where f.direct_dw_object_id = o.dw_object_id

      and f.activity_type = 70 --move

      and f.DIRECT_OBJECT_TYPE = 102  --document

      and o.object_id = 758282);

       

      1.5) Positive Ratings : we have to find the number of likes received for a document.

       

      a) In Jive 7 Documentation (SchemaSpy - db_700op_default_analytics.public), there is no link specified between jivedw_document_stats_fact.content_id and jivedw_document.document_id.

      However, can you please confirm that we can use the following query ?

       

      select a.*, b.activity_date, b.likes

      from jive_analytics.jivedw_document a, jive_analytics.jivedw_document_stats_fact b

      where b.content_id = a.document_id

      and b.likes > 0;

       

      b) Can you also confirm that content_id is a foreign key only to JIVEDW_DOCUMENT table ?

      Can use the query from point a) without any other filter ?

       

      Appreciate your help!

       

      Thank you,

      Georgiana

       

      Message was edited by: Georgiana Teler