Data Transformation with MongoDB
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:
- Who their customers are
- How their accounting and finances are progressing
- Insights into their servicing and order capabilities
Here’s what Yellowfin did to achieve their requirement:
Step 1: Utilizing the CData MongoDB JDBC driver
Yulia (my fellow consultant) and I were able to utilize the JDBC driver from CData to connect to MongoDB. This is where we found the CData driver: https://www.cdata.com/drivers/mongodb/jdbc/
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.
Step 4: Offering a better reporting solution: Data Transformation
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.