installing postgres for jive dwh implementation

8/12/2019 Installing Postgres for Jive DWH Implementation

1/21

PostgreSQL Jive Analytics InstallationProcessfor Use with Jive Analytics

Database

This document walks a user through the process to install the PostgreSQL database server, related ODBC

driver, and how to restore the Jive Analytics database for use with an ODBC compliant tool or directly

from PostgreSQLs own tool.the QlikView reporting framework.

Contents

Install Postgres Software .............................................................................................................................. 2Create a Postgres Database ........................................................................................................................74

Restore Jive Analytics Database.................................................................................................................. 94

Install Postgres ODBC Driver .....................................................................................................................114

Configure Postgres ODBC Driver ...............................................................................................................154

Download the Analytics Database from the Jive Cloud ............................................................................174

Connect the PostgreSQL Database to Microsoft Access ...........................................................................184

Install Postgres Software .............................................................................................................................. 2

Create a Postgres Database .......................................................................................................................... 7

Restore Jive Analytics Database.................................................................................................................... 9

Install Postgres ODBC Driver ....................................................................................................................... 11

Configure Postgres ODBC Driver ................................................................................................................. 14

Download the Analytics Database from the Jive Cloud .............................................................................. 16

Connect the PostgreSQL Database to Microsoft Access ............................................................................. 17

Bill Chamberlain

Centric Consulting, LLC

[email protected]

Formatted:Default Paragraph Font,spelling and grammar







Formatted:Font: Bold

Formatted:Space After: 0 pt, Line single
mailto:[email protected]:[email protected]


2/21

Install Postgres Software

PostgreSQL is an open source database that is free to download and use on both an individual computer

as well as on a server. This document focuses on installing the individual user version.

http://www.postgresql.org/is the link to the official Postgres site. http://www.enterprisedb.com/products-services-training/pgdownload#windowsis the link to

the download options which include Linux, Windows (32 and 64 bit) and Mac OS X.

For a Jive 5.x implementation, Postgres 9.1.x is required. This version will also work for Jive 4.5.x

implementations.

To install the software, simply follow these steps:

1. Close all Microsoft Applications (Outlook, Excel, Word, etc.) running on the computer2. Choose the version of the software you want to download. (via the PostgreSQL link above)3. Once the file has downloaded (e.g. postgresql-9.1.4-1-windows.exe), double click the file to

begin the installation process and choose Run
http://www.postgresql.org/http://www.postgresql.org/http://www.enterprisedb.com/products-services-training/pgdownload#windowshttp://www.enterprisedb.com/products-services-training/pgdownload#windowshttp://www.enterprisedb.com/products-services-training/pgdownload#windowshttp://www.postgresql.org/


3/21

4. Begin the PostgreSQL installation

5. Use the default directory for the installation and click Next.


4/21

6. Use the default directory for the data and click Next.

7. Setup a password for the account and click Next.


5/21

8. Input the port to listen on. Use 5432, but you can also use 5433 in most cases.

9. Use the default locale.


6/21

10.Begin the installation process by clicking Next.

11.When the installation has finished, the StackBuilder selection will be selected by default.Uncheck it (no need to install this) and click Finish.

12.Restart your system upon completion.


7/21

Create a Postgres Database

1. Start the PostgreSQL database by selecting pgAdmin III from the Windows Programs menu.

2. Double click the PostgreSQL 9.1 selection to start the database server and enter the passwordyou setup. If you receive an error about the server not listening, restart your computer and try

again.

3. Next, you want to create your database. Right click Databases and select New Database


8/21

4. You can name the database anything. For this example, use JiveAnalytics and click OK.Choosepostgres as the owner. It will take 1-2 minutes to create the new database.


9/21

Restore Jive Analytics Database

1. To restore the database (.dmp) file that was downloaded from the Jive Cloud (see steps for thislater in the document), right click on the JiveAnalytics database and choose Restore.

2. Choose the directory and the .dmp file you would like to restore, the role name of postgresand click Restore. This process will take some time and is dependent on the amount of data

being restored along with the type of computer. (assume 10-15 minutes for a 100 MB .dmp

file)


10/21

3. When complete, you will see a screen similar to this. Press Cancel at this point. (Pressing Restorewill start the Restore process again!)

4. The database is now fully restored and can be queried directly via the PostgreSQL tools or viaother tools using an ODBC connection.


11/21

Install Postgres ODBC Driver

1. Download the PostgreSQL ODBC driver from this link. You will want to use the most recentdriver. (psglodbc_09_01_0100-1.zip as of July 2012)

http://www.postgresql.org/ftp/odbc/versions/msi/

2. Double click the .msi file in the zip file that was downloaded.

3. Click Run to install the driver.
http://www.postgresql.org/ftp/odbc/versions/msi/http://www.postgresql.org/ftp/odbc/versions/msi/http://www.postgresql.org/ftp/odbc/versions/msi/


12/21


13/21

6. Click Next

7. Click Install.


14/21

8. Click Finish.


15/21

Configure Postgres ODBC Driver

1. From a Windows machine, click Start > Control Panel2. Click Administrative Tools.3. Click Data Sources (ODBC)4. Click System DSN and click Add.

5. Choose the PostgreSQL Unicode driver and click Finish.


16/21

6. Enter the following information:a. Data Source name (any description)b. Description (any description)c. Database (the database you previously created)d. server (localhost)e. User Name (postgres)f. server password (previously created).g. Leave the port at 5432 and the SSL mode at disable.h. Click Test to see if the connection works. (You should receive a Connection Successful

message)

7. Click Save and you will see your new Data Source. You can have multiple databases and datasources on a single machine.

8. Click Ok to close out the screen.You computer is now ready to connect toQlikView orany other application that connects via an ODBC

data source.


17/21

Download the Analytics Database from the Jive Cloud

1. Login to the Jive Cloud

2. Choose you correct Jive instance (top of the screen) and select Downloads.

3. Click Download for the database that you want to download.


18/21

Connect the PostgreSQL Database to Microsoft Access

1. Create a MS Access Database.

2. Click the External Data tab > More > ODBC Database


19/21

3. Click the Link option and choose OK.

4. Select the data source to which you want to connect and click OK.


20/21

5. Click Select All and OK to select all the tables.

6. In Access, you will be prompted for the Unique Identifier. Click Cancel(or control + break) to exitout of this to skip this. (unless you want to identify the unique identifier for each table).


21/21

7. You will now have access to all tables in the system directly from MS Access and can access thedata in any table by clicking on the table name.

installing postgres for jive dwh implementation

Documents