Native Couchbase N1QL Business Intelligence & Reporting

This is a guest blog post by Jay Gopalakrishnan, founder of Cloud9 Charts, a Business Intelligence platform for multi-structured data with native NoSQL integrations.

N1QL provides unprecedented query capabilities on Couchbase with sort, filter, aggregations and join capabilities on JSON, with a familiar SQL syntax. Cloud9 Charts provides visual insights from Couchbase data with native N1QL support.

This post is a hands-on primer of the Cloud9 Charts integration into Couchbase. With nothing to install, you’ll be able to derive visualizations from our live Couchbase demo database in just a few minutes.

The integration has the following key features:

  • Data to insights and everything in between from queries to visual insights, either in the cloud or on-premise.

  • No ODBC drivers, desktop tools or separate publishing servers to install.

  • Fully native N1QL integration that maintains multi-structured data form without shoehorning the data into relational form.

  • Basic N1QL query generation and field discovery.

  • Drag & Drop Analysis capabilities.

  • Advanced transformations on the data including prediction models & cohorts

  • Multi-datasource joins.

  • Visualizations and dashboards with publishing sharing & embed.

The walkthrough uses the travel-sample bucket from Couchbase samples. We’ll use it to run a simple N1QL query to determine flights by destination, by each airline. We’ll put together some visualizations from it to build the following simple dashboard.

Steps:

  1. Go to https://www.cloud9charts.com/couchbase

  2. Paste the following query into the N1QL section:

SELECT

    a.name,

    r.destinationairport,

       a.country,

    count(a.name) as total_flights

FROM travel-sample r JOIN travel-sample a ON KEYS r.airlineid

WHERE r.type =”route” AND a.type=”airline”

GROUP BY r.destinationairport, a.name, r.country,r.city

ORDER BY r.destinationairport

 

This gets flights by each carrier by each destination airport.

     Click on ‘Show Me’ and wait for the query to return results. The return data in a simple grid form looks like this:

       

      3. Build Visualizations:

  • From the settings menu of the widget, select  ‘Analyze’ to open the dataset for analysis and aggregations.  

  • Drag & Drop total_flights field into Fields/Metrics section. Select “Sum” as the aggregation type.

  • Drag name into the dimensions section. This will now list the Total Flights by airline name. Also drag total Sum of Total flights field into the sort area and sort by Descending order. We now have a listing of total flights by carriers.              

  • Select “Bar” as the visualization type to build a simple bar chart from it.

  • Click on Save.

  • Select Word Cloud visualization type from the visualization carousel.

  • Click on Save → Clone into new widget → Add to Dashboard.  This creates a word cloud visualization from the same dataset and adds it to the dashboard.

 

That’s it – In a few simple steps, we’ve built a simple dashboard from Couchbase data.

Zz7ovLDbiV.gif

 

Additional Resources:

Couchbase Integration Docs

Documentation

Multi-Datasource Joins

Data Transformations, Predictions and more

Posted by The Couchbase Team

Leave a reply