29 Replies Latest reply on Oct 9, 2014 7:24 AM by josh.richau

    Jive analytics database vs new API interface

    mnevill

      gummi

       

      I heard that eventually Jive was planning to move to the new API analytics interface available in Jive 7 and remove customer access from the existing Jive analytics database.  I wanted to know if that was true and if so express some concern.  There are several of us that have direct access to the EAE, System/Core, and Analytics databases via the Jive cloud portal and use all three databases often for custom reporting.  I will be honest that I have not invested much time to see what is accessible via the APIs, but wanted to confirm that we would have access to everything that is in the current analytics database today over the APIs.  If some information will not accessible, can we get a list of what specifically won't be there?

       

      Also, I remember hearing something about "one big CSV export" that would be available somehow.  Can you let us know what specific information that would include and how it would be accessed?  Is that the same as the API analytics interface, or something different all together?

       

      Sorry if I got some of this confused.  As we are planning to upgrade soon I wanted to make sure I had my ducks in a row and that all of the things I report on today will be available in the long term.

       

      As a side note: can the Jive Analytics User Group Group and this Jive Analytics space be merged?  There is relevant information scattered across both places, and it would be easier to just have one official place to go to for all things analytics.

       

      Thanks,
      Matt

        • Re: Jive analytics database vs new API interface
          DustinSmth

          I'm no where near an authoritative source on this, however this almost exact question was asked in the analytics breakout session at JiveWorld last week and josh.richau stated that regarding the analytics DB, it definitely doesn't disappear when the API shows ups.  If I understood correctly, the plan was to run both side by side while people had opportunities to compare what they had set up against the analytics DB against what they could run from the API.  Overtime it sounds like Jive would like to phase out the Analytics DB, but only once the API is able to provide the data needed to support data hungry customers.

           

          Overall I got the sense that any and all changes were going to be communicated well in advance of anything happening and that the Jive Analytics community would be well consulted.

           

          Again - definitely not the source of truth on this one, just reporting the conversation I was apart of. Don't change your analytics strategies based on my words.

           

          Best,

          Dustin

            • Re: Jive analytics database vs new API interface

              Thanks Dustin - your words are correct.

               

              Generally we expect the new Data Export API (built upon the analytics service that is coming with Fall Cloud and Jive 7) to have everything that the analytics DB has. But until we are certain of that - we intend to run them side by side and do comparisons...

               

              The Data Export API is also the "one big CSV" you heard about - you can get data from it in JSON or CSV format and it contains the info in the analytics DB in a denormalized by row fashion. More docs and info coming as we roll out Fall Cloud and Jive 7

              1 person found this helpful
            • Re: Jive analytics database vs new API interface
              mnevill

              Thanks for the confirmation and quick response.  I missed the analytics breakout session so sorry if it was covered there.  I am glad to hear there will be a feedback process while these are in parallel to make sure everyone has a chance to communicate their needs. 

               

              Can you also confirm that even though the analytics database will eventually go away, we will continue to have access to the EAE and system/core databases?

                • Re: Jive analytics database vs new API interface

                  Thanks Matt Nevill for posting the question and Dustin Smith for answering!

                  • Re: Jive analytics database vs new API interface

                    Matt - accessing the core DB and EAE DB is a matter of hosting allowing it. There is no plan to remove those or your access any time soon - but I would love to understand how we reduce your needs there and get you what you need to know via API rather than DB downloads.

                    1 person found this helpful
                    • Re: Jive analytics database vs new API interface
                      bchamberlain

                      I agree with the comments in this thread. We've worked with clients that have 30M+ rows of data in their activity table. Accessing this data in the Postgres Analytics database (via SQL Queries, Tableau, QlikView, or other ODBC tools) is a snap. The questions that I want to explore once the APIs are available include:

                       

                      • How does the API access work? Will I need to get a developer involved to write some level of code to pull the data?
                      • How will the APIs scale if I want to pull lots of data? (i.e. Jem Janik's question)
                      • My understanding is that by using the APIs, we'll be able to access incremental data refreshes. We didn't talk about "how" this will work at JW13, but I assume it means I will need to pull the data locally into a database on the client end. (e.g.Postgres, SQL Server, Oracle, etc.)
                      • A large denormalized .csv file concerns me from a couple perspectives:
                        • This file will be a subset of what's available in the Analytics database today. My assumption is it will be the Activity table with some additional columns included. Ultimately, I am going to lose access to some data that I have today.
                        • How will this scale? If I am pulling millions of rows of data down in a .csv file, I need to put in a database.
                    • Re: Jive analytics database vs new API interface

                      All excellent concerns. Let me address them:

                       

                      From Jem, Caton: concern about CSV size and web timeouts

                       

                      The Data Export API is a standalone service that has been designed to provide data - so as far as timeouts are concerned there should not be a problem. We do have a row limit to each request of 50k rows - so you may have to make multiple requests to get all of the data you need.

                       

                      From Bill:

                      • How does the API access work? Will I need to get a developer involved to write some level of code to pull the data?

                      It is meant to be easy, but depending on what you want to do with the data you may have to do some scripting. The main idea is that you get a clientID and secret from the Jive app, then hit the API to get a temporary key - then use the key in REST based calls. I have been playing with it myself and simply using curl to get CSV files

                      • How will the APIs scale if I want to pull lots of data? (i.e. Jem Janik's question)

                      This is where some amount of scripting may be necessary (but also where providing example scripts will be easy). Basically you pull 50k rows at a time. There should not be any scale issues but you will have to decide what to do with the data you pull - possibly putting it in a DB or other data warehouse style technology

                      • My understanding is that by using the APIs, we'll be able to access incremental data refreshes. We didn't talk about "how" this will work at JW13, but I assume it means I will need to pull the data locally into a database on the client end. (e.g.Postgres, SQL Server, Oracle, etc.)

                      Yes - the API will allow you specify a from and to time stamp - so you will be able to say get all the data in the first step, then get a week or days worth in the next incrementally. This is one of the biggest and best differences between today's entire analytics DB download and tomorrows - get historical data once and then download incrementals after.

                      • A large denormalized .csv file concerns me from a couple perspectives:
                        • This file will be a subset of what's available in the Analytics database today. My assumption is it will be the Activity table with some additional columns included. Ultimately, I am going to lose access to some data that I have today.

                      We are endeavoring to make the denormalized rows complete - but we will be running the analytics DB alongside the service for a period of time as we figure out if there are elements in the analytics DB that are needed that we are not providing.

                        • How will this scale? If I am pulling millions of rows of data down in a .csv file, I need to put in a database.

                      You may need to put it in a db or potentially import it directly into a BI tool.

                       

                      However, one other major point is that in addition to before and after time elements - the API will allow you to specify the types of activity you want and the specific meta data you want - so you may be able to more easily get just the set of data you need for a specific report rather than having to get an entire copy of everything. We did our best to think of these concerns as we built but as I mentioned we will be running the two side by side for a period to make sure we get it right - and since the Data Export API is from the analytics service our ability to iterate it based on your feedback will be possible.

                       

                      I know we need to get more docs and examples out to you as well - I am working on it!

                      1 person found this helpful
                        • Re: Jive analytics database vs new API interface
                          Caton Guilbault

                          Thanks Josh!

                           

                          I don't want to speak for everyone but I greatly appreciate your candor in answering these questions.

                          • Re: Re: Jive analytics database vs new API interface
                            DustinSmth

                            Echoing Caton and Ted's notes of appreciation.  This is going to be a game changer for me as well as other folks at Tableau and having all this really candid info. is fantastic.

                             

                            DATA FOR EVERYONE!

                            • Re: Jive analytics database vs new API interface
                              bchamberlain

                              Hi josh.richau -

                               

                              Our instance was upgraded over the weekend and one of our developers jumped into things this morning to review the new analytics service and data export API. We were able to get data from both the existing REST and new Analytics APIs. Here's our quick reaction and a follow up question.

                               

                              • It appears the Analytics API is, is basic relative to the REST API.  While the analytics API provides good information about the activities, it lacks a lot of the dimensional information we use in our model.
                              • The Rest API has all the data we currently have and our thinking is it might be easier to use the Rest API to access data. (We had not done anything with the Rest API in the past, so this was new to us; maybe not new to others.) We are going to explore this a bit further.

                               

                              One question -- we assume the REST API is going to remain. Correct assumption?

                               

                              Bill

                                • Re: Jive analytics database vs new API interface

                                  Bill,

                                  We definitely have some more work to do in the Data Export API to make it as usable as possible so please keep the feedback coming.

                                   

                                  When you say *the* REST API I assume you mean Jive's V3 API for accessing people, places, and content. Those API's are used by our mobile team, our office and outlook team, etc so they will absolutely be staying around.

                                   

                                  For performance reasons I would like to include as much useful data in the actual export - so again please let me know what you expect that you don't get. That said, you are right that you can use the REST API to expand on the objects you get from the Data Export...

                                    • Re: Jive analytics database vs new API interface
                                      bchamberlain

                                      Thanks for the quick response!

                                       

                                      You are correct - the V3 APIs.We will keep you posted as we continue to explore this.

                                      • Re: Jive analytics database vs new API interface
                                        jieying.chen@analog.com

                                        Josh,

                                        I"m trying to count the number of new registrations by month, do you know how to get this done by analytics or REST API?

                                        Thanks!


                                          • Re: Jive analytics database vs new API interface
                                            Caton Guilbault

                                            Hi Jane,

                                             

                                            You can use the query below to pull that information from the System database. If you need to use the Analytics DB you could modify to it to pull from the jive_dwuser table. If you need to use the API then search for ACTIVITY_CREATE_USER as the action but I wouldn't recommended using the API for historical reporting. 

                                             

                                            SELECT

                                              userid,

                                              username,

                                              firstname,

                                              lastname,

                                              email,

                                              federated,

                                              to_timestamp(creationdate/1000) as "CreationDate",

                                              to_timestamp(modificationdate/1000) as "ModificationDate",

                                              to_timestamp(lastloggedin/1000) as "LastLoggedinDate",

                                              to_timestamp(lastprofileupdate/1000) as "LastProfileUpdateDate",

                                              date_part('year',to_timestamp(creationdate/1000)) as "CreateYear",

                                              date_part('month',to_timestamp(creationdate/1000)) as "CreateMonth",

                                              date_part('day',to_timestamp(creationdate/1000)) as "CreateDay",

                                              date_part('week',to_timestamp(creationdate/1000)) as "CreateWeek",

                                             

                                            Case (userenabled)

                                                    When 1 Then 'Y'

                                                    Else 'N'

                                            End

                                             

                                            FROM

                                            Jiveuser

                                             

                                            Order by userid asc

                                              • Re: Jive analytics database vs new API interface

                                                jivedw_user is synced from jiveuser nightly and has very similar info.

                                                 

                                                For "registrations" you need to decide if you care about users created or users logged in - below reflects the initial login time which is how I tend to think about registrations.

                                                 

                                                SELECT

                                                  user_id,

                                                  username,

                                                  firstname,

                                                  lastname,

                                                  email,

                                                  creation_ts as "CreationDate",

                                                modification_ts as "ModificationDate",

                                                  last_logged_in as "LastLoggedinDate",

                                                  initlogin_ts as "InitialLoginDate",

                                                  date_part('year',initlogin_ts) as "InitialLoginYear",

                                                  date_part('month',initlogin_ts) as "InitialLoginMonth",

                                                  date_part('day',initlogin_ts) as "InitialLoginDay",

                                                  date_part('week',initlogin_ts) as "InitialLoginWeek",

                                                Case (userenabled)

                                                        When 1 Then 'Y'

                                                        Else 'N'

                                                End

                                                FROM

                                                jivedw_user

                                                Order by user_id asc

                                      • Re: Jive analytics database vs new API interface
                                        elizacolon

                                        Hi. I'm a newbie to this so pardon my naive question. But what kind of reports are you able to obtain from the EAE and system/core databases that you don't get from the analytics database? I'm in financial services and need to create reports on usage in particular spaces and groups for compliance and I thoughts I would get this from the analytics database. Greatly appreciated any insights. Thanks