Data Transformation with MongoDB

Posted by: Tim McIntosh

An internet provider recently came to Yellowfin wanting to be able to report off MongoDB. As you know, MongoDB is not a relational database and many reporting tools struggle to connect and pull anything insightful from the data. So, we were met with a challenge.

The internet provider was interested in understanding their own data around the following areas:

Step 1: Utilizing the CData MongoDB JDBC driver

Step 2: Connecting to MongoDB in Yellowfin

The CData driver gave us the capability to connect to the MongoDB data source and view the data in Yellowfin by utilizing CData’s metadata layer, powered by MySQL’s query engine. We worked with CData to acquire the correct connection string to use within Yellowfin: jdbc:mongodb:Server=localhost;Port=27017;Database=fcl;FlattenObjects=true;

By using the “flattenobjects” option, we were able to flatten nested objects within the MongoDB so that they would be available to use in Yellowfin.

Step 3: Understanding the reality

The original idea of creating a proof of concept (POC) was to showcase how Yellowfin can utilize the data by reporting directly off MongoDB. However, the reality was that based on the customer’s requirements the data had to be restructured to facilitate their needs.

I played with the idea that, potentially, we could use Yellowfin’s data transformation capabilities to transform data from MongoDB to MySQL. It worked really well and the transformations were quite easy to do. I only spent an hour or so setting these up. See the screenshots below. I simply created a transformation step for each table the internet provider had. I moved between input steps “freehand SQL” and “single table”.

Step 5: Producing a more insightful POC

By using data transformation within Yellowfin, we were able to create a much more insightful POC because we were now using data that had been moved to a relational database. The team at the internet provider were quite impressed with how Yellowfin presented the POC back to them. I believe one of their lines was: “We have never seen our data visualized in this way before. It’s quite interesting to see the insights you’re already producing for us.”

Here are some screenshots:

It’s always a great challenge to try to find ways of doing things that other analytics platforms struggle to do. We are always on hand to find new ways to help clients get the most out of their BI and analytics.