Tutorial: Using MongoDB, Windows Communication Foundation, OData, and PowerBI to build a Business Intelligence solution on Windows Azure

Table of Contents

  • Overview
  • MongoDB Options for this Tutorial
  • Prerequisites
  • MongoDB Option 1: Steps to setup a windows server 2012 datacenter VM
  • MongoDB Option 2: Set up the MongoLab Service for Windows Azure
  • Connecting to MongoDB using OData via Windows Communication Foundation (WCF)
  • Open Government Data : the FAA Aircraft Database
  • Viewing OData with LINQPad
  • Visualizing OData using PowerBI
  • Mapping the Data with PowerMap
  • Conclusion

Overview

For this tutorial we’re combining three open themes – Open Government, Open Data, and Open source. First, for Open Government, we’re using open domain data provided by the US Federal Aviation Administration. Next, we’re using an open source Database, MongoDB, to store data and lastly, we’re using the Open Data protocol (OData) to connect to and visualize the data.

MongoDB Options for this Tutorial

You have the option of setting up MongoDB on a Windows Azure VM, or using a free subscription to the MongoLab Mong0DB-as-a-Service on windows Azure. I’ve outlined both processes below. If you set up your own VM you have more control – it’s your virtual server. If you set up MongoLab, you don’t need to own or maintain your own server or MongoDB instance, the MongoLab service takes care of all that for you. Both options for fine for the purposes of this tutorial.

Prerequisites

This tutorial requires a Windows Azure subscription. If you don’t have a subscription, you can sign up for a free trial subscription on WindowsAzure.com. You’ll also need Visual Studio to set up the Windows Communication Foundation (WCF) OData data service. If you don’t have the full version of Visual Studio You can download Visual Studio Express here for free.

MongoDB Option 1: Steps to setup a windows server 2012 datacenter VM

Login to your azure portal and select the virtual machines tab. Next, select compute > Virtual Machine > Quick Create. Select the Windows Server 2012 Datacenter selection for the image type, then fill in all the relevant info below to create the VM.

clip_image002[4]

It takes a few minutes to get the VM up and running. Once the VM is created, open the virtual machines tab and select the machine you just created. Select the dashboard option and click CONNECT to get a remote desktop file you’ll use to set up the VM:

clip_image004[4]

You’ll get a prompt to open or save the RDP file. Save it then open your downloads folder and double click to open the saved RDP file. You should see a warning that the publisher of the remote connection cannot be identified, it’s OK to click connect (it’s a trusted source).

In the Windows Security prompt, click on Use another Account:

clip_image005[4]

Fill in the username and password you used to create the account. Make sure domain is blank, and click OK.

clip_image006[4]

You will see a warning similar to this, just click Yes (it’s a trusted remote computer):

clip_image007[4]

It will take a few seconds to connect and log in to the VM via remote desktop.

Setting up MongoDB

The first thing we need t do is prepare the VM for running MongoDB by opening ports for ongoDb communication. In the Windows Azure Portal, select the VM you just created, then select endpoints. Add the following endpoints:

NAME: mongodb

Protocol: TCP

Public Port and Private port: 27017

This is just the minimum for setting up MongoDB on a Windows Azure VM. There are many more configuration options detailed in the documentation at http://www.mongodb.org/

Next, let’s set up and empty MongoDB database and start the MongoDB server. From your local computer (not the VM - downloading files to your main computer and not the VM directly avoids multiple security issues and prompts).

Go to the MongoDB Website and select the download tab. Select the download link for the Windows 64 bit version.

clip_image009[4]

Download the .zip file then copy the file to the VM. Create a directory named C:\mongodb and extract the MongoDB zip file to this directory.

clip_image011[4]

Next, create a directory called C:\data, then another called C:\data\db.

Open a command prompt as administrator then run the below command to start the main MongoDB database process:

C:\mongodb\bin\mongod.exe

The waiting for connections message in the console output indicates that the mongod.exe process is running successfully.

Open a second command prompt as administrator and run the following command to connect to MongoDB using the mongo.exe shell:

C:\mongodb\bin\mongo.exe

The mongo.exe shell connects to mongod.exe via port 27017 that you set up earlier. The MongoDB instance is now up and running and ready to receive data and commands.

To make sure that the instance is running properly, let’s run mongotop.exe. mongotop tracks the amount of time a MongoDB instance spends reading and writing data, with a test every second, which makes it a quick and easy way to test your instance is functioning properly. Errors or very long response times indicate something wrong with the server or the configuration. More documentation on mongotop can be found here.

Open a third command prompt as administrator and run the following command to start mongotop:

C:\mongodb\bin\mongotop.exe

Very small response times indicate the system is functioning properly. 0ms response times are optimal, especially with little or no data in the database:

clip_image013[4]

For complete instructions on how to setup MongoDB, follow this link.

MongoDB Option 2: Set up the MongoLab Service for Windows Azure

Open Internet Explorer and navigate to https://manage.windowsazure.com. Login to open the Windows Azure Management Portal.

clip_image014[4]

Select New at the bottom of the page.

clip_image015[4]

Then click Store.

In the Choose an Add-on dialog, select MongoLab from All and click the arrow at the bottom right of the page:

clip_image017[4]

clip_image019[4]

In Personalize Add-On, select the Sandbox radio button for the tutorial. The high-availability MongoDB Replica Set Cluster is for production systems that need more horsepower.

Enter a MongoLab instance name and select a region, then click the right arrow at the bottom right to continue.

Next you’ll see a Review Purchase tab, click Purchase (there will be no charge for the free option) to complete the database setup.

After a few minutes, the data status should show Started indicating that the database is ready:

clip_image021[4]

Select your new MongoLab instance and click Connection Info from the lower toolbar to open the connection info dialog box.

clip_image022[4]

MONGOLAB_URI shows the connection string to the MongoDB service on Azure. Copy this text to the clipboard by clicking on the icon to the right of the MONGOLAB_URL text field.

Note that the URI for all MongoLab databases is in the following format:

      mongodb://<dbuser>:<dbpassword>@host:port/dbname

You can use your MongoLab URI to figure out your DB username as well as your dbname.

Connecting to MongoDB using OData via Windows Communication Foundation (WCF)

For this tutorial we’re using Windows Communication Foundation (WCF) to set up an Open Data Protocol (OData) Data Service.

Windows Communication Foundation (WCF) is a framework for building service-oriented applications. WCF sends send data as asynchronous messages from one service endpoint to another. A service endpoint can be part of a continuously available service hosted by IIS or an application.

OData is a standardized protocol for creating and consuming data APIs. OData builds on core protocols like HTTP and commonly accepted methodologies like REST

Open Visual Studio (I used 2012) or the free Visual Studio Express and create a new Website. Choose the Empty ASP.NET Web Application template and give it a name.

Open the NuGet package manager and search for a MongoDB OData driver. I used the driver here. More details on the driver can be found here and the source code can be found here.

Install the driver then Open the Web.config and update the connection string to your MongoDB instance using the following format:

connectionString="mongodb://DNS NAME:27017/<yourDBName>"

For convenience I’ve created a repository with a fully baked WCF data service on GitHub.  This code provides a data definition (FAAObjects.cs) and code to serve the Odata service (OdataWcfDataService.cs ).

You need to change two things in this code to make this work in your installation, then publish the code as a WCF Data Service on Azure.

Under App_Code, in OdataWcfDataService.cs, change the MongoDB connection string to point to your MongoDB Instance:

 protected override MongoServer CreateMongoServer()

 

    {

        return MongoServer.Create("<your MongoDBconnection String Goes Here - Example: mongodb://URL:27017>");

    }

 

In the OdataWcfDataService.svc file, name the service:

<%@ ServiceHost Language="C#" Factory="System.Data.Services.DataServiceHostFactory" Service="<your service Name goes here>" %>

Next, log in to Windows Azure and download a publishing profile.  In Visual Studio, use the publish website option to import your publishing profile.  Leave all default settings and click publish.

That’s it! You just created an OData service using WCF.

To access your service, the URL is:

http://<YourWCFServiceURL/<yourOdataServiceName>.svc

Open Government Data – The FAA Aircraft registry

The FAA Aircraft Registry is a public Aircraft Registration database including 800,000 individual aircraft registrations as well as thousands of aircraft dealers, and a few other data points, updated weekly. Data is shared in comma delimited format (CDF) and can be manipulated by common database management applications.

The data here is just for illustration purposes, so I won’t spend a lot of time describing the data itself. To follow along, you can use any data source you like. For the tutorial I’ll assume we’re starting with a MongoDB instance on Windows Azure with some data loaded.

The Data in MongoDB can be split into one or more collections. I created several collections based on the original files downloaded from the FAA site. When viewed by an OData client, each one of these collections will look like a separate table in a database. I used the mongoImport command to load each collection from the downloaded comma-delimited format files.

Viewing Data with LINQPad

We’re now ready to test our connection all the way though from MongoDb on Windows azure to a client via OData. One of the quickest and easiest free ways to do this is with LINQPad, a free tool originally created to help interactively query SQL server, but has been expanded as a C#/VB/F# scratchpad that instantly executes any expression, and more importantly has an easy to use connection interface to OData. To connect to your WCF OData service, click on Add Connection, then choose the WCF Data Service option as shown here:

clip_image024[4]

Add your connection URL in the next screen, and leave all the rest of the options the way they are. Press the test button to test your connection, then OK to save it:

clip_image025[4]

Now you can browse the data using LINQPad. Here’s an example of the FAA Aircraft database’s DEALER_Data table, showing the first 100 rows:

clip_image027[4]

Visualizing OData using PowerBI

To visualize our data we’ll be using Power Query and Power Map, two key features of PowerBI. PowerBI is a set of very useful self-service business intelligence tools that are added on to Microsoft Excel to discover, combine, refine and visualize your data. You can also share your results using PowerBI for Office 365.

In the sample data set I’m using, there are 7,606 Data records of Aircraft dealers in the USA. For this example I’m going to order and group dealers by State, then put a count of dealers by State on a Map.

Open a new spreadsheet in Excel, then select the Power Query Tab. Select From Other Sources then From OData Feed:

clip_image028[4]

Enter the URL for your WCF OData Service:

clip_image030[4]

For this example, we want to access the DEALER_Data table. On the right use the Navigator to select the dealer table, and hover over it to see a preview of the dealer table data:

clip_image032[4]

Double Click the table to open the Table in Edit Mode. Next, select Group and choose the STATE_ABBREV column to Group By and Count Rows for the Operation.

clip_image034[4]

The Query that gets generated for this is:

= Table.Group(DEALER_Data1, {"STATE_ABBREV"}, {{"Count", each Table.RowCount(_), type number}})

And here’s the raw data in the Query results:

clip_image035[4]

Mapping the Data with PowerMap

Next, we launch PowerMap by selecting Insert > Map > Launch Power Map

clip_image036[4]

In the first screen, we define which column indicated Geography for the Map by choosing the STATE_ABBREV column, then selecting the Next button on the bottom right of the screen. Note that PowerMap already recognized the data I was selecting as US State abbreviations, and oriented the map accordingly:

clip_image038[4]

In this screen we choose what will be displayed and how the display will be formatted, For this example I chose to display results as a bubble, and to make the US Map flat, both menu options.

clip_image040[4]

There are several other mapping options, I invite you to play around with the data and see what you come up with yourself, and share your results using PowerBI for Office365!

Conclusion

In this example I’ve shown you how to develop a solution that makes MongoDB data on Windows Azure available to OData client applications via WCF, and I’ve shown you how to use Microsoft PowerBI to easily visualize and map that data. This is a very simple example of visualization, here are more samples to think about when considering how to visualize data. Also, note that there is an online search function built-in to Power Query that helps you easily find open data from other sources and integrate it into your queries and visualizations.

Lastly, please let us know if there’s anything else related that you’d like to see or any similar techniques you’ve implemented – we value your feedback!

http://<YourWCFServiceURL/<yourOdataServiceName>.svc/


11 thoughts on “Tutorial: Using MongoDB, Windows Communication Foundation, OData, and PowerBI to build a Business Intelligence solution on Windows Azure

  1. Pingback: Dew Drop – February 20, 2014 (#1727) | Morning Dew

  2. Pingback: February 22 is International Open Data Day – Celebrate with our Tutorial or create your own! - Interoperability @ Microsoft - Site Home - MSDN Blogs

  3. After ramming through all the issues, I finally get to the last step, and then I get this:

    Unable to connect to server xxxxxx.cloudapp.net:27017: Object reference not set to an instance of an object..

    My connection string looks like this:

    add name=”MongoDB” connectionString=”mongodb://xxxxxx.cloudapp.net:27017/testdb?safe=true”

    What should I enable?

  4. Pingback: MongoLab ofrece nuevas opciones para desarrolladores de MongoDB en Azure | Espacio Microsoft

  5. Pingback: Microsoft, presente en el Día Internacional del Open Data para apoyar la transparencia y el crecimiento económico local | Espacio Microsoft

  6. Hi,
    I can get a count to work but nothing else. When I try to list top 100 I get :-

    Cannot deserialize string from BsonType ObjectId

    ANy Ideas?

  7. Hi,
    I am getting an error when trying to list top 100.
    An error occurred while deserializing the Id property of class FAAObjects+ACFTREF_Table_Record: Cannot deserialize string from BsonType ObjectId.

    Any ideas??

Leave a Reply