BI Tools with MongoDB

Generating MongoDB Reports – Using MongoDB Directly As a Source

The key feature in MongoDB allowing reports to be created is the MongoDB Connector for Business Intelligence (also called BI Connector). All the popular BI tools available are only designed to work with flat schema or relational data models. This presents a problem as mongodb follows a schemaless or flexible schema model which the BI tools don’t understand.

BI Connector solves this issue by allowing your traditional BI tools to use SQL queries to visualize, graph, or generate reports on your MongoDB data. It acts as an intermediary between your BI tools and translates the SQL queries from BI tools, pulls the data out from Mongo, and presents the data in a form that the BI tools can interpret. It is important to note that the BI connector itself does not store any data off of mongo.

An interesting alternative to using BI connector and traditional BI tools is to use Mongo charts but it’s only available for Atlas users. Mongo charts for on-prem were discontinued on September 21 and are no longer supported.

In today’s blog post I will summarize the functions of MongoDB Connector and in a subsequent part II blog post will provide an in-depth tutorial on Configuring the BI connector for any of the traditional BI tools.

 Some of the BI tools that can be used to generate MongoDB Reports  using SQL include:

• MySQL Workbench
• Microsoft Excel
• Microstrategy
• Tableau Desktop
• Microsoft PowerBI
• QlikSense
• Spotfire Cloud
• Looker

MongoDB BI Connector Components

The system components for the BI Connector deployment are:

• BI Tools specified above like Microsoft PowerBI or Tableau
• MongoDB database can be accessed from the Atlas interface or from the backend as a database as a service.
• BI Connector that makes the relationship and translates the SQL queries between the BI Tools and MongoDB
• JDBC or ODBC drivers that represent the connection method between the BI Tools and MongoDB database

MongoDB BI connector is available for MongoDB Enterprise however the feature can be used also by the Community version as an evaluation. Details can be checked in the LICENSE file when installing the BI Connector.

How To Access MongoDB Using BI Tools

  MongoDB BI connector can be accessed in multiple ways :

1. Directly from Atlas interface. The BI Connector can be enabled when creating a cluster in Atlas.

After creating the cluster you can connect to the BI Connector by clicking the Connect button of your cluster and then clicking Connect Your Business Intelligence Tool.

Note: BI connector can be enabled only on clusters equal to or greater than M10 because it is running operations that are highly consuming for CPU and memory usage.

Below you can see the steps of how to connect to the MongoDB BI connector in the Atlas interface:

https://www.mongodb.com/docs/atlas/bi-connection/?_ga=2.110236742.271177537.1671525883-1536000472.1663229415&_gac=1.22500809.1669718435.Cj0KCQiA-JacBhC0ARIsAIxybyNhRlqR88TRAvcMxrqXsxuDQDM7PYpt2dTCyr4waBVVdP0kkIRWfpIaAl0VEALw_wcB#std-label-bi-connection

2. BI Connector can also be installed locally on your computer on different platforms like Windows, macOS, Red Hat, and Debian.

See the below link as a reference for the desired operating system:

https://www.mongodb.com/docs/bi-connector/v2.13/installation/#std-label-installation-guides

3. MongoDB BI Connector can be also deployed to MongoDB Cloud Manager and MongoDB Ops Manager

Below you can find the steps for deploying :

https://www.mongodb.com/docs/cloud-manager/tutorial/deploy-bi-connector/

and

https://www.mongodb.com/docs/ops-manager/current/tutorial/deploy-bi-connector/

What is Mongosqld?

Mongosqld is the BI Connector service running on the server that connects MongoDB to BI tools. After BI Connector is installed on your operating system then the Mongosqld should be configured and up and running before being able to connect with the BI tools.

In the below link you can find how to start and configure Mongosqld:

https://www.mongodb.com/docs/bi-connector/current/launch/#std-label-msqld-as-a-system-service

One of the BI Tools can be connected after installing and configuring of MongoDB BI Connector. For connecting one of the BI Tools you can use ODBC or JDBC drivers (see below flow).

How BI Connector runs in the backend

As a reference guide below you can find how you can connect through the ODBC driver for some of the BI Tools:

https://www.mongodb.com/docs/bi-connector/current/client-applications/

Authentication

When using MongoDB BI Connector is recommended to use an authentication mechanism in order to avoid unauthorized access.

The authentication mechanisms are:

• SCRAM-SHA-1
• SCRAM-SHA-256
• PLAIN (LDAP)
• GSSAPI (Kerberos)

When the BI Connector(Mongosqld) is connecting to the MongoDB database it requires the admin user credentials in order to generate the BI connector schema.

Below you can find how you can enable the authentication mechanism for the BI connector :

https://www.mongodb.com/docs/bi-connector/current/authentication/

 Advantages of using MongoDB BI Connector

• It’s a native MongoDB tool that allows users to visualize data by performing SQL queries with their favorite BI tool
• It’s compatible with many BI tools
• It translates the data directly from mongo and presents it to the BI tools without the need for complex ETL processes or intermediate pipelines