Version 1

    This document captures the SQL behind the components of the admin dashbaord. (Thanks > MarkW).

    Cumulative Membership Trend & Cumulative Content Trend

    For cumulative membership we run this query:

     

    SELECT
        day.begin_ts,
        count(user_id)
    FROM
        jivedw_day day
    INNER JOIN
        jivedw_user u
        ON
        u.initlogin_ts BETWEEN day.begin_ts
       AND day.end_ts
    WHERE
        u.initlogin_ts IS NOT NULL
    GROUP BY
        day.begin_ts
    ORDER BY
        day.begin_ts DESC
    

     

    Each record in the result indicates the day and a count of the users who had logged in for the first time on that day.  In order to present a graph showing running total (day to date), the running totals are calculated in the application server by java code. However, there are techniques to do this on the database side.

     

    Initlogin is calculated in a prior step (but only once). It is calculated by looking in the activity fact table for the earliest timestamp for a LOGIN activity event.  It is performed in the UserInitLoginETL java class using this query:

     

    SELECT
        min(activity_ts),
        f.user_id
    FROM
        jivedw_activity_fact f
    INNER JOIN
        jivedw_user u
        ON
        u.user_id = f.user_id " + "
    WHERE
        u.initlogin_ts IS NULL
        AND activity_type = " + AnalyticsEventCode.LOG_IN.getId() + "
    GROUP BY
        f.user_id
    

     

    For customers using an SSO provider this LOGIN event will not be captured. We could therefore modify our this process to expand the search to include any type of view activity.

     

    For cumulative content we run this query:

     

    SELECT
        begin_ts,
        SUM(activity_count) as actsum
    FROM
        jivedw_activity_agg_day a
    INNER JOIN
        jivedw_day day
        ON
        a.day_id = day.day_id
    WHERE
        activity_type          = :activityType
        AND direct_object_type = :objectType
        AND day.begin_ts BETWEEN :beginTs
        AND :endTs
    GROUP BY
        day.begin_ts
    

     

    Each record in the result indicates the day and a count of all the activity for a particular activity code. In order to present a graph showing running totals (day to date), the running totals are calculated in the application server by java code. However, there are techniques to do this on the database side.

     

    Active Users and Contributors

     

    Active Users are determined by looking for any activity by a user. The count works this way:

     

    SELECT
        begin_ts,
        COUNT(DISTINCT user_id)
    FROM
        jivedw_activity_agg_day a
    INNER JOIN
        jivedw_dau day
        ON
        a.day_id = day.day_id
    WHERE
        day.begin_ts BETWEEN :beginTs
        AND :endTs
    GROUP BY
        day.begin_ts
    

     

    Contributors works the same way but only write activity is included.

     

    Top Container and Content Queries

     

    The calculation of top containers and top content is a two step process. The first step executes a query to aggregate the counts of activity for each container/content – the results of these queries are entered into a temporary table. The second step queries from this table using a different aggregate grouping, e.g. summing the top container by user or by activity.

     

    Here are the two queries that populate the temporary table:

     

    Top Container:

     

    INSERT
    INTO
        jivetmp_obj_cnt
        (
        SELECT
            id,
            cnt
        FROM
            (
            SELECT
                a.dw_container_id   as id,
                SUM(activity_count) as cnt
            FROM
                jivedw_activity_agg_day a
            INNER JOIN
                jivedw_day day
                ON
                day.day_id = a.day_id
            INNER JOIN
                jivedw_container c
                ON
                a.dw_container_id = c.dw_container_id
            WHERE
                day.begin_ts BETWEEN :beginTs
                AND :endTs " + "
                AND activity_type  = :activityType
                AND container_type = :containerType
            GROUP BY
                a.dw_container_id
            ORDER BY
                cnt DESC
            )
            sub)
    

     

    Top Content:

     

    INSERT
    INTO
        jivetmp_obj_cnt
        (
        SELECT
            id,
            cnt
        FROM
            (
            SELECT
                a.direct_dw_object_id as id,
                SUM(activity_count)   as cnt
            FROM
                jivedw_activity_agg_day a
            INNER JOIN
                jivedw_day day
                ON
                day.day_id = a.day_id
            INNER JOIN
                jivedw_object o
                ON
                a.direct_dw_object_id = o.dw_object_id
            WHERE
                day.begin_ts BETWEEN :beginTs
                AND :endTs " + "
                AND activity_type      = :activityType
                AND direct_object_type = :objectType
            GROUP BY
                a.direct_dw_object_id
            ORDER BY
                cnt DESC
            )
            sub )
    

     

    This temporary table can be queried to get different types of aggregate activity:

     

    E.g.  For top container by activity:

     

     

    SELECT
        id,
        SUM(activity_count)
    FROM
        jivedw_activity_agg_day a
    INNER JOIN
        jivetmp_obj_cnt tmp
        ON
        tmp.id = a.dw_container_id
    INNER JOIN
        jivedw_day day
        ON
        day.day_id = a.day_id
    WHERE
        day.begin_ts BETWEEN :beginTs
        AND :endTs
        AND activity_type = :activityType
    GROUP BY
        id
    

     

    For top content by activity:

     

    SELECT
        id,
        SUM(activity_count)
    FROM
        jivedw_activity_agg_day a
    INNER JOIN
        jivetmp_obj_cnt tmp
        ON
        tmp.id = a.direct_dw_object_id
    INNER JOIN
        jivedw_day day
        ON
        day.day_id = a.day_id
    WHERE
        day.begin_ts BETWEEN :beginTs
        AND :endTs
        AND activity_type = :activityType
    GROUP BY
        id
    

     

    For top content by user participation:

     

    SELECT
        id,
        COUNT(DISTINCT user_id)
    FROM
        jivedw_activity_agg_day a
    INNER JOIN
        jivetmp_obj_cnt tmp
        ON
        tmp.id = a.direct_dw_object_id
    INNER JOIN
        jivedw_day day
        ON
        day.day_id = a.day_id
    WHERE
        day.begin_ts BETWEEN :beginTs
        AND :endTs
        AND activity_type = :activityType
    GROUP BY
        id
    

     

    Rolling up activity to parent communities is not currently possible because we do not have the community hierarchy available.