Using MySQL Proxy to integrate operational data

Originally posted on SUN, JULY 15, 2007 AT 07:51

Most organizations have operational reports that are run on a daily basis. These reports record such things as balances, occurrences and transactions - the day-to-day facts of their operation. Operational reports differ from Analytical reports in that they tend to be snapshots of a relatively narrow time slice. Analytical reports tend to range over much more data covering a longer duration to be probed for individual facts or to generalize the data based on trends or extremes.

To take an example from a call centre, a typical operational report would be a report for the previous day showing the number of calls received broken down by agent. Duration, and probably call routing information from the Interactive Voice Response (IVR) system would be included for each call as details. A typical analytical report would aggregate the same data from a longer period say, 13 weeks and be used to look for trends, extremes and exceptions. If the call centre wanted to understand its relative productivity, or needed to make educated predictions about its future call volume, these reports are used.

What often happens is that companies will have really good coverage on the operational reporting side and abysmal coverage on the analytic side. This, despite the best efforts of the Business Intelligence vendors to make analytic reporting easy and pervasive. One of the main reasons for this is that most data is not in a dimensional model and is distributed all over the place either physically (multiple data sources) or logically (in a normalized form). Companies are often fearful of building Data Warehouse solutions due to perceptions of cost and risk.

One promising approach is to use the operational reports as a de facto data warehouse. Since the data is being extracted as facts along dimensions and with details, a kind of dimensional modelling is occurring in the report design. If the reports are run at intervals and saved as instances, a time series of dimensional data is being accumulated. The trick though, has been in accessing this data. As there is no procedural query syntax for this kind of data.

MySQL Proxy allows SQL queries submitted to the MySQL server to be intercepted and interpreted before being passed on to the server. This allows for query re-writing and language extension. This provides an opening for querying of report instances. The following imagined query would return aggregate data grouped by call agent for a given time period using data saved in report instances created by running an operational report on a daily basis over a three month period.

SELECT Agent_Name, COUNT(Call_Id), AVG(Call_Duration), MIN(Call_Duration), MAX(Call_Duration) FROM REPORTREPOSITORY( 'Call Centre Reports/Call Details by Agent', '2007-01-01 00:00:00.000', '2007-03-31 11:59:59.999') GROUP BY Agent_Name

MySQL Proxy includes a Lua parser that in turn gives you shell access. This should be sufficient to afford API access to a report repository - given that such API access is provided by the reporting solution. The main challenge for this approach is the amount of time that it would take to extract the saved data out of each report instance, which I would expect varies, depending on report format and API design.