2 Replies Latest reply on Mar 20, 2014 5:25 AM by georgianateler

    Communities Project - Data mapping for RESPONSE, SPACE in JIVE_ANALYTICS schema (Jive7)


      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/).


      Can you please help us confirm/find the following mappings?


      1) RESPONSE

      1.1) Response Delete Date


      From JIVEDW_ACTIVITY_META table we understand that we store information about response/message deletes.


      SELECT * FROM jivedw_activity_meta where lower(activity_name) = 'delete';

      Activity_type = 30, Activity_name = ‘Delete’, Description = ‘Fires when any major content types including poll, project, social group, blog, space, announcement, user, avatar, task, message (from a discussion), comment, bookmark, or attachment is deleted …..’


      Can you please confirm that we can use the query bellow to obtain Response Delete Date?


      select f.ACTIVITY_TS

      from jive_analytics.JIVEDW_ACTIVITY_FACT f

      where f.direct_object_type = 2 --message

      and f.ACTIVITY_TYPE = 30 --delete

      and f.DIRECT_DW_OBJECT_ID = <dw message id>;


      1.2) Positive Ratings


      We understood that “Rating” is a 1-5 star value that can be applied to content.

      In JIVEDW_ACCLAIM table, we saw that messages (object_type=2) received only 'likes', not also 'rates'.


      Can you please tell us if messages can receive also rates (1-5 points) ?


      select distinct acclaim_type

      from jivedw_acclaim

      where object_type = 2;  --for “message”, the query returns only 'like'



      2) SPACE


      2.1) Previous Space Name


      We understand that in JIVEDW_ACTIVITY_FACT table we can find information about space modifications (like space name change).

      Can we deduce the previous space name from the Metadata field in JIVEDW_ACTIVITY_FACT table?

      If yes, how exactly? Can we find the information stored something like: newname='name2’, oldname = ‘name1’ ?


      select f.metadata

      from jive_analytics.JIVEDW_ACTIVITY_FACT f

      where f.DIRECT_OBJECT_TYPE = 14  --space

      and f.activity_type = 40 --modify

      and f.direct_dw_object_id = <dw space id>;


      2.2) Space Status

      We understood that a space can have a status : 1=Active, 2=Archived, 3=Deleted

      Can you tell us in what tables/columns can we find this information in JIVE_ANALYTICS schema ?


      Please let me know if I can provide you more information regarding my questions!


      Thank you,