HowToCreateKPIExternalDB

version 1.8.0 or higher

How to Create a KPI with data from external DB

When you configurate a KPI at Plandora (through the root user) the default behaviour is to get data from own Plandora data base. But, it is possible to get data from another data base server using JDBC.

The steps bellow shows how to create different KPIs with different data sources.

• First of all, copy at lib/ folder of tomcat server the JDBC drivers of data bases that you want to connect. In this example we will show KPIs connecting at Oracle, Postgres, MySQL and SQLServer.

• Perform the login using the root user and click at "KPIs Form (Key Performance Indicator)".

• The trick is to put a prefix of a JDBC directive at 'SQL' field. If no prefix was set, the system will try to execute the SQL statement at Plandora database. The prefix must follow the format:

    [ driver | URI | user | password ]
    select column from my_table_at_external_database

Where driver is the JDBC driver path of each data base supplier. The URI is the URI source where the data base is located, and the user and password are those used to access the data base.

Bellow there are some examples to access different DBs.

PostgreSQL

    [org.postgresql.Driver | jdbc:postgresql://192.168.0.1:5432/PSG_TEST | myuser | mypass ]
    select count(*) as c from my_postgres_table

MySQL

    [com.mysql.jdbc.Driver | jdbc:mysql://localhost:3306/sugarcrm | root | ]
    select count(*) as c from opportunities

SQLServer

    [com.microsoft.sqlserver.jdbc.SQLServerDriver | jdbc:sqlserver://localhost:1433;databaseName=MSSQL_TEST | sa | mypass ]
    select count(*) as c from my_sqlserver_table

Oracle

    [oracle.jdbc.OracleDriver | jdbc:oracle:thin:@192.168.0.1:1521:XE | myuser | mypass ]
    select count(*) c from my_oracle_table

• The result at KPI Viewer Form will be "transparent" to final user. This feature could be usefull to create a control board, that gather data from different systems at the same place.