PHP Demo

If you’d rather skip the story and just go see the tool now, click here.

Warning: Techno-babble ahead!

I’ve wanted to move into web development for some time. I was looking for a project to help me develop my PHP and MySQL skills, when I happened on some information about the Google Chart API. Having worked on business intelligence (BI) tools such as Hyperion Essbase, Business Objects, and Cognos BI I wondered: would it be possible to custom-build my own BI tool?

So I set about creating a web-based application to do some basic visualizations. I envisioned a PHP-based tool that would allow me to access MySQL data and create charts from Google Charts, with a limited capability for analysis (for example, changing the chart contents based on a simple filter criterion such as product or geographic area). I used an open-source tool called Spawner to create a very simple data mart. The mart has 3000 fact rows, 100 customers, and a handful of products and employees. I then set about creating the application.

Google Charts is designed to connect to a relatively small set of data sources, and MySQL is not one of them. But it does have a DataTable structure that allows you to populate your own data with which to drive a chart. So I was able to dynamically build the Google Chart API calls in PHP, using data returned from a MySQL query. The HTML and JavaScript source that gets generated isn’t as pretty as I’d like, but it passes unit test! I coded just enough CSS for layout purposes, but I could certainly do more in the styling area.

The business case is a fictitious gaming supply company that sells casino chips, dice, and gaming-related apparel. I initially set up four charts: a line chart of monthly sales over two years, a pie chart of sales by employee, a map of sales by US state, and a column chart of year-to-date sales that lets the user choose what product to focus on.

Having gotten the app uploaded, I’m now contemplating what I could do to improve it. The answer: Plenty! Here’s some things I’d like to do:

  • Create a basic report writer to leverage Google’s chart options.
  • Create an application to build a semantic layer (like Business Objects’ universe or Cognos framework model) so report designers can see business terms instead of database fields when trying to create reports.
  • Maybe use a different (preferably open-source) charting API that could be deployed locally, thus eliminating dependence on the Internet (needed for access to Google’s Chart API).
  • Do a bit of modularizing; right now the whole thing is about 120 lines of code in one index file with a small CSS file.
  • Expand the filtering capability to allow the possibility of true drill-through.
  • Add support for other database management systems.

So there it is. Please let me know if you find this interesting and/or potentially useful. You can access the demo here.


PHP Demo — 2 Comments

    • If I understand the question correctly, I did not use a tool or framework such as Zend to create the charts, although you certainly could do so. The PHP is hand-coded. I create a connection to a MySQL database, query for the specific “business” data I need, and perform a separate query to get the specifications for the chart or report I want to display. I then use PHP to parse out the JavaScript call to the API based on the data I have and the chart type I want. HTH

Leave a Reply

Your email address will not be published. Required fields are marked *