Saturday, April 9, 2016

Couchbase Native BI with N1QL

Originally published on Couchbase blog.
Native Couchbase N1QL Business Intelligence & Reporting

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.