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
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
- PowerBI for Desktop
- Mongo Installed with sample data
Configuration Steps
Step 1: Install MongoDB Connector for BI from the below location.
Step 2 : Launch BI connector
mongosqld
connects 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
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.