7 Replies Latest reply: Jun 7, 2010 9:00 AM by john.tarbutton RSS

    Jive Analytics - Business Objects - Customer Instance Tables

    john.tarbutton

      Hi All,

       

      (1) Can anyone provide me with some background and any details on how the following tables are created/used in the Jive BOBJ environment?  These tables are not part of the out of the box analytics environment and I am curious as to how they are being used and how they assist in the analytics process.

       

      For example, from sample join2 below, when joining between jivedw_object and jivedw_document I would assume that you have to join using document_id = object_id and object_type = 102 to avoid incorrect joins on object_id.  (2) But does the use of customer_instance_id in this case assist with not needing to do that?

       

      (3)

      jivedw_customer

       

      jivedw_customer_group

      jivedw_customer_instance

      jivedw_customer_instance_mapping

       

      I notice that on most of the table joins that a 'customer_instance_id' is used to join between dimension tables.  (4) What does that field represent and how is it populated?

       

      sample join1: jivedw_day.day_id=jivedw_activity_agg_day.day_id and jivedw_day.customer_instance_id=jivedw _activity_agg_day.customer_instance_id

       

      sample join2: jivedw_object.customer_instance_id=jivedw_document.customer_instance_id and jivedw_object.o bject_id=jivedw_document.document_id

       

      sample join3: jivedw_activity_agg_user_day.day_id=jivedw_day.day_id and jivedw_activity_agg_user_day.custo mer_instance_id=jivedw_day.customer_instance_id

       

       

       

       

       

      Thanks in advance,

       

      John

       

        • Re: Jive Analytics - Business Objects - Customer Instance Tables

          Hey John,

           

          We run a multi-tenant environment for our hosted Business Objects offering, so we have set of security tables that determine what data can be presented to different customers.  The tables you reference are used to make that determination - we populate them through the ETL process that brings over customer data.  For your purposes, I would say that you could ignore them and remove references to them from the joins.

           

          Best,

          dan

          • Re: Jive Analytics - Business Objects - Customer Instance Tables
            john.tarbutton

            Also, while we are on the subject - can you also explain the following three tables (i.e. what they contain and how they are used?):

             

            jivedw_activity_type

            jivedw_activity_type_user

            jivedw_object_type

             

            Thanks,

             

            John

              • Re: Jive Analytics - Business Objects - Customer Instance Tables

                jivedw_activity_type - this table lists the different activity types and their corresponding codes. The values in this table are same for all customers.

                jivedw_activity_type_user - this is an alias table of jivedw_activity_type in the BOBJ universe to join with 2 different fact tables.

                jivedw_object_type - this table lists the types of different objects in SBS. These values are customer specific in BOBJ analytics. The Object type IDs (object_type column in the table) is used to identify the type of object that the database row refers to.

                 

                Below are some table values in the tables - jivedw_activity_type and jivedw_object_type.

                 

                Hope this helps!

                Priyanka

                 

                 

                jivedw_activity_typejivedw_object_type
                Activity_TypeNameobject_typeNamecustomer_instance_idobject_type_id
                290ADDED 501CUSTOM OBJECT TYPE 1666
                130APPROVE0NO_VALUE_SUPPLIED1667
                200ASSOCIATE-1INVALID_VALUE_SUPPLIED1668
                60COPY-2SYSTEM_CONTAINER1669
                20CREATE1THREAD1670
                30DELETE2MESSAGE1671
                210DISSOCIATE3USER1672
                90EXPIRE4GROUP1673
                270FOLLOW5THREAD_NAME1674
                50HIDE6MESSAGE_SUBJECT1675
                -1INVALID_VALUE_SUPPLIED7MESSAGE_BODY1676
                160LOG_IN8CREATION_DATE1677
                161LOG_OUT9MODIFICATION_DATE1678
                110MODERATE10EXTENDED_PROPERTY1679
                40MODIFY11ANONYMOUS1680
                70MOVE12REGISTERED_USERS1681
                0NO_VALUE_SUPPLIED13ATTACHMENT1682
                150OFFLINE14COMMUNITY1683
                140ONLINE16COMMUNITY_NAME1684
                100RATE_OR_VOTE17SYSTEM1685
                220REGISTER18POLL1686
                240REJECT19COMMUNITY_SEARCH_QUERY1687
                260REMOVE_WATCH20PRIVATE_MESSAGE1688
                300RESOLVED21PRIVATE_MESSAGE_FOLDER1689
                170SEARCH_CONTENT22ANNOUNCEMENT1690
                180SEARCH_USERS23SEARCH1691
                120SEND24CRONTASK1692
                190SPOTLIGHT_SEARCH25STATUS_LEVEL1693
                80UNDELETE26AVATAR1694
                280UNFOLLOW27QUESTION1695
                230VALIDATE29COMMUNITY_QUESTION1696
                10VIEW30SYSTEM_QUESTION1697
                250WATCH31GATEWAY1698

                  • Re: Jive Analytics - Business Objects - Customer Instance Tables
                    john.tarbutton

                    Priyanka,

                     

                    Thank you for the great details and quick response!  Very helpful.  I have actually set something similar up in my environment to map the type ids to the text descriptions for Activity Types and Object Types - so that makes sense.

                     

                    I have a couple last questions I would like to try and sneak into this thread if you don't mind.

                     

                    1. You mentioned "jivedw_activity_type_user - this is an alias table of jivedw_activity_type in the BOBJ universe to join with 2 different fact tables"  From what I can tell, the jivedw_activity_type table is used to join to the jivedw_activity_agg_user_day table and jivedw_activity_type_user alias is used to join to the jivedw_activity_agg_day .. Correct?

                     

                    2.  I noticed that there is one hierarchy defined in the BOBJ environment for Container.  How is this Hierarchy setup and what type of drill down capabilities does it allow for?  I have setup a Community Container Hierarchy in my environment using the Community LFT and RGT values to traverse the Community Tree, but I don't really see how you could setup a general Container Hierarchy (all container types) with what comes from jive analytics out of the box in the jivedw_container table .. so I am curious how the BOBJ Container Hierarchy is setup and used (aka what drill down or other functionality it provides)?

                     

                    Thanks for all the help.

                     

                    John

                      • Re: Jive Analytics - Business Objects - Customer Instance Tables

                        1. You mentioned "jivedw_activity_type_user - this is an alias table of jivedw_activity_type in the BOBJ universe to join with 2 different fact tables"  From what I can tell, the jivedw_activity_type table is used to join to the jivedw_activity_agg_user_day table and jivedw_activity_type_user alias is used to join to the jivedw_activity_agg_day .. Correct?

                         

                        - That is correct

                         

                        2. There is a custom hierarchy in the universe called - container. Please refer to the screenshot attached for how the hierarchy is defined. As I see in the definition, it has container type and container name. This hierarchy allows you to find all the conatiners for each container type id and then I assume helps to find activities for a particular container type. So the drill path - Container_Type -> Container Name.

                         

                        Hope it helps!

                         

                        Container_TypeContainer Name
                        14Abbott Vascular
                        14ABN AMRO
                        14ABN AMRO Management Services, Ltd.
                        14Accenx Technologies, Inc.
                        14Accept Software
                        14Ace Mortgage, LLC
                        14Acme Corp.
                        14Acquity Group
                        14ActiveCampaign, Inc.
                        14Adaptive Planning
                        14Adaptive, Inc.
                        14Adobe Support Discussions Migration
                        6001.10.8 Upgrade Project
                        6002.0 Upgrade
                        6002.0 upgrade UI Fixes
                        6002.5 Upgrade
                        6002.5.9 Upgrade
                        6002008 September QBR
                        6002009 Enhancements
                        6002009 India Business Forum
                        6003.0 Upgrade
                        600365.rsaconference.com 2.5 Upgrade
                        600A-Space v1
                        600ACS Network