Connect to a database

Info-Explorer needs a connection to the database to read and refresh data.

Info-Explorer supports the following connections: Microsoft SQL Server, IBM DB2, Oracle, Pervasive.SQL and ODBC

 

To configure an existing cube to a database, you need to update the connection details, save the cube and refresh the data.

Select Properties on the Home Ribbon: 

  • Click on the appropriate connection

  • Amend the connection details.

  • The details to provide depend on the database type.

Database Type = Microsoft SQL Server

This is used for Sage 300 data, Sage CRM data or other Microsoft SQL Server databases.

  • Database Type = Microsoft SQL Server

  • Database User ID - It is recommended to create a dedicated user in the database with the minimum security rights required to read data in the appropriate databases.

  • Database Password

  • Server / Datasource

  • Database - Name of the SQL Server database

Note: When using Sage 300, the Database name can be different from the Sage Database ID. The user name and password are not the details you use to login to Sage 300 application. They are details to connect to the SQL database.

Example:

Using Sage 300

Database Type = ODBC

This is used for Spire database.

Important!

As Info-Explorer is a 64-bit application, you need to download and install the 64-bit ODBC driver for the database you use on machines used to refresh the cube.

  1. Create an ODBC Connection using the 64-bit ODBC driver. Enter details of a database user that has rights to read the database on the server/machine where you need to connect to the database and refresh the data.

  2. In Info-Explorer > Properties > Connections, Select Database Type = ODBC

  3. Enter the ODBC connection name

  4. You can also enter the full details in the connection string. In most cases, the connection string includes the DSN Name, the HOST, DB, UID, PWD and PORT Number.

Spire ODBC Detailed steps

Step 1. Prerequisite to setup Spire ODBC

You need a 64-bit ODBC connection to the Spire database to use Info-Explorer. You can use an existing connection or create a new one.

In most cases, you need to add some lines to the Spire pg_hba file on the Spire server as follows:

File: "C:\ProgramData\Spire\PostgreSQL\14\pg_hba.conf"

Copy

Spire pg_hba file

host   all         spireapp           fe80::/64         md5
host   all         spireapp           samenet           md5

The ODBC Credentials can be found in the spire.ini file in C:\ProgramData\Spire for spireapp

Note: Contact your Spire partner for assistance with setting up the ODBC connection.

Step 2. Install 64-bit PostgreSQL ODBC Driver

As Info-Explorer is a 64-bit application, you need to download and install the 64-bit ODBC driver on machines used to refresh the cube.

Refer to PostgreSQL site to download the ODBC driver. https://www.postgresql.org/ftp/odbc/versions/msi/

Step 3. Create an ODBC Connection to the PostgreSQL database

: This name will be used in Info-Explorer

: Enter the Database Name that you want to analyse with Info-Explorer

: Enter a database user that has rights to read the database.

Tip: You can use the credentials found in the spire.ini file in the c:\ProgramData\Spire folder on spireapp mentioned above or create a dedicated user with sufficient rights to the database.

Step 4. Update Connection Details in Info-Explorer

: ODBC

Option 1: Use ODBC Connection

: If you have setup an ODBC connection, enter DSN=ConnectionName in Info-Explorer > Properties > Connection

In the example below, the DSN is called PostgreSQL64

Option 2: Use full connection string

Alternatively, you can enter the full Connection String with the following details

DSN=PostgreSQL30;HOST=localhost;DB=inspire+2022;UID=user;
PWD=passwd;PORT=5432;

Step 5. Test the Connection

Use the Test Connection button to ensure the connection is successful.

Troubleshooting the connection

If you get an ODBC error 22P05, error: character with byte sequence in encoding UTF8 has no equivalent in encoding WIN1252, change the driver from "PostgreSQL ANSI(x64)" to "PostgreSQL Unicode(x64)", then restart Info-Explorer and check if it works ok.

Step 6. Save the cube

Save the Cube and Refresh the Data.

Noteworthy

  • Use "test connection" to verify your details.

  • The connection details are used when refreshing the data. If you send an existing cube to a user who doesn't have access to the SQL Database or the ODBC Connection, they can still analyse the data in the cube, but they can't refresh the data.

  • It is recommended to save the cube after updating the database properties to save the database settings. Refer to Save a cube

Refresh data

Then click "refresh" to update the cube's data. Refer to Refresh cube manually

See also

Working with Info-Explorer - Home Tab