Over a million developers have joined DZone.

Reporting JSON Data With Couchbase and PowerBI

Using Couchbase and PowerBI to visualize data with native connectivity through N1QL REST API

· Big Data Zone

Learn how you can maximize big data in the cloud with Apache Hadoop. Download this eBook now. Brought to you in partnership with Hortonworks.

PowerBI and Native JSON Processing

PowerBI is one of the great new reporting environments you can use to visualize data. One great advantage of PowerBI is its native JSON support and built-in programmable transformation engine. Power-BI can simply directly talk to Couchbase Server and report on the data. No ODBC or JDBC driver required!

Visualize data in Couchbase Server v4 in 4 Steps

Lets walk through the 3 steps to get your Couchbase Server data visualized in PowerBI:

#1 Get Some Sample Data:

I am using the travel-sample bucket that comes with Couchbase Server 4. Install this dataset using the "settings" tab in admin console or during installation. You will be able to follow along with the examples.

#2 Query Through the N1QL REST API:

I will use a fairly simple query for the sample: This query returns full list of all airlines with their total flights to each destination airport.

  SELECT a.name, r.destinationairport, 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 
  ORDER BY r.destinationairport

To connect PowerBI with N1QL. We will use the N1QL REST API to execute the query. Some may not know this but N1QL comes with a REST API that takes in an ad-hoc statement. N1QL REST API is documented here and you can use the JSON aware tool of your choice to directly hit this API. My tool of choice is Postman (available on the chrome macketplace as well). N1QL REST API combined with postman gives me the full visability to what's travelling over the wire.

In the following figure, I am hitting a local cluster node on port 8093 with the above query with postman.

We will pass the encoded URL to Power BI and it looks like this:

http://10.0.0.52:8093/query/service?statement=select%20a.name,r.destinationairport,%20count(a.name)%20total_flights%20from%20`travel-sample`%20r%20join%20`travel-sample`%20a%20on%20keys%20r.airlineid%20where%20r.type%20=%22route%22%20and%20a.type=%22airline%22%20group%20by%20r.destinationairport,%20a.name%20order%20by%20r.destinationairport

#3 Submit and Transform Data in PowerBI:

Last, you need to fire up the desktop version of Power BI to get your memory into shape. Desktop version is free and is available here.

  • Goto "Get Data" menu and choose "Web" as the method. Enter in the URL above and wait for the data to show up.
  • Now we will go through a set of transformations to prepare our data for visualization.
    1. Once the results are streamed into Power BI, you will see the JSON attributes that come back from N1QL. Drill into "results" by clicking the "List" link in the screen.
  • We will use the table transform to shape our JSON. Click on "To Table" menu at the top and don't specify a delimiter. Simply click "OK".
  • Next, you will click the little "split" icon at the top of column in the single column grid to split the attributes in JSON into seperate columns in the table.
  • Once you click ok, this is what the completed data transformation should look like...  Simply click "Close & Apply" at the top left corner
  • #4 Visualize Data in PowerBI:

    I like to use the TreeMap visualization for displaying the most popular destinations for each airline. To get there,

    1. Drag "Column1.destinationairport" under Group 
    2. Drag "Column1.total_flights" to Values.
    3. Drag "Column1.name" to Page Level Filters and expand the field to filter on "Air Foyle".

    You will also modify the data type for total_flights. To get there, there are 2 more clicks. Follow the Green Plus on the figure below.

    1. Change the data type for column total_flights to "Whole Number".
    2. Change the Aggregation on total_flights to "Sum".

    And you are done!.

    Previous decades, relational data with tables and columns were the most popular shape. ODBC and JDBC provided a great deal of ease to connecto to that data. Couchbase Server provides this option as well. Here is the link to download free ODBC and JDBC Drivers for Couchbase Server. However... What I am seeing in todays world is, majority of the new data that is generated and stored is in JSON. With these changes, the new data visualization platforms are adding powerful support around JSON. PowerBI has done a great job to to make it super easy to work with data in Couchbase Server without and ODBC driver.

    Hortonworks DataFlow is an integrated platform that makes data ingestion fast, easy, and secure. Download the white paper now.  Brought to you in partnership with Hortonworks

    Topics:
    json ,business intelligence ,couchbase

    Published at DZone with permission of Cihan B., DZone MVB. See the original article here.

    Opinions expressed by DZone contributors are their own.

    The best of DZone straight to your inbox.

    SEE AN EXAMPLE
    Please provide a valid email address.

    Thanks for subscribing!

    Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
    Subscribe

    {{ parent.title || parent.header.title}}

    {{ parent.tldr }}

    {{ parent.urlSource.name }}