19 Replies Latest reply on Feb 25, 2013 6:46 AM by RyanSe

    Using Postgres SQL with Jive Databases webinar

    mnevill

      I take it many people would be interested in a webinar that covers how to run some basic queries in Postgres on Jive DBs.  Is there anyone out there knowledgeable in this area that would be willing to help out some fellow Jive users?  If this is something you would like to see or can help facilitate please comment so we can gauge interest.  I think an appropriate audience would be people who have Postgres installed, have restored the DB from a .DMP file from Jive Cloud (both covered here in Bill Chamberlain's awesome document ), but need some guidance on how to run queries.

       

      Thanks,
      Matt

        • Re: Using Postgres SQL with Jive Databases webinar
          jemjanik
          • I know how to download the DB from the cloud
          • How to restore the DB from the downloaded file (I could show what you do & the end result... on my pc it takes hours)
          • I use the PostGres tools to query and dump the results to a csv file.
          • I can slowly start posting queries I've written too (which are not guaranteed to be right since, but they seem to be)
          • Re: Using Postgres SQL with Jive Databases webinar
            mnevill

            The tables and queries are where I am getting tripped up.  Since there is such a learning curve to BI on demand (when it works), I decided to go the Postgres route since I am going to have to invest some effort either way.  The new CMR has been amazing especially mixed with Google Analytics   The only pieces I am missing are some queries that should be fairly simple.  I know there has got to be some people out there that are pretty comfortable with Postgres who probably deal with it semi-often, and I was hoping some of us newbies could get a quick tutorial and Q&A with some of those experts.  I bet there are other folks out there at the same place as I am, and would jump on the opportunity to have a quick knowledge sharing pow wow with some other more experienced users.

              • Re: Using Postgres SQL with Jive Databases webinar
                jemjanik

                The queries I have figured out took alot of playing around originally.  My calendar is packed this week, but I've made a note to start sharing some of them in the group next week.

                • Re: Using Postgres SQL with Jive Databases webinar
                  RyanSe

                  Hi Matt,

                   

                  Yeah. I think there are a few of us hanging around that have a fairly solid understanding of how the databases are structured and pulling stuff out. I have well north of 100 different queries built out for different things. My peers and I are currently working to organize things a bit.

                   

                  Depending on the amount of interest (and current workload), I would love to help out with showing folks the ropes. To me the real power of knowing the databases is that you can make some really interesting connections that otherwise are not available in CMR, BOBJ or any "pre-built" type of reporting.

                   

                  Ryan

                • Re: Using Postgres SQL with Jive Databases webinar
                  mnevill

                  Here is my contribution: Using Postgres SQL queries and Excel to determine non-employee answers

                   

                  If anyone else can share anything they have come up with I know it would be appreciated by others.

                    • Re: Using Postgres SQL with Jive Databases webinar
                      chl_meg

                      Thanks, Matt,

                       

                      I think it would be great to start a library of SQL queries that could be shared so we're not all recreating the wheel individually.

                       

                      One problem we're currently trying to solve is finding a way to link back counts of replies at the user-level?

                       

                      Use case: We are building a participation report that includes user stats across many different variables (replies, correct answers given, helpful answers given, points accumulated).

                       

                      We have been told by Jive support that they are unaware of a way to get these counts at the user level (we have been able to get these numbers at the sub-forum level).

                       

                      Has anyone else tried to solve for this problem?

                       

                      Meg

                       

                      Jitendra Soni

                        • Re: Using Postgres SQL with Jive Databases webinar
                          allison.doherty

                          Hi Matt Nevill

                          I have a few queries to share... some as simple ... other a little more complex. Can we start a doc in this space?

                           

                          Hi Mor Avital,

                          Since you are a space admin... can you create a doc and give us rights to edit it?

                          • Re: Using Postgres SQL with Jive Databases webinar
                            RyanSe

                            chl_meg wrote:

                             

                            Thanks, Matt,

                             

                            I think it would be great to start a library of SQL queries that could be shared so we're not all recreating the wheel individually.

                             

                            One problem we're currently trying to solve is finding a way to link back counts of replies at the user-level?

                             

                            Use case: We are building a participation report that includes user stats across many different variables (replies, correct answers given, helpful answers given, points accumulated).

                             

                            We have been told by Jive support that they are unaware of a way to get these counts at the user level (we have been able to get these numbers at the sub-forum level).

                             

                            Has anyone else tried to solve for this problem?

                             

                            Meg

                             

                            Jitendra Soni

                             

                            Hi chl_meg:

                             

                            So when you say replies, are you looking for how many replies the person has to threads of other individuals or the more interesting "Of posts someone made how many replies have they received to said posts?"

                             

                            As far as the correct answers given, helpful answers given and points accumulated those are all pretty easy to get at. I can take a look in my query vault tomorrow and pull them out. There is on caveat, the data is based on questions that are currently in the system, if a question is deleted or archived to another space then it doesn't count against the original space.

                             

                            Ryan

                              • Re: Using Postgres SQL with Jive Databases webinar
                                chl_meg

                                Thanks, Ryan.

                                 

                                We are using Jive as a social support channel. So, it's actually more important for us to know how often someone provided replies to questions asked by others rather than how many responses someone rec'd on their post (i.e. are priority is the inverse of what you posted above).

                                 

                                Hope that makes sense. I know we've had other challenges with "replies" before b/c I think Jive is set up to see them as "objects" rather than "events". However, if you can count how many original posts someone made, I would think you should also be able to count how many replies they provided.

                                 

                                I actually think that I may have found another chain that provides a solution: Measuring customers helping other customers

                                 

                                I will pass this along to our team and, if it works, I'll let everyone here know.

                                 

                                Meg