Contents

  • Database Tables

    • This document describes tables in the analytics database. The analytics data model is a star schema form.

    The schema is comprised of a fact table that represents the events in Jive SBS, and corresponding dimension tables that represent the actors and objects that take part in those events. Each column in the fact table contains a key that relates to an entry in the corresponding dimension table. For example, the user_id column contains ids that can be found in the jivedw_user table. A basic query against the analytics schema will be a SELECT from the fact table, optionally performing INNER JOINS against the dimension tables. The dimension tables are used both constrain the results of the query based on their attributes and also to provide attribute data, e.g. the usernames of users.

    Note: Some information, like column indexes and foreign keys, is omitted. For this, please read the individual schema of the database you're interested in. Data types may also differ somewhat in each individual database schema.

    For more about the schema's design, be sure to check out Analytics Data Model in the Developers' Guide on Jivespace.

    Attention: When you provision the database you'll be using for analytics data, be sure that it supports stored procedures. For example, on PostgreSQL you can use the CREATE LANGUAGE command on your server to register the needed procedural language:
    CREATE LANGUAGE plpgsql

    Data Type Conventions

    Database Tables

    Table Name Description
    jivedw_activity_agg_day Aggregate Activity Fact Table at Day Granularity
    jivedw_activity_agg_month Aggregate Activity Fact Table at Month Granularity
    jivedw_activity_agg_user_day Aggregate Activity Fact Table over all Users at Day Granularity
    jivedw_activity_agg_user_month Aggregate Activity Fact Table over all Users at Day Granularity
    jivedw_activity_agg_user_week Aggregate Activity Fact Table over all Users at Week Granularity
    jivedw_activity_agg_week Aggregate Activity Fact Table at Day Granularity
    jivedw_activity_fact Activity Fact Table
    jivedw_activity_stage Data Warehouse Table for Tracking User Activity.
    jivedw_blogpost Blog post dimension table.
    jivedw_blogpost_stage Blog post dimension staging table.
    jivedw_container Container dimension
    jivedw_container_stage Container dimension stage
    jivedw_day Time dimension for Day granularity
    jivedw_document Document dimension table.
    jivedw_document_stage Document dimension staging table.
    jivedw_etl_job ETL Jobs
    jivedw_etl_task ETL Tasks
    jivedw_message Jive Message dimension
    jivedw_message_stage Jive Message dimension
    jivedw_month Time dimension for Month granularity
    jivedw_object Object dimension table.
    jivedw_profilefield Profile field data table.
    jivedw_profilefield_stage Profile field data table.
    jivedw_user User dimension
    jivedw_user_stage User dimension stage
    jivedw_userprofile User profile data table.
    jivedw_userprofile_stage User profile data stage table.
    jivedw_version Version data table.
    jivedw_week Time dimension for Day granularity
     

    jivedw_activity_agg_day Table

    Aggregate Activity Fact Table at Day Granularity

    Column Name Type Length Nullable Unicode Description
    activity_count int N/A false   Count of the activities for the day
    activity_type int N/A false   Activity type
    day_id int N/A false   Time the activity occurred - foreign key to time dimension
    direct_dw_object_id bigint N/A false   id of direct object of action
    direct_object_type int N/A false   type of direct object of action
    dw_container_id bigint N/A true   Container of the target at the time of the activity
    indirect_dw_object_id bigint N/A true   id of indirect object of action
    indirect_object_type int N/A true   type of indirect object of action
    user_id bigint N/A false   User that initiated the activity

    Back to the table list.

    jivedw_activity_agg_month Table

    Aggregate Activity Fact Table at Month Granularity

    Column Name Type Length Nullable Unicode Description
    activity_count int N/A false   Count of the activities for the day
    activity_type int N/A false   Activity type
    direct_dw_object_id bigint N/A false   id of direct object of action
    direct_object_type int N/A false   type of direct object of action
    dw_container_id bigint N/A true   Container of the target at the time of the activity
    indirect_dw_object_id bigint N/A true   id of indirect object of action
    indirect_object_type int N/A true   type of indirect object of action
    month_id int N/A false   Time the activity occurred - foreign key to time dimension
    user_id bigint N/A false   User that initiated the activity

    Back to the table list.

    jivedw_activity_agg_user_day Table

    Aggregate Activity Fact Table over all Users at Day Granularity

    Column Name Type Length Nullable Unicode Description
    activity_count int N/A false   Count of the activities for the day
    activity_type int N/A false   Activity type
    day_id int N/A false   Time the activity occurred - foreign key to time dimension
    direct_dw_object_id bigint N/A false   id of direct object of action
    direct_object_type int N/A false   type of direct object of action
    dw_container_id bigint N/A true   Container of the target at the time of the activity
    indirect_dw_object_id bigint N/A true   id of indirect object of action
    indirect_object_type int N/A true   type of indirect object of action

    Back to the table list.

    jivedw_activity_agg_user_month Table

    Aggregate Activity Fact Table over all Users at Day Granularity

    Column Name Type Length Nullable Unicode Description
    activity_count int N/A false   Count of the activities for the month
    activity_type int N/A false   Activity type
    direct_dw_object_id bigint N/A false   id of direct object of action
    direct_object_type int N/A false   type of direct object of action
    dw_container_id bigint N/A true   Container of the target at the time of the activity
    indirect_dw_object_id bigint N/A true   id of indirect object of action
    indirect_object_type int N/A true   type of indirect object of action
    month_id int N/A false   Time the activity occurred - foreign key to time dimension

    Back to the table list.

    jivedw_activity_agg_user_week Table

    Aggregate Activity Fact Table over all Users at Week Granularity

    Column Name Type Length Nullable Unicode Description
    activity_count int N/A false   Count of the activities for the month
    activity_type int N/A false   Activity type
    direct_dw_object_id bigint N/A false   id of direct object of action
    direct_object_type int N/A false   type of direct object of action
    dw_container_id bigint N/A true   Container of the target at the time of the activity
    indirect_dw_object_id bigint N/A true   id of indirect object of action
    indirect_object_type int N/A true   type of indirect object of action
    week_id int N/A false   Time the activity occurred - foreign key to time dimension

    Back to the table list.

    jivedw_activity_agg_week Table

    Aggregate Activity Fact Table at Day Granularity

    Column Name Type Length Nullable Unicode Description
    activity_count int N/A false   Count of the activities for the day
    activity_type int N/A false   Activity type
    direct_dw_object_id bigint N/A false   id of direct object of action
    direct_object_type int N/A false   type of direct object of action
    dw_container_id bigint N/A true   Container of the target at the time of the activity
    indirect_dw_object_id bigint N/A true   id of indirect object of action
    indirect_object_type int N/A true   type of indirect object of action
    user_id bigint N/A false   User that initiated the activity
    week_id int N/A false   Time the activity occurred - foreign key to time dimension

    Back to the table list.

    jivedw_activity_fact Table

    Activity Fact Table

    Column Name Type Length Nullable Unicode Description
    activity_ts timestamp N/A false   Original timestamp of the activity
    activity_type int N/A false   Activity type
    day_id int N/A false   Time the activity occurred - foreign key to time dimension
    direct_dw_object_id bigint N/A false   id of direct object of action
    direct_object_type int N/A false   type of direct object of action
    dw_container_id bigint N/A true   Container of the target at the time of the activity
    indirect_dw_object_id bigint N/A true   id of indirect object of action
    indirect_object_type int N/A true   type of indirect object of action
    metadata varchar 256 true   arbitrary metadata depending on the event
    user_id bigint N/A false   User that initiated the activity

    Back to the table list.

    jivedw_activity_stage Table

    Data Warehouse Table for Tracking User Activity.

    Column Name Type Length Nullable Unicode Description
    activity_ts timestamp N/A false   Timestamp of user activity
    activity_type int N/A false   What the user did
    container_id bigint N/A true   id of container direct object resides in
    container_type int N/A true   type of container direct object resides in
    direct_object_id bigint N/A false   id of direct object of action
    direct_object_type int N/A false   type of direct object of action
    indirect_object_id bigint N/A true   id of indirect object of action
    indirect_object_type int N/A true   type of indirect object of action
    metadata varchar 256 true   arbitrary metadata depending on the event
    user_id bigint N/A false   User that performed activity

    Back to the table list.

    jivedw_blogpost Table

    Blog post dimension table.

    Column Name Type Length Nullable Unicode Description
    blog_id bigint N/A false   The ID of the blog this blog post is contained within.
    blogpost_id bigint N/A false   The ID of the blog post. Primary key.
    creation_ts timestamp N/A false   Time record was created
    modification_ts timestamp N/A false   Time record was update
    subject varchar 255 true true The subject of the blog post.

    Back to the table list.

    jivedw_blogpost_stage Table

    Blog post dimension staging table.

    Column Name Type Length Nullable Unicode Description
    blog_id bigint N/A false   The ID of the blog this blog post is contained within.
    blogpost_id bigint N/A false   The ID of the blog post. Primary key.
    creation_ts timestamp N/A false   Time record was created
    modification_ts timestamp N/A false   Time record was update
    subject varchar 255 true true The subject of the blog post.

    Back to the table list.

    jivedw_container Table

    Container dimension

    Column Name Type Length Nullable Unicode Description
    container_id bigint N/A false   ID of container Primary key.
    container_type int N/A false   Type of container
    creation_ts timestamp N/A false   Time record was created
    dw_container_id bigint N/A false   Surrogate primary key of this record, unique to the datawarehouse Primary key.
    modification_ts timestamp N/A false   Time record was update
    name varchar 255 false true Name of container.

    Back to the table list.

    jivedw_container_stage Table

    Container dimension stage

    Column Name Type Length Nullable Unicode Description
    container_id bigint N/A false   ID of container
    container_type int N/A false   Type of container
    creation_ts timestamp N/A false   Time record was created
    modification_ts timestamp N/A false   Time record was update
    name varchar 255 false true Name of container.

    Back to the table list.

    jivedw_day Table

    Time dimension for Day granularity

    Column Name Type Length Nullable Unicode Description
    begin_ts timestamp N/A false   Timestamp indicating the beginning range of this time period
    day_id bigint N/A false   Surrogate primary key Primary key.
    day_of_month int N/A false   The day of the year
    end_ts timestamp N/A false   Timestamp indicating the end range of this time period
    month_of_year int N/A false   The mont of the year
    week_of_year int N/A false   The week of the year
    year int N/A false   The year

    Back to the table list.

    jivedw_document Table

    Document dimension table.

    Column Name Type Length Nullable Unicode Description
    container_id bigint N/A false   The ID of the container the document is within.
    container_type int N/A false   The type of the container the document is within.
    creation_ts timestamp N/A false   Time record was created
    document_id bigint N/A false   The internal ID of the document. Primary key.
    modification_ts timestamp N/A false   Time record was update
    title varchar 255 false true The document title.

    Back to the table list.

    jivedw_document_stage Table

    Document dimension staging table.

    Column Name Type Length Nullable Unicode Description
    container_id bigint N/A false   The ID of the container the document is within.
    container_type int N/A false   The type of the container the document is within.
    creation_ts timestamp N/A false   Time record was created
    document_id bigint N/A false   The internal ID of the document. Primary key.
    modification_ts timestamp N/A false   Time record was update
    title varchar 255 false true The document title.

    Back to the table list.

    jivedw_etl_job Table

    ETL Jobs

    Column Name Type Length Nullable Unicode Description
    end_ts timestamp N/A true   Time the job completed
    error_msg varchar 2000 true    
    etl_job_id bigint N/A false   Surrogate primary key Primary key.
    start_ts timestamp N/A false   Time the job was started
    state int N/A false   Status of the job

    Back to the table list.

    jivedw_etl_task Table

    ETL Tasks

    Column Name Type Length Nullable Unicode Description
    classname varchar 100 false   Name of the java class implementation
    etl_task_id bigint N/A false   Surrogate primary key Primary key.
    lastProcModificationDate bigint N/A true   Modification date of the last record processed
    name varchar 20 false   Name of the ETL task

    Back to the table list.

    jivedw_message Table

    Jive Message dimension

    Column Name Type Length Nullable Unicode Description
    container_id bigint N/A false   The ID of the container the document is within.
    container_type int N/A false   The type of the container the document is within.
    creation_ts timestamp N/A false   Time record was created
    message_id bigint N/A false   Message ID. Primary key.
    modification_ts timestamp N/A false   Time record was update
    parent_message_id bigint N/A true   Parent message ID.
    subject varchar 255 true true Subject of message.
    thread_id bigint N/A false   ID of thread to which message belongs.

    Back to the table list.

    jivedw_message_stage Table

    Jive Message dimension

    Column Name Type Length Nullable Unicode Description
    container_id bigint N/A false   The ID of the container the document is within.
    container_type int N/A false   The type of the container the document is within.
    creation_ts timestamp N/A false   Time record was created
    message_id bigint N/A false   Message ID.
    modification_ts timestamp N/A false   Time record was update
    parent_message_id bigint N/A true   Parent message ID.
    subject varchar 255 true true Subject of message.
    thread_id bigint N/A false   ID of thread to which message belongs.

    Back to the table list.

    jivedw_month Table

    Time dimension for Month granularity

    Column Name Type Length Nullable Unicode Description
    begin_ts timestamp N/A false   Timestamp indicating the beginning range of this time period
    end_ts timestamp N/A false   Timestamp indicating the end range of this time period
    month_id bigint N/A false   Surrogate primary key Primary key.
    month_of_year int N/A false   The mont of the year
    year int N/A false   The year

    Back to the table list.

    jivedw_object Table

    Object dimension table.

    Column Name Type Length Nullable Unicode Description
    creation_ts timestamp N/A false   Time record was created
    dw_object_id bigint N/A false   The ID of the blog post. Primary key.
    modification_ts timestamp N/A false   Time record was update
    name varchar 255 true true The name of the object
    object_id bigint N/A false   Jive SBS Object ID Primary key.
    object_type int N/A false   The type of the object

    Back to the table list.

    jivedw_profilefield Table

    Profile field data table.

    Column Name Type Length Nullable Unicode Description
    field_id bigint N/A false   The ID of the field. Primary key.
    name varchar 255 false true The name of the field.

    Back to the table list.

    jivedw_profilefield_stage Table

    Profile field data table.

    Column Name Type Length Nullable Unicode Description
    field_id bigint N/A false   The ID of the field. Primary key.
    name varchar 255 false true The name of the field.

    Back to the table list.

    jivedw_user Table

    User dimension

    Column Name Type Length Nullable Unicode Description
    creation_ts timestamp N/A false   Time record was created
    firstname varchar 100 true true First name
    initlogin_ts timestamp N/A true   Timestamp when the user first logged in
    lastname varchar 100 true true Last name
    modification_ts timestamp N/A false   Time record was update
    name varchar 100 true true Name of user
    user_id bigint N/A false   User id from Jive SBS Primary key.
    userenabled int N/A false   Whether the current user is enabled or not.
    username varchar 100 false true User name.

    Back to the table list.

    jivedw_user_stage Table

    User dimension stage

    Column Name Type Length Nullable Unicode Description
    creation_ts timestamp N/A false   Time record was created
    firstname varchar 100 true true First name
    lastname varchar 100 true true Last name
    modification_ts timestamp N/A false   Time record was update
    name varchar 100 true true Name of user
    user_id bigint N/A false   User id from Jive SBS Primary key.
    userenabled int N/A false   Whether the current user is enabled or not.
    username varchar 100 false true User name.

    Back to the table list.

    jivedw_userprofile Table

    User profile data table.

    Column Name Type Length Nullable Unicode Description
    field_id bigint N/A false   The ID of the field.
    primaryval smallint N/A false   Whether or not this value is the primary value in a list
    user_id bigint N/A false   The ID if the user this profile is associated with.
    value varchar 3500 true true The value set for the field.

    Back to the table list.

    jivedw_userprofile_stage Table

    User profile data stage table.

    Column Name Type Length Nullable Unicode Description
    field_id bigint N/A false   The ID of the field.
    primaryval smallint N/A false   Whether or not this value is the primary value in a list
    user_id bigint N/A false   The ID if the user this profile is associated with.
    value varchar 3500 true true The value set for the field.

    Back to the table list.

    jivedw_version Table

    Version data table.

    Column Name Type Length Nullable Unicode Description
    name varchar 50 false true The name of the version key. Primary key.
    version int N/A false   The latest version number this instance is up to.

    Back to the table list.

    jivedw_week Table

    Time dimension for Day granularity

    Column Name Type Length Nullable Unicode Description
    begin_ts timestamp N/A false   Timestamp indicating the beginning range of this time period
    end_ts timestamp N/A false   Timestamp indicating the end range of this time period
    week_id bigint N/A false   Surrogate primary key Primary key.
    week_of_year int N/A false   The week of the year
    year int N/A false   The year

    Back to the table list.