Access MongoDB from Power BI via MongoDB ODBC Driver
In this guide, we will walk you through connecting Microsoft Power BI to a MongoDB DataSet using our MongoDB Certified ODBC driver.
Join the DZone community and get the full member experience.Join For Free
Microsoft Power BI enables rich data visualization of your company’s data through its suite of analytics tools, letting you easily analyze and share insights in any device. Here’s how to take this awesome BI tool and integrate it with MongoDB data to take it to the next level.
Note: This guide assumes you have MongoDB installed with the “Restaurants” MongoDB Data Set. The “Restaurants” MongoDB Data Set can be found here.
Determine whether your version of PowerBI is 32bit or 64bit. Then, download + install the corresponding free 15 day trial of the DataDirect for MongoDB ODBC driver. Navigate to the Progress DataDirect for ODBC Installation directory and click on the ODBC Administrator. Click on User or System DSN and click Add:
Select DataDirect 8.0 MongoDB. Configure the Data Sources by filling out a Data Source Name, the Host Name, Port Number, and which Database within MongoDB you want to connect to (you may choose to leave this blank if you prefer). The Schema Definition Path will be automatically generated for you, but feel free to click on the Browse button in order to change the location of the schema or rename it.
Schema Tool Note: The DataDirect MongoDB 8.0 driver will automatically generate a schema for you without you having to open up the Schema Tool! Click on Test Connect and ensure that your connection is established.
Launch Microsoft Power BI and select New Report:
Select Get Data > Other > ODBC and click Connect:
- Select your MongoDB Data Source Name from the drop down window and select OK:From the Navigator pane, select the MongoDB data you would like to use in PowerBI. In our case, we are selecting Restaurants and Restaurants_Address for our Visualization. Select Load.
You can now see your selected tables in PowerBI on the right-hand side of the screen under Fields.
Next, select the Fields from your MongoDB that you’d like to use in your Visualization. In our case, we are going to select Restaurants (Cuisine) and Restaurants_Address (Zipcode).
Then, select the Filled Map Visualization indicated by this icon:
You will then see your data transformed into a map visualization representing all of the different types of cuisines located in our geographic area (in this case, the greater New York City area).
Suppose we wanted to count the different types of restaurants by cuisine in each zip code? We can easily accomplish this, adding a “New Measure” by selecting the New Measure icon from the toolbar under Calculations. Our New Measure is going to count the number of restaurants in each zip code for us. So we will use Measure = DISTINCTCOUNT(RESTAURANTS[NAME]) Our map now reflects the count of each type of Cuisine by zip code.
Your Turn to Start Integrating
Your turn! At this point, you should have access to all your MongoDB data from the familiar interface of PowerBI, and you’ve done it with the proven performance of Progress DataDirect for MongoDB ODBC driver. If you have more data sources you would like to connect, what are you waiting for? Head to our website to see the full suite of DataDirect connectors and start integrating!
Published at DZone with permission of , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.