Analytics Database Overview

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

Date/time values are stored as GMT -- in other words, the time zone is not stored. Applications using this data must make time zone conversions as an offset of GMT. Dates in the analytics database are stored using the timestamp data type of the DMBS.