Connecting to a Web Service

You can source metric and report data from any web service that returns information using a JSON message format that conforms to Metric Insights Query Response format.

Architecture Overview

Architecture Overview

The diagram above illustrates graphically the Metric Insights (MI) Web Services architecture and high-level process flow; the numbering on the chart corresponds to the list below:

  1. The  Web Service URL and parameter data its obtained from the MI database; if required for the call, Web Service credential data (username and password) is also retrieved and decrypted, if necessary
  2. An HTTP POST request is made using the parameters described in the 'Web Service POST Data' section below and uses the unencrypted credentials (that were retrieved in Step 1) to authenticate to the Web Service, using HTTPS, if requested
  3. The Web Service performs a data fetch process as defined by the developer of that service
  4. The Web Service returns the results from the data fetch process to Metric Insights in JSON format as described in the 'Data Returned from Web Service' section
  5. MI parses JSON data and updates its database with the returned Metric data or Report instance data
  6. Data received from the Web Service is used by MI to generate new/updated tables and charts according to the parameters defined for the Metric or Report associated with the Web Service call

1. Adding a New Authentication

If you need to establish the the values for the 'Authentication' field on an Element's Editor, you use   to the right of the field's text display. There you add the URL for the source of your data. The authentication process associates a Username and password with the URL as it constructs the web service call.

2. Using Web Service POST Data

The system collects and passes the following information to the Web Service when performing the data fetch command:

2.1. Web Service URL

The Web Service URL is provided by the Administrator via the Report or Metric Editor and is used by the system to determine both the location of the Web Service and whether the fetch is to be performed using HTTPS or HTTP. In addition to any information specified directly in the URL, the table below contains items that are automatically appended to the list of HTTP POST parameters as determined by the type of element:

2.2. Information Added to POST

Information Added to POST

The table above illustrates the information that is appended to the POST that is performed to the Web Service URL

2.3. Dimension Values

As shown in the table in the preceding section, the  dimension_name information is passed to the Web Service URL for Metrics and Reports that are dimensioned.

2.4. Dimension Values

A separate Web Service call is performed for every 'dimension Value' defined for the 'dimension'. Each individual 'Key Value' of that dimension is passed in the dimension_value parameter. This value may either be an integer or text, depending upon the definition of the dimension.

2.5. Date Formats from Metrics and Reports

The  POSTed parameters shown above are re-formatted prior to substitution, based on the date format mask specified in the Web Service credentials associated with the Insight Element:

2.6. Date Formats if the 'MySQL to Web Service Format' is not specified

3. Authentication Credentials

If Username and/or password data is present for the credentials associated with the Insight Element, these parameters are passed to the web service using HTTP Basic authentication.

NOTE: It is important that an Element's developer understand that authentication credential information that the Web Service requires in order to perform the data fetch, including Usernames and passwords to other external services, must be managed by the Web Service since this information is not stored or processed by Metric Insights. Authentication and data collection processing performed by the Web Service occurs outside of this system.

4. Web Service Processing

The Web Service performs whatever processing is required to collect data for the Metric or Report. Data can be sourced by the Web Service from internal corporate information resources as well as from external systems hosted outside the corporate firewall. The Web Service is triggered by Metric Insights based on the execution of the Data Collection Trigger associated with the  Element as well as when the Administrator performs the Web Service call through the Editor.

5. Data Returned from Web Service for Metrics

5.1. Data Returned for a Metric

Metric Insights performs the following validation on the returned Metric data set before processing the data to ensure that:

  • The Web Service always returns data sets that include one numeric (type of “integer” or “decimal” ) and one date/time value (type of “date”) per row
  • If no date/time format is specified in the Web Service credentials for the Metric, all date/time column values conform to the date format of

          “YYYY-MM-DD HH24:MI:SS”

  • If a date/time format is provided as part of the Web Service credentials for the Metric, all date/time column values conform to the specified format mask

5.2. Metric Example

1.  If a Web Service uses the following SQL statement to generate a dataset:

Select measurement_time 'Calendar Date', sum(measurement_value) ‘Total Sales’

The Result Set contains the following 2 rows:

[‘2011-01-01’,1234.10] ,

The returned JSON would be:

{"header":[{"name":"Calendar Date","type":"DATE"}, {"name":"Total Sales","type":"DECIMAL"}],
“data”: [
[“2011-01-01 00:00:00”,1234.10]
[“2011-01-02 00:00:00”,5678.00]

The example above has been arranged for readability but a Web Service JSON result set does not have to be formatted and would typically look like the following example:

{"header":[{"name":"Calendar Date","type":"DATE"},{"name":"Total Sales","type":"DECIMAL"}], "data":[["2011-05-10 07:00:00",4730661.59],["2011-05-11 07:00:00",4602004.14],["2011-05-12 07:00:00",4635604.11],["2011-05-13 07:00:00",4873962.11],["2011-05-14 07:00:00",4614745.529999999],["2011-05-15 07:00:00",4699752.8],["2011-05-16 07:00:00",4774199.8],["2011-05-17 07:00:00",4793545.17],["2011-05-18 07:00:00",4529600.81],["2011-05-19 07:00:00",4605180.539999999]]}

5.3. Returned Metric Data Validation

Metric Insights performs the following validation on the returned Metric data set before processing the data to ensure that:

  • The Web Service always returns data sets that include one numeric (type of “integer” or “decimal” ) and one date/time value (type of “date”) per row
  • If no date/time format is specified in the Web Service credentials for the Metric, all date/time column values conform to the date format of
         YYYY-MM-DD HH24:MI:SS
  • If a date/time format is provided as part of the Web Service credentials for the Metric, all date/time column values conform to the specified format mask

6. Data Returned from Web Service for Reports

Web Services that populate Reports must return the three JSON elements listed below:

  1. Header Column Names
  2. Column data types (“DATE”,”DECIMAL”,”INTEGER”,”TEXT”) given that the “DATE” data type is used for date-time values as well as date-only values
  3. Data Set values for each row

6.1. Expected JSON structure for a result set with four columns and three rows

"header": [
"type": "DATE",
"name": "Order Date"
"type": "DECIMAL",
"name": "US order Volume (US$)"
"type": "DECIMAL",
"name": "Intl order Volume (US$)"
"type": "DECIMAL",
"name": "Total order Volume (US$)"
"data": [
"2011-04-06 00:00:00",
"2011-04-05 00:00:00",

7. Error Reporting

Any error encountered in Web Service processing are returned to Metric Insights so that the Administrator can be notified of any problems. The following information must be included in the JSON message (for both metric and report data fetches) when an error is encountered:

“error”: ‘’| ‘error string’

7.1. Returned Report Data Validation

Any errors encountered in Web Service processing are returned to Metric Insights so that the Administrator can be advised of any problems. The information that must be included in the JSON message (for both metric and report data fetches) when an error is encountered is shown above.

Metric Insights logs (or displays in the Editor during validation) both error messages explicitly returned in the JSON message as well as standard HTTP header error codes; e.g., 404,503,505. For a data fetch to be considered successful all of the following conditions must be satisfied:

  • The web service call returns a ‘OK’ HTTP status
  • No “error” section exists in the returned JSON
  • The returned JSON format adheres to the provided JSON specification
  • All validation rules set for the Metric's or Report's data are satisfied

8. Remotely Invoking Web Service Calls

Feature coming soon!

9. Sample Code

The following sub-steps contain examples of Web Services for consumption of Metric and Report data written in php and python:

9.1. PHP Web Service example for collecting metrics data. Note the use of last_measurement_time.

Above is a Php 
* @see Devx_Model
require_once 'Devx/Model.php';
* DemoModel object
* @version 1.0
* @package Insight
class DemoModel extends Devx_Model
public function getDemoData1($subst) {
$cfg = Zend_Registry::get('config');
$password = Custom_Model_External::decryptPassword($cfg->database->password);
$params = array('host'     => $cfg->database->host,
'username' => $cfg->database->username,
'password' => $password,
'dbname'   => 'demo',
'port'     => 3306 );
try {
$db = new Devx_Db_Adapter_Pdo_Mysql($params);
$db->query('SET NAMES "utf8"');
$sql = "select sum(amount), date(order_time)
from customer_order ord, customer_order_detail ord_line
where ord.order_id = ord_line.order_id
and ord.order_time > :last_measurement_time
and date(ord.order_time) < now()
group by 2";
if (!isset($subst['last_measurement_time'])) return array('error' => 'No substitution provided for \':last_measurement_time\' pattern');
$pattern = ':last_measurement_time';
$sql = trim(str_ireplace($pattern, "'" . $subst['last_measurement_time'] . "'", $sql));
$rows = $db->query($sql)->fetchAll();
if (is_array($rows)) {
return array(
'header' => array(
array('name' => 'sum(amount)', 'type' => 'decimal'),
array('name' => 'date(order_time)', 'type' => 'date')
'data' => $rows
} else return array('error' => 'Unexpected data structure is returned');
} catch (Exception $ex) {
return array('error' => $ex->getMessage());

9.2. Python code example used to collect report data using a Web Service”

#!/usr/bin/env python2.5
Requirements: apache, mod_python, python2.5, MySQLdb
Sample of virtual host file:
<Directory /var/www/generator/>
    Options MultiViews
    Order allow,deny
    allow from all
    AddHandler mod_python .py
    PythonHandler webservice
    PythonAuthenHandler webservice
    AuthType Basic
    AuthName "Restricted Area"
    require valid-user
    AuthBasicAuthoritative Off
    PythonDebug On
    PythonOption mod_python.legacy.importer *
<VirtualHost *:80>
    DocumentRoot /var/www/generator/
    ServerName generator
    ServerAlias www.generator
Needed modules: apache, util (from mod_python)
Local modules: simplejson

import os
import sys
import datetime
import MySQLdb
path = os.path.abspath(os.path.dirname(__file__))
import simplejson
import logging
import logging.handlers
import os, tempfile
from datetime import date
class MLogger:
    def __init__(self, name):
        self._logger = logging.getLogger(name)
        log_name = 'log-%s-.txt' %
        full_log_dir = '/var/www/generator/log/'#os.path.join(os.path.split(os.path.split(os.path.split(os.path.abspath( __file__ ))[0])[0])[0], 'log')
        full_log_name = os.path.join(full_log_dir, log_name)
            os.chmod(full_log_name, 0777)
        except OSError:
            self._ch = logging.FileHandler(full_log_name)
        except IOError:
            tmp = tempfile.mkstemp(prefix='log_', dir = full_log_dir)
            self._ch = logging.FileHandler(tmp[1])
        self._formatter = logging.Formatter("%(asctime)s - %(name)s - %(levelname)s - %(message)s","%Y-%m-%d %H:%M:%S")
    def get_logger(self):
        return self._logger
local testing data
#main date format
datetime_format = '%Y-%m-%d %H:%M:%S'
date_format = '%Y-%m-%d'
def unformat_date(var):
    unformat string to datetime
    date = None
    if var:
            date = datetime.datetime.strptime(var, datetime_format)
                date = datetime.datetime.strptime(var, date_format)
                # cannot format it
    return date
def format_date(var):
    unformat datetime to string
    date = None
    if var:
            date = datetime.datetime.strftime(var, datetime_format)
            # cannot format it
    return date
Login and password to access this script
web_service_credentials = {'username': 'user',
                      'password': ''
                       #'password': 'U2FsdGVkX19z/09S2MlKaiqCS3YmkwcCnOPqnFkX1Yc='
reports = {27: {'data_fetch_command_sql':
                        SELECT calendar_date 'Order Date',
                            sum(if( country = 'United States', total_amount, 0)) 'US order Volume (US$)',
                            sum(if( country = 'United States', 0, total_amount)) 'Intl order Volume (US$)',
                            sum(total_amount) 'Total order Volume (US$)'
                        FROM daily_order_summary
                        WHERE calendar_date > date(%(measurement_time)s) - INTERVAL 60 DAY
                            AND channel = %(channel)s
                        GROUP BY 1
                        ORDER BY 1 DESC
                    SELECT DISTINCT calendar_date
                        FROM demo.daily_order_summary
                    WHERE calendar_date < date(now())
                        AND calendar_date > date(%(last_measurement_time)s)
field_type = {
    0: 'DECIMAL',
    1: 'TINY',
    2: 'SHORT',
    3: 'LONG',
    4: 'FLOAT',
    5: 'DOUBLE',
    6: 'NULL',
    7: 'TIMESTAMP',
    8: 'LONGLONG',
    9: 'INT24',
    10: 'DATE',
    11: 'TIME',
    12: 'DATETIME',
    13: 'YEAR',
    14: 'NEWDATE',
    15: 'VARCHAR',
    16: 'BIT',
    246: 'NEWDECIMAL',
    247: 'INTERVAL',
    248: 'SET',
    249: 'TINY_BLOB',
    250: 'MEDIUM_BLOB',
    251: 'LONG_BLOB',
    252: 'BLOB',
    253: 'VAR_STRING',
    254: 'STRING',
    255: 'GEOMETRY' }
simple_field_type = {
    0: 'DECIMAL',
    1: 'INTEGER',
    2: 'INTEGER',
    3: 'INTEGER',
    4: 'DECIMAL',
    5: 'DECIMAL',
    6: 'TEXT',
    7: 'DATE',
    8: 'INTEGER',
    9: 'INTEGER',
    10: 'DATE',
    11: 'DATE',
    12: 'DATE',
    13: 'DATE',
    14: 'DATE',
    15: 'NVARCHAR',
    16: 'INTEGER',
    246: 'DECIMAL',
    247: 'TEXT',
    248: 'TEXT',
    249: 'TEXT',
    250: 'TEXT',
    251: 'TEXT',
    252: 'TEXT',
    253: 'NVARCHAR',
    254: 'NVARCHAR',
    255: 'TEXT' }
_NAME = 'channel'
class MysqlConnect(object):
    error = ''
    connection = None
    headers = []
    #headers_types = []
    result = None
    rows = []
    def __init__(self, *args, **kargs): = {
                     'host': 'localhost',
                     'user': 'generators',
                     'passwd': 'p0rtal',
                     'db': 'demo',
                     'port': 3306,
                     'use_unicode': True,
                     'charset': 'utf8'
        if kargs.has_key('host'):
  ['host'] = kargs['host']
        if kargs.has_key('user'):
  ['user'] = kargs['user']
        if kargs.has_key('passwd'):
  ['passwd'] = kargs['passwd']
        if kargs.has_key('db'):
  ['db'] = kargs['db']
        if kargs.has_key('port'):
  ['port'] = int(kargs['port'])
    def connect(self):
            self.connection = MySQLdb.connect(*[], **
            return True
        except MySQLdb.Error, e:
            self.error = "%d %s" % (e.args[0], e.args[1])
        except Exception, e:
            self.error = e
        return False
    def close(self):
        if self.connection is not None:
            except Exception, e:
    def query(self, query, params):
            cursor = self.connection.cursor(MySQLdb.cursors.Cursor)
            cursor.execute(query, params)
        except MySQLdb.Error, e:
            self.error = "%d %s" % (e.args[0], e.args[1])
            return False
            self.result = {'header': [{'name': header[0], 'type': simple_field_type[header[1]]} for header in cursor.description],
                      'data': []}
            records = cursor.fetchall()
            for record in records:
                row = []
                for i, item in enumerate(record):
                    if self.result['header'][i]['type'] == 'DATE':
                        item = item.strftime(datetime_format)
                        item = unicode(item)
            #self.json_result = simplejson.dumps(result)
            return True
        except Exception, e:
            self.error = e
        return False
def is_int(s):
        return True
    except ValueError:
        return False
def authenhandler(req):
    pw = req.get_basic_auth_pw()
    user = req.user
    if user == web_service_credentials['username'] and (
            (web_service_credentials['password'] and pw == web_service_credentials['password']) or not web_service_credentials['password']):
       return apache.OK
       return apache.HTTP_UNAUTHORIZED
def handler(req):
    Binds handler routing
    req.content_type = 'application/json'
    #req.content_type = 'text/html'
    form = util.FieldStorage(req, keep_blank_values=1)
    process(form, req, ret_answer)
    return apache.OK
def ret_answer(ret, req):
    Formats answer to json answer and returns to apache
    #req.write(simplejson.dumps(ret, indent=4))
    return apache.OK
def print_answer(ret, req):
    Print answer to stdout. For test purposes.
    print simplejson.dumps(ret,4)

def process(form, req, ret_answer):
    Main routine
    # empty answer dict
    #ret = {'error': ''}
    ret = {}
    # check for last_measurement_time field
    if 'measurement_time' in form:
        form['last_measurement_time'] = None
    log = MLogger('webservice')
    logger = log.get_logger()'before elem id checks')
    # check for element_id field
    if 'element_id' not in form:
        ret['error'] = 'ERROR. element_id is not set'
        ret_answer(ret, req)
    # check if element_id is correct
    if not is_int(form['element_id']) or int(form['element_id']) not in reports:
        ret['error'] = 'ERROR. element_id is incorrect %s ' % form['element_id']
        ret_answer(ret, req)
    element_id = int(form['element_id'])
    # get mysql connection
    outer_conn = MysqlConnect()
    if not outer_conn.connect():
        ret['error'] = "ERROR. Cannot connect to db: %s" % outer_conn.error
        ret_answer(ret, req)
    if 'command' in form and form['command'] == 'get_measurement_times':
        if 'last_measurement_time' in form and form['last_measurement_time']:
            last_meas_time = unformat_date(form['last_measurement_time'])
            last_meas_time = datetime.datetime(1900, 1, 1, 0, 0, 0)
        if not last_meas_time:
            last_meas_time = datetime.datetime(1900, 1, 1, 0, 0, 0)
        query = reports[element_id]['measurement_time_fetch_command_sql']
        params = {'last_measurement_time': last_meas_time}
        # check for  value substitution
        _value = ''
        if _NAME in form:
            _value = unicode(form[_NAME])
        if not _value:
            ret['error'] = "ERROR. _value is not specified"
            ret_answer(ret, req)
#        # check for  names substitution
#        _name = ''
#        if '_name' in form:
#            _name = unicode(form['_name'])
#        if not _name:
#            ret['error'] = "ERROR. _name is not specified"
#            ret_answer(ret, req)
#            return

        # check for measurement time
        #if 'measurement_time' in form and form['measurement_time']:
        #    meas_time = unformat_date(form['measurement_time'])
        if 'last_measurement_time' in form and form['last_measurement_time']:
            meas_time = unformat_date(form['last_measurement_time'])
            meas_time = None
        if not meas_time:
            ret['error'] = "ERROR. Measurement time is required"
            ret_answer(ret, req)
        query = reports[element_id]['data_fetch_command_sql']
        params = {'measurement_time': meas_time, _NAME: _value}
    if not outer_conn.query(query, params):
        ret['error'] = "ERROR. Cannot execute query: %s" % outer_conn.error
        ret_answer(ret, req)
    result = outer_conn.result

    if not result:
        ret['error'] = "ERROR. Source db returned empty result"
        ret_answer(ret, req)
    ret['header'] = result['header']
    ret['data'] = result['data']
    ret_answer(ret, req)
if __name__ == "__main__":
    for testing from bash
    form = {'element_id': 27, 'username': u'user', 'meas_time': '', '_value': u'corporate sales', '_name': u'channel', 'last_measurement_time': None, 'password': ''}
    if len(sys.argv) >= 3:
        form['command'] = 'get_measurement_times'
        form['last_measurement_time'] = sys.argv[2]
    elif len(sys.argv) >= 2:
        form['measurement_time'] = sys.argv[1]
    process(form, sys.stdout, print_answer)
    from mod_python import apache, util
    directory = os.path.dirname(__file__)