Right Outer Join

18 July 2014

Hierarchical JSON from Oracle

Filed under: Master Data Management, Oracle — Tags: , , , — mdahlman @ 14:03


Semarchy manages master data hierarchies (corporate structures, product group hierarchies, employee management trees, etc.) easily with out of the box functionality. By this I mean it can validate the data, match up different sources, enrich the data from external systems, manage an audit log of changes and so forth. It’s all great stuff.  But on a recent project  I wanted to display hierarchical data using an intuitive visual interface. A plethora of visualization libraries exist, and I was leaning toward using D3 since it appears to be one of the most polished, most used, and most actively developed at the moment.


The D3 example I wanted to use is designed to accept data in JSON format. My data is in Oracle, and Oracle doesn’t provide a simple way to generate a complex JSON output.

Likely Solutions

A few people pointed me to plsql-utils, aka Alexandria, as the best starting point. It’s a really useful Oracle resource, and I spent some time investigating this idea. Morten Braten’s 2010 article about it is excellent. But in the end I didn’t find it to be the right tool for this problem. It made it very easy to take a result set and convert it to valid JSON where each row was a JSON record. But this was just tabular data as JSON, so this was not what I needed to feed into the D3 engine. I have no doubt that I could write a stored procedure which could loop through my data to get a more appropriate hierarchical structure and then use plsql-utils to convert this to JSON. But the level of effort required seemed high.

I found several references to PL/JSON. This project shows potential, but it doesn’t appear to be actively developed (as of mid 2014).

There’s an interesting answer at the greatest of all answer sites from Mike Bostock, the author of D3(!). That example is focused on converting comma separated values (CSV) data to JSON. The concepts could be applied here. But my data, though tabular, is not actually CSV. I would prefer to use the D3 sample with the smallest number of changes possible. So I would much prefer to return the data to D3 already JSON-ified if I can.

Then I found Lucas Jellema’s 2011 article about generating a JSON string directly from a query. This presented a more intuitive approach for me. He uses a common table expression (CTE) to easily create a sub-select to gather the hierarchical information along with the LIST_AGG analytic function to present it well. Clever. In the end I didn’t actually use LIST_AGG and I didn’t really use a CTE. (OK, my sample query below has a CTE… but it could be changed into a standard subquery with trivial effort.)

My Solution

In the end I decided to use Oracle’s inherent abilities to handle hierarchical information (mainly the CONNECT BY syntax) and then convert it to JSON with the additional of simple string logic. The key concepts needed in this conversion are:

  • The CONNECT BY query can return the data in a specified logical order.
  • By knowing if the next record is at a higher, lower, or equal level in the hierarchy, we can generate JSON brackets correctly.
  • We can know if the next record is at a higher, lower, or equal level in the hierarchy by using analytic windowing functions like LAG and LEAD.

Here’s the commented SQL used to return the data

WITH connect_by_query as (
     ROWNUM                               as rnum
    ,FIRST_NAME || ' ' || LAST_NAME       as FULL_NAME
    ,LEVEL                                as Lvl
    /* the top dog gets a left curly brace to start things off */
    WHEN Lvl = 1 THEN '{'
    /* when the last level is lower (shallower) than the current level, start a "children" array */
    WHEN Lvl - LAG(Lvl) OVER (order by rnum) = 1 THEN ',"children" : [{' 
    ELSE ',{' 
  || ' "name" : "' || FULL_NAME || '" '
  /* when the next level lower (shallower) than the current level, close a "children" array */
  || CASE WHEN LEAD(Lvl, 1, 1) OVER (order by rnum) - Lvl <= 0 
     THEN '}' || rpad( ' ', 1+ (-2 * (LEAD(Lvl, 1, 1) OVER (order by rnum) - Lvl)), ']}' )
from connect_by_query
order by rnum;

Here’s an example of the data returned (formatting was added afterwards, but the data was returned exactly like this):

  "name": "Steven King",
  "children": [{
    "name": "Neena Kochhar",
    "children": [{
      "name": "Nancy Greenberg",
      "children": [{
        "name": "Daniel Faviet"
      }, {
        "name": "John Chen"
      }, {
        "name": "Ismael Sciarra"
      }, {
        "name": "Jose Manuel Urman"
      }, {
        "name": "Luis Popp"
  }, {
    "name": "Lex De Haan",
    "children": [{
      "name": "Alexander Hunold",
      "children": [{
        "name": "Bruce Ernst"
      }, {
        "name": "David Austin"
      }, {
        "name": "Valli Pataballa"
      }, {
        "name": "Diana Lorentz"
  }, {
    "name": "Den Raphaely",
    "children": [{
      "name": "Alexander Khoo"
    }, {
      "name": "Shelli Baida"
    }, {
      "name": "Sigal Tobias"
    }, {
      "name": "Guy Himuro"
    }, {
      "name": "Karen Colmenares"

With the data in that form, it was easy to implement this D3 sample inside Semarchy Convergence for MDM:

Employee Hierarchy in Semarchy MDM

The left side shows the standard tree view. Practical.
The right side shows the D3 tree visualization. Awesome.
(And practical in different ways.)


Here is the SQL (creates, inserts, and the complete select statement) to try it yourself:

Oracle select query to generate JSON data


8 July 2014

MDM in the Cloud (on Amazon AWS Marketplace)

Semarchy MDM on AWS Marketplace

Semarchy shows off its 5 star reviews as the most popular MDM solution on Amazon’s AWS Marketplace

MDM in the Cloud

One of the biggest impediments to Master Data Management (MDM) projects is that they can be hard to get started. An enterprise has lots of people and lots of groups who all stand to benefit from improved data quality, structured data governance, and systematic master data management. But the very fact that so many people stand to gain from it is also a reason why it’s slow to start. Gathering requirements and opinions from everyone takes time.

One of the best ways to get quick agreement about what the scope for the first iteration of an MDM project is to generate a quick proof-of-concept or proof-of-value prototype. And one of the quickest ways to get started on an MDM prototype is by using software that’s completely pre-installed and pre-configured. This can lead to better alignment about what will be possible in an MDM project ensuring that a project will be more successful.

The cloud is a natural fit for this.

Amazon’s AWS Marketplace provides an environment where it’s easy to find software that’s relevant to your needs and get it launched instantly without any up-front costs. When I worked at Jaspersoft I invested quite a bit of time into getting a pre-configured JasperReports Server instance available and in making it easy for people to use Business Intelligence (BI) in the cloud. It was a natural fit especially for anyone who already had data in Amazon RDS or Redshift. The time we invested in that paid off nicely as customers flocked to it. Sales are way up; the reviews are great; and it should serve as a model and an inspiration to other vendors considering cloud offerings.

Semarchy in the Cloud

While business intelligence offerings in the cloud are legion, traditional Master Data Management vendors have been much too slow to embrace the cloud. The industry has taken baby steps. For example, Informatica purchased Data Scout and sells this as their SaaS MDM Salesforce.com plug-in solution. It’s a great utility for salesforce.com, but I don’t put it into the same class as enterprise MDM. Other SaaS MDM solutions are similar.

At Semarchy I see the cloud as an excellent vehicle for putting enterprise MDM into the hands of more users. You can have a fully functional MDM server running in an Amazon Virtual Private Cloud (VPC) in less than an hour. It’s accessible to only people from your company, and it’s ready for you to model your master data management requirements and to start fuzzy-matching and de-duplicating your data.

I expect other vendors to follow eventually. The net result will be improved solutions available to data management professionals everywhere. I’m pleased that Semarchy is leading the way.

Create a free website or blog at WordPress.com.