Monday, July 20, 2015

Multi-Datasource Joins


As organizations increasingly adopt polyglot database architectures with data across multiple disparate data sources, deriving a unified view into that data becomes a difficult proposition where significant engineering efforts must be spent to cleanse, join prior to defining and loading into a traditional data warehouse for reporting purposes.

Cloud9 Charts is excited to announce our Multi Datasource Joins feature. This allows users to query data from one data source, perform look-ups and joins against other sources, and seamlessly store the merged result into a schema-less warehouse, along with visualization and analysis capabilities on it.

A step-by-step example:

In the following example, we’ll connect and query a MongoDB database, then perform a inner join against a customer table in MySQL to retrieve the customer details for each result. 

Step 1: If you currently do not have an account, sign up for free at cloud9charts.com

Step 2:
  • Click on the Datasource icon on the left menu --> select MongoDB --> accept the default settings (points to a MongoDB instance that we run on the cloud). Similarly, set up a MySQL Datasource to point to the default settings (see GIF below). 

  • Click on the Queries/Datasets menu icon --> Add New Query 
  • Use data the Query Generator to dynamically generate the queries. 
    • Select "sendingActivity" from the collections drop down menu. 
    • Select "sent" from the metrics drop down menu. Click on the selected item to perform a Sum aggregation it. 
    • Select "customer" from the dimensions drop down menu.
Here we are tracking a simple metric of Total Sent messages grouped by a customer.
  • Click "preview" to view the results.
Step 3:

Now, for the returned results from MongoDB, let’s look up the customer details, which is stored in a MySQL table called customer.

  • Click "Join". 
  • On the dropdown, select the new MySQL data source we just created from the dropdown menu 
  • Use data the Query Generator to dynamically generate the queries. 
    • select "customer" from the collections drop down menu 
  • Map fields from previous queries to the current one using "join fields" 
    • enter in "customer=customer" into the join fields box 
  • Click "preview" to view the results





The preview should now contain data from both MongoDB and MySQL that includes address and currency fields added to the MongoDB query for those customers.


Click on Save. This dataset can now added to a dashboard and shared/embedded easily.


In a few simple steps, we’ve gone from raw data to insights from multiple datasources.