MongoDB BI Connector

In this article we will explore how we can use Business Intelligence tools like PowerBI to explore MongoDB data and create powerful reports. BI tools are build for visualising Tabular data, but how can it support Mongo’s Document data? MongoDB provides BI connectors for this purpose.

Architecture

Credit : MongoDB

BI Connector acts as a mediator between the mongos/mongod and BI Tools. The BI Tool will communicate using SQL queries, the BI Connector translate them into MongoDB native queries with the aggregation framework and return the data. It doesn’t have any data stored in this layer. Below are the components in BI Connector.

• mongodrdl generates database schema information for use with the MongoDB Connector for BI.
• mongosqld proxies incoming queries between SQL clients and a MongoDB server.
• mongotranslate translates a given SQL query into a MongoDB aggregation pipeline.

DSN is holds the connection configuration data and can be used by BI tools as dataset.

Prerequisite

1. PowerBI for Desktop
2. Mongo Installed with sample data

Configuration Steps

Step 1: Install MongoDB Connector for BI from the below location.

Step 2 : Launch BI connector

mongosqldconnects MongoDB instance to Power BI. It requires a data schema for mapping MongoDB collections and databases. There are multiple options for launching and creating a schema.

mongosqld --schemaSource training

It reads all the database and collections and waits for connection at port 3307

Step 3 : Create System DSN.

Download and install Mongo ODBC driver from

https://docs.mongodb.com/bi-connector/master/reference/odbc-driver/

After Successful installation create System DSN

Configure the Connection Details. Use the values from the last step. Our Mongo BI Connector is listening in port 3307. So Configure the Data Source Name, IP Address and Port.

Test the configuration, it should now be successfully connected.

Step 4 : Connect using Power BI to the configured System DSN

Select ODBC as Data Source

Use the System DSN Configured in the last step as ODBC source

After setting up the ODBC source. The powerBI navigator will show all the collection and the data.

BI Connector Hosting options for Atlas

Atlas comes with BI connector installed for m10 and above. As a part of cluster set up the user needs to enable BI Connector under Additional settings.

For MongoDB Atlas deployment smaller than M10, BI Connectors can be installed locally and mongosqld process can be started with remote database address using --mongo-uri option, following the same steps as we did for mongo instance not hosted in Atlas.