5 in 5 Vizualization 2 - Generating a d3.js visualization from a SQL Developer HTML report...
This second vizualization is more of a workflow proof of concept compared to some novel visualization but I can guarantee you haven't seen the SCOTT.EMP table like this before. I have wanted to combine my two favorite tools for a while. Here is how you can drive a d3.js visualization directly from a SQL Developer custom report.
SQL Developer is a leading free database query and administration tool from Oracle.
So here is a way to leverage SQL Developer's features to grab data for us. We will create a shell of a HTML doc which can be used to create a custom graphic in d3.js. And we can share this with our friends pretty easily - no server side knowledge or admin privileges required.
Looking at data for this POC I zeroed in on one of my favorite relationships - hierarchical relationships. These are all over the place in the data we work with day to do. There are some convenient ways to work with the data like Parent/Child or Drill Down reports. The problem is these often break down after your data is one or two levels deep. So this is great for say showing records on each side of an FK relationship, but when about when you want to see the SCOTT.TMP.MGR hierarchy from King all the way down to every employee you have to do some CONNECT BY LEVEL magic and you still have to deal with some tabular output oddness.
select (SELECT ENAME FROM EMP WHERE EMPNO=E.MGR) AS MGR_ENAME, ENAME FROM EMP E WHERE MGR IS NOT NULL;
This returns 13 rows. Order is not important for this. The code that will accept this data will we will feed the data to make connections as they are introduced to the graphic.
I had already done a lot of work with d3 node graphs earlier this year so I started with some of my earlier prototypes and simplified them to the bare minimums.
Pop-Up Schemio V1
Pop-Up Schemio V5 - Click "Add Next Event" button to add objects to graph.
I usually prototype d3.js using tributary.io. It is a site where you can browse visualization and build your own. Tributary has a secret superpower of its own - it is a live coding environment. Great for people like me who casually know 20 languages and the one I know best is pseudocode. While changing code in that environment you can easily see the effects of you changes without the normal edit / refresh cycle associated with web development.
The Difficult part
- A HTML page generated from SQL Developer which contained a JSON representation of the data I wanted to use with d3.js
- A reference to the d3.js library
- A reference to a Style Sheet so I could make things pretty
- A reference to my custom .JS code that I will keep on a web server accessible to me. This can either be internet or intranet availability, just depends on what controls you want to take with your code. Technically you could keep them on your local PC as well but sharing would be harder. If the external files are available on the net you can export your report definition and share with friends. If on your local PC you would need to make them a bundle and make sure the file paths in the parent HTML document are available on their systems as well.
Building this yourself
1) Put the mynodecode.css and mynodecode.js on a web server accessible to you and your users.
2) Create a User Defined Report of type "PL/SQL DBMS_OUTPUT". Use the reportcode.sql source. Modify the paths to the .css and .js files as needed.
3) Right click on the UDR and select "HTML..."
4) Set DB connection, select "Open When Complete" and click Apply.
5) SQL Developer will render the report to a local HTML file and fire up a browser to view it.
The biggest challenge here was organization of the code. I wanted SQL Developer to be responsible for pushing the data required for the visualization and wanted to avoid wrapping any heavy logic into the SQL Developer User Defined report itself. Review reportcode.sql for the trim amount of work I have SQL Developer contribute to this process.
Here is an example of the report running after everything is in place.