Visualize your Data with GraphViz

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 adds an 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 reads something called a *.dot file, which describes the Nodes ,how to display them and describes the Edges and the their properties without worrying about distributing them on the resulting Canvas.

The Question now is : How to get from Oracle DB Records and Fields 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 process the XML data and generate the required *.DOT file.
3. Eventually read the DOT file into GraphVIZ.

One limitation that I still find in GraphViz is that the produced PDF’s are useless unless you you have a huge plotter. (i hope then fix that in Future releases)

An update 2014 Nov: there are some JavaScript packages out there that can do the same, maybe I will discuss them in other articles).

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: