You’ll use ODBC interfaces to connect to a SQL database. Use Visual KPI Server Manager to install and configure ODBC interfaces.
Using Visual KPI Server Manager to Set Up ODBC Interfaces
To use Visual KPI with ODBC interfaces, you need to specify the connection parameters for your database and modify the SQL Scripts to reference the actual field names for SampleData, LookUp, Value, and lupd. The ODBC interface installation, using Visual KPI Server Manager, allows you to make these changes as part of the installation. Should you need to modify the connection parameters after the interface has been installed, you can use Visual KPI Server Manager to make the changes.
If you want to test ODBC interfaces before you connect it to your live database, you can create and work with the SQL Demo database.
ODBC interfaces are made up of a Web service and two SQL scripts (GetCurrentData.sql and GetTrendData.sql). Each SQL script uses four fields, two of which are used in your KPIs:
- LookUp (this is the tag name)
- Value (this is the value associated with the tag name).
The other two fields are used to find the correct value:
- SampleData (the table name)
- lupd (the date/time the row was last updated)
These are generic field names.
How Visual KPI Reads Data with ODBC Interfaces
The ODBC interface has three queries (one is optional) that you’ll need to configure. When you install an ODBC interface, it will, by default, add an example query. You can edit this.
When displaying KPI values, the Visual KPI ODBC interface reads the tag name associated with a specific KPI column (such as Value, Target, Low, and so on) and then searches the SampleData table for a row that has the same name in the LookUp column. When the interface finds the correct row, it retrieves the value in the same row and returns that value to Visual KPI.
The query can be either inline SQL or a stored procedure. Parameters are passed into the query using index placeholders, such as ‘{0}’, ‘{1}’, ‘{8}’. Queries should all have the same number of custom input parameters (the ‘{0}’ and ‘{1}’) and the Historical Value has the {TIMESTAMP} and finally the Trend query has {STARTDATE} and {ENDDATE}.
Get Current Value Query
This query will retrieve the current value of what you are looking up. The query needs to return one row with two columns called Value and Date. Any other columns and rows will be ignored. Let’s say you want to select a KPI value named DownloadPIR for a given a provider, which we’ll call Identifier.
query:
[code lang=”sql”]
Select sum(DownloadPIR) as Value,
GetDate() as Date
from YOURTABLE
where Identifier = ‘{0}’
[/code]
In Visual KPI Designer, you would use the Identifier (example:ENP001005) as the Actual Value and whatever you called the ODBC Interface as the interface.
If you wanted to further parameterize this (example Beam) you would add the index placeholder.
query:
[code lang=”sql”]
Select sum(DownloadPIR) as Value,
GetDate() as Date
from YOURTABLE
where Identifier = ‘{0}’ and Beam = ‘{1}’
[/code]
In Visual KPI Designer, you would use a two-part Actual Value separated by a pipe: ENP001005|Beam2
When the query runs, Visual KPI substitutes the parameter placeholders in the query:
[code lang=”sql”]Select sum(DownloadPIR) as Value,
GetDate() as Date
from YOURTABLE
where Identifier = ‘ENP001005’ and Beam = ‘Beam2’[/code]
If this table or query has an actual timestamp column, you can also return that instead of GetDate. The key is to return one row, and two columns (Value and Date), and the logic should result in a “current value”.
Get Historical Value Query
Get Historical Value Query is very much like Get Current Value Query except there is one required parameter called {TIMESTAMP}. The Get Historical Value Query also needs to return one row with two columns (Value and Date). This query should return the Value at or before a given timestamp.
Example query:
[code lang=”sql”]Select sum(DownloadPIR) as Value,
SomeTimeColumn as Date
from YOURTABLE
where Identifier = ‘ENP001005’ and Beam = ‘Beam2’
and SomeTimeColumn <= {TIMESTAMP}[/code]
Get Trend Data Query
When displaying a KPI’s trend, the Visual KPI ODBC interface reads the tag name associated with a specific KPI column (such as Value, Target, Low, and so on) and then searches the SampleData table for each row that has the same name in the LookUp column and where the lupd (timestamp) falls within the correct time range. Time range is based on the current Trend Range in Visual KPI. When the interface has completed its search, it retrieves the Value and lupd (timestamp) in the same row and returns these to Visual KPI, which in turn, draws the trend graph.
If you are doing a sum, consider whether the sum is daily, hourly or weekly. The query will need to handle this logic given a start and end date. If you ask for a year of data, your query is summing on a daily basis and should return 365 values (one per day). You could create a parameter where you pass in Daily, Hourly, and Weekly and write the logic to handle this correctly in SQL. Then, the Actual in Visual KPI would be something like: ENP001005|Beam2|Daily or ENP001005|Beam2|Hourly
Using Stored Procedures
You can use stored procedures (sprocs) and reference them in Visual KPI Server Manager. You’ll need to return the same parameters for each query type, along with the named sproc.
query examples:
Get Current Value: exec MyCurrentValueSproc ‘{0}’
Get Historical Value: exec MyHistoricalValueSproc ‘{0}’, {TIMESTAMP}
Get Trend Data: exec MyTrendDataSproc ‘{0}’, {STARTDATE}, {ENDDATE}
Note: You can also use named parameters: exec MyCurrentValueSproc someparm=’{0}’, someotherparm=’{1}’
Aggregate Raw Data with ODBC
You can use ODBC Aggregation Settings with an ODBC interface to do some math on the returned data (a bucket of data), rather than delivering the raw data. Visual KPI will return the raw data that has been queried via the interface, and then aggregate the data, based on parameters you set, before returning the data to your Visual KPI site.
Learn more
Install and Configure ODBC Interface