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

    Communities Project - Data mapping for COMMENT, DOCUMENT in JIVE_ANALYTICS schema (Jive7)

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

       

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

       

      1) COMMENT

       

      1.1) Comment Delete Date

      From JIVEDW_ACTIVITY_META table we understand that we store information about Comment 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 …..’

       

      However, in Jive7 Development environment, we find no data for comment deletes in JIVEDW_ACTIVITY_FACT table.

       

      The query bellow returns no data:

      select f.* from jive_analytics.JIVEDW_ACTIVITY_FACT f

      where f.direct_object_type = 105 --comment

      and f.activity_type = 30; --delete

       

      --Object type = Comment

      SELECT object_type

      FROM jive_analytics.jivedw_object_meta

      where lower(object_name) = 'comment'; -- 105

       

      --Activity type = Delete

      SELECT activity_type

      FROM jive_analytics.jivedw_activity_meta

      where lower(activity_name) = 'delete'; --30

       

      Can you please tell us if this is because there were no comment deletes / the ETL process that brings data from Jive Engage schema to Jive Analytics schema has not run?

      We should be able to find data about comment deletes by using the query?

       

      1.2) Comment Last Update Date

       

      From JIVEDW_ACTIVITY_META table we understand that we store information about updates for content, but did not specifically find “comment” in the Description field:

       

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

      Activity_type = 40, Activity_name = ‘Modify’, Description = ’Fires when any major content types including announcement, space (such as changing its name), project, social group, bookmark, user profile, or user avatar is modified. For content, this includes editing the post or tags of the post. For containers, this only includes when the title, description, or tags are modified. This will also be fired if the community organization chart is modified.’

       

      Can you please confirm if we can find information about Comment updates in JIVEDW_ACTIVITY_FACT table with the query bellow?

       

      select f.ACTIVITY_TS

      from jive_analytics.JIVEDW_ACTIVITY_FACT f

      where f.direct_object_type = 105 --comment

      and f.activity_type = 40; --modify

       

      This query returns no data on Dev environment – is this because there were no comment updates or the ETL process that brings data from Jive Engage schema to Jive Analytics schema has not run?

       

       

      2) DOCUMENT

       

      2.1) Move Document

      Document movements impact important KPIs such as “# of Documents” or “# of Document reads”.

      Business asked us to track these document movements between different spaces in order to be able to justify any change in KPIs.

       

      From JIVEDW_ACTIVITY_META table we understand that we store information about movements, but we did not find a reference to “document” in the Description field.

       

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

      Activity_type = 70, Activity_name = ‘Move’, Description = ‘Fired when any major content type or space is moved. This can fire for a blog for one specific scenario: if a community that has a blog is being merged into another community that does not already have a community.’

       

      Can you please tell us if we can find information about document moves in JIVEDW_ACTIVITY_FACT table using the query bellow?

       

      select f.*

      from jive_analytics.JIVEDW_ACTIVITY_FACT f

      where f.DIRECT_OBJECT_TYPE = 102  --document

      and f.activity_type = 70; --move

       

      This query returns no data in Dev environment – is this because there were no document moves / the ETL process has not run?

       

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

       

      Thank you,

      Georgiana