Analyzing & Visualizing Couchbase Data – Tutorial

Table of Contents

• Introduction
• Connecting to Couchbase
• Querying Couchbase Data
• Completing Your Query With Search-Based Analytics
• Analyzing and Visualizing Your Data
• Summary

Introduction

Couchbase is a powerful NoSQL database that empowers enterprises with the ability to store and query large collections of unstructured data. Couchbase’s scalability, flexible data model, and performance rate make it one of the best NoSQL databases on the market today.

As it stands, most existing business intelligence and analytics platforms require custom coding or a tedious ETL process to enable them to use unstructured NoSQL data from Couchbase. Fortunately, Knowi is one of the few business intelligence platforms that offers native integration to Couchbase, which enables our users to take advantage of Couchbase’s strengths.

In this tutorial, you’re going to learn how to use Knowi to analyze and visualize data from Couchbase.

Connecting to Couchbase

Once you’ve logged in to your Knowi trial account, you’ll need to connect to a Couchbase Datasource by following these steps: 

1. Head to the panel on the left side of your screen and select “Data sources.”
2. Head to the top right corner of the NoSQL Datasources and select Couchbase.
3. Select “Test Connection” at the bottom of your screen.
4. Once you’ve ensured that your connection is successful, select “Save.”

Querying Couchbase Data

When you saved your datasource, you should’ve received an alert at the top of your screen saying “Datasource Added. Configure Queries.” Follow these steps in order to begin setting up your first query:

1. Get started by clicking on “Queries.” When you do this, you will be taken to a query builder, and the alert at the top of your screen will change to “Getting Buckets.” This means that Knowi is automatically indexing “Buckets” (or datasets) from the Couchbase datasource that you just connected to. This will take a few seconds to complete, so while you wait, go ahead and name your report “Queens Bakeries.” (In case the report name didn’t give it away, we’re going to analyze data on bakeries in the Queens borough of New York.) 
2. Hover down to “Bucket/Dataset” after your alert changes to “Buckets retrieved. Use the Query generator section to discover and build reports/queries.” Click inside the bar, and you will see every bucket that is stored within your Couchbase Database. Select “restaurant” and Knowi will automatically generate an N1QL Query that queries all columns from the first 10,000 rows of the restaurant dataset.
3. Select “Preview” at the bottom left corner of your screen. Scroll down to see the preview. As you can see, the “address” and “grades” columns are both in nested JSON. Our goal is to analyze specific information within the address field, so we’re going to need to expand it; Cloud9QL, Knowi’s SQL style syntax, makes this a breeze. Just scroll back up to the area where you initially indexed your bucket, and under “Cloud9QL Query”, type “select * expand(address)” and preview your query again.

This time, you’ll notice that your Preview Data has been expanded to include a few new columns: zipcode, coord, street, and building. More importantly, because the new “coord” field contains the coordinates for each of these restaurants, Knowi automatically turned your Preview Chart visualization type into a Geo – Clusters/Custom Maps visualization.

Completing Your Query With Search-Based Analytics

Our query isn’t complete just yet; we haven’t even saved it, let alone homed in on bakeries in Queens, which was our initial goal. In order to do these things, we’re going to get a little help from Knowi’s search-based analytics feature which allows us to query our data in plain English and receive results in real time.  In order to use search-based analytics to complete your query, follow these steps:

1. Head to the search bar at the top of your Preview Data and type “show me every bakery in Queens.” As you can see, Knowi will automatically filter the data to include only rows where cuisine is equal to Bakery, and borough is equal to Queens.
2. Scroll down to your Preview Chart. As you can see, the data now includes a much smaller subset of restaurants, and only those within Queens. This was our goal from the start. Select “Save & Run Now” at the bottom right corner of your screen in order to save this query.

Once you ran your query, Knowi stored the results as a dataset within its elastic data warehouse. Knowi does this every time you successfully run a query.

Analyzing and Visualizing Your Data

In addition to storing the results of your query as a dataset within Knowi’s elastic data warehouse, Knowi also saved your Preview Chart visualization as a widget once you ran your query. Now that your first widget has been created, it’s time for you to give it a home by creating a dashboard to place it, and to further visualize data from your dataset by following these steps:

1. Head to the top of the panel on the left side of your screen and select “Dashboards.” Select the “+” icon to create a new dashboard, name it “Queens Bakery Dashboard”, and click “OK.”
2. Head back the panel on the left side of your screen, and just below “Dashboards,” click on “Widgets.” Here, you will see the “Queens Bakeries” Widget that you just created. Drag it over to your dashboard to add it.
3. Now that you’ve added your widget to your dashboard, you’re set to further analyze your data and create new visualizations. Click the elipses icon at the top right corner of your widget and select “Analyze.” This will direct you to the raw data that is powering your widget.
4. If you look at the columns on the left side of your screen, you’ll notice that there is a plus icon next to “coord” and “grades.” That means that these fields are still in nested JSON format, since we didn’t expand them in our initial query. That’s okay; we can expand them here as well. To do this, click on the plus icon next to grades, and then click the plus icon next to [*].
5. The field that we’re interested in here is the “grade” field. We know that each of these bakeries was given a grade, and we want to analyze the distribution of these grades to figure out what each grade really means; a pie chart should do the trick. In order to set up your pie chart, drag “grade” over to “Grouping/Dimensions:” Then, drag “grade” over to “Fields/Metrics:” and change “Operation” from “None” to “Count.” This will show us how many times each grade appears within our data.
6. As you can see, we’ve got A grades, B grades, C grades, and…P, and Z grades? As well as some that are labeled “Not Yet Graded.” The P and Z grades are fairly uncommon, and we don’t know what they mean, so it’s probably in our best interest to just get rid of them. In order to do this, drag your “count of grades[*].grade” metric over to “Sort by:” and change Direction to “Descending.” As you can see, the most common grades are A, B, and C. Because these are the only 3 that we’re interested in, head over to “Limit:” and enter 3. This will limit our data to the 3 most common grades.
7. Now, head over to “Visualization” and change “Visualization Type” to “Pie.” Instead of saving and overwriting our geo cluster map, we want our pie chart to stand side by side with it, so head to the top right corner of your screen and click the clone icon which resembles two pieces of paper stacked atop one another. Name your widget “Queens Bakeries – Grade Distributions” and click “Clone.” Then, click “Add to Dashboard.”

As you can see from our pie chart, it is overwhelmingly common for bakeries in Queens to receive an A grade, and if they don’t, they will almost certainly receive a B grade. C grades on this scale are about as uncommon here as they are on the report cards of a Rhode’s Scholar, so we can conclude that a C grade is probably a red flag rather that denotes poor quality rather than average or mediocre quality. This is very useful information.

Summary

In review, we started off this tutorial by connecting to a Couchbase database and querying a restaurant dataset within our database using a helping hand from Knowi’s search-based analytics feature to make querying easier. Successfully running this query stored the results of our query as a dataset within Knowi’s elastic data warehouse and stored our Preview Chart as Widget within our Knowi account. Then, we created a new dashboard to store our new widget, and created another widget to further analyze our dataset and answer an important question.