Connect to MongoDB from a BI tool

How to connect to MongoDB from a BI tool such as Reporting Services or Power BI, which needs an ODBC driver that accepts SQL queries.

You want to use your reporting or dashboard tool — SQL Server Reporting Services or Power BI — against MongoDB. How do you go about it?

These tools send SQL queries to data servers that speak SQL. MongoDB, however, is a document-oriented database engine that handles JSON through a proprietary query language, entirely different from SQL. Is there a way round that?

To bridge the gap, MongoDB developed an intermediate layer called the MongoDB Connector for BI.

The connector acts as a go-between. It is a server-side component that answers an ODBC driver and reads SQL queries. It translates each query straight into the MongoDB language, and formats the result as a tabular result set.

You can use it as a cloud service, with MongoDB’s cloud offering Atlas, or download and install it on-premises.

The installation is documented here.

The connector is available for several platforms. It is a daemon named mongosqld, configured through a YAML file. The configuration reference is here.

SQL Server Expertise Need help with SQL Server? Contact me