Monday, October 20, 2008

Why don't databases have foreign keys any more?

If you've looked at a database from a vendor lately (Tivoli included; some example products that include databases are CCMDB, TADDM, ITCAM for WAS, ITCAM for RTT to name a few), you'll notice that there are very few (or NO) relationships between the tables. What this means is that you can't get a nice Entity Relationship Diagram from them, and that makes it quite a bit harder to write reports using the data in them. 

So why is it this way?

More and more, software developers are using Object-relational mapping (ORM) components to abstract the software from the database. Here is a pretty comprehensive list of object-relational mapping software. What this software does is abstract the data relationships from BOTH the software AND from the database, putting that relationship information into various files that are used by the selected ORM implementation. The impact this has is that it is difficult as a consumer/user of the software to write reports directly against the database - because the first step is having to reverse engineer the usage of the tables in the database.

What can be done about it?

I can think of a couple of approaches:

1. The best solution I can think of is that you need to ask the vendor for ERDs for all databases that will be used to store collected metrics. 

2. Since there may be some valid intellectual property-related arguments against no. 1 above, the next best approach would be to ask the vendor for the SQL needed to produce specific reports.

3. If neither of the above works, then reverse engineering is the only approach left. I've had success in this area by turning up the debugging on the software and looking for SQL "SELECT" statements in the log files. 

Wednesday, October 15, 2008

Data visualization using Google Spreadsheets, Yahoo Pipes and Google Maps

OK, so this isn't a Tivoli-related post, but since a big part of what we all want to do is visualize data, I thought that this post that I found through reddit.com was an exellent description of how to use several free web-based utilities to gather and display data. Here's the link:


Here is the author's summary:

So to recap, we have scraped some data from a wikipedia page into a Google spreadsheet using the =importHTML formula, published a handful of rows from the table as CSV, consumed the CSV in a Yahoo pipe and created a geocoded KML feed from it, and then displayed it in a Yahoo map.

I haven't come up with an implementation that gathers Tivoli data, but I can think of one:

The main thing you would need is to get HTML that can be consumed by Google Spreadsheets. One way to do this would be with a JSP (on WAS) or PHP (on IBM HTTP Server) or other server-side language that makes SOAP or direct database calls to retrieve data from TDW and display it in HTML. Once you have that, you could then follow the steps in the article to viualize the data.