Connecting to the Pulse Databases through an ODBC driver

This article will guide the Pulse Administrator to configure ODBC connections to the Pulse Databases.

The reader of this article should be familiar with the Pulse terminology and should have a moderate knowledge of how to configure ODBC connections in a Windows PC or Server.

Introduction

Pulse stores historical data and documentation in two internal databases. If you would like to extract information from Pulse using SQL queries you can access information that is not available in the user interface. Accessing these databases can be achieved through an ODBC driver.

Both Pulse databases are stored in the db directory where Pulse is installed:

Monitor.db: A SQLite database that stores all of the monitoring information for the server and each TM1 instance.

docs.h2.db: A H2 database that stores all of the documentation, TM1Web usage and Excel usage.

NOTE: Monitor.db Locking

The Monitor.db is a SQLite database and can only be accessed by one process at a time. If you are quering the database or extracting information using a Turbo Integrator process you may lock the file, stopping Pulse writing to the file.  If you want to run extended queries you should copy the Monitor.db file to another location.

Configuring the ODBC connections

Before configuring the ODBC connections the Pg (Postgresql) interface must be enabled:

  1. Open conf\Pulse.cfg in the Pulse install directory with Notepad or your favourite text editor
  2. Find the database section of the config file
  3. Set PgStart setting to true as highlighted below
  4. If you want to be able to access the database from other computers enable PgRemoteAllowed by setting its value to true
  5. Save the file
  6. Restart the Pulse for TM1 Application Server service in the Services Control Panel

[Database]

ConsoleStart = false
ConsoleRemoteAllowed = false
ConsolePort = 8090
PgStart = true
PgRemoteAllowed = false
PgPort = 8091
TcpStart = false
TcpRemoteAllowed = false
TcpPort = 8092

ODBC Drivers

To access the databases via ODBC you need to download the following drivers drivers:

  1. SQLite
  2. Postgresql

DSN Setup Monitor

  1. Go to Administrative Tools -> Data Sources (ODBC)
  2. Click on the System DSN tab
  3. Click Add
  4. Select Sqlite3 ODBC Driver
  5. Click Finish
  6. Enter in the Data Source Name: Pulse Monitor
  7. Browse to the Monitor.db file (default location is C:\Program Files\Pulse for TM1\db\Monitor.db)
  8. Click on OK

 

DSN Setup Documentation

  1. Go to Administrative Tools -> Data Sources (ODBC)
  2. Click on the System DSN tab
  3. Click Add
  4. Select PostgreSQL Unicode(x64)
  5. Click Finish
  6. Enter in the Data Source Name: Pulse Docs
  7. Set the Database to docs;DATABASE_TO_UPPER=false;
  8. Set the Server to localhost or the name of the server
  9. Set Port to 8091
  10. Set User Name to reader
  11. Set Password, to retrieve the password, go to the Pulse Web Client and under Administration/Configuration, copy the password from the settings, as shown below

  12. Click on OK