Ever wanted to visualize your  database information ?

here is a quick answer:

SQL –> XML –> XSL trasnformation–> DOT –> GraphViz –> JPEG/PDF

I work theses days with Siebel CRM system.  It is very configurable in how you want to store your data and display them.

One thing about Siebel Configuration is that you can overload one Table with multiple entities.  Each Siebel customization could be different in how you organize your entities into tables. So I had a problem in really understanding how the system was developed. Cases like which type of records link to which entities in the same table or other tables.

I was looking for someway to visualize Entity relation based on the data that is there in the Database -I’m not talking about Entity Relationship Diagram (ERD)- I’m talking about how certain customers relate to each others and to their addresses.

I always thought I could use SVG for this task, but the problem of SVG, is that you have to describe the location of each node you want to display, Which would added extra effort on me when displaying my network of customers (data records) and their relationships. I would have to develop and Algorithm to distribute the displaying of customers on the canvas and then make sure the relationships don’t intercept too much together.

One day I stumbled upon a well-known mathematical theory called the “Graph Theory” which explains universes with network nature – exactly the type of model I was looking for- by digging in further I found  about Nodes and Edges (Node relationships).

Going in Deeper; I found the GraphViz tool, which is dedicated to visualizing Nodes and Edges. This tool read something a dot file, which describes the Nodes ,how to display them and describes the Edges and the their properties without caring about distributing them on the resulting Canvas.

The Question now is : How to get from Oracle DB to .DOT file ?

1. Since the Data is in Oracle DBMS, I used XMLDB functions like XMLAGG, XMLELEMENTS,XMLCONCAT,XMLROOT  and XMLFOREST to generate the data into one OUTPUT.XML.
2. Then used XSL trasformation to parse through the XML data and generate the required DOT file.
3. Eventually read the DOT file into GraphVIZ.

Find below the bird’s-eye-view of sample output (just to let you know what is possible):

So in conclusion; this is the process I followed:

Oracle DB to Network graph

and by the way The above graph is generated with GraphViz using this .DOT file: