MongoDB And BI: Friends Or Foes?

MongoDB is a popular NoSQL database, which stores data using a JSON-like document format. MongoDB was designed to be extremely convenient for application developers and compatible with JavaScript-based application architectures.

MongoDB can be monitored using key metric categories like throughput, resource utilization, database performance, and more. A MongoDB dashboard is used to visualize metrics related to MongoDB cluster usage and performance.

But it’s much less convenient for business intelligence (BI) purposes—MongoDB BI access is not offered out of the box and is not trivial, because MongoDB does not have a SQL interface.

MongoDB and Non-Relational Databases—a Throwback to the Days Before SQL?

Many years ago, when applications were written using legacy frameworks like FORTRAN and COBOL, data structures were not easily accessible. To extract data from a program, users had to request custom development.

With the advent of SQL, and modern software which organized itself around a centralized SQL-compatible database, data access became much easier, even taken for granted. Individual users could extract data directly using SQL queries, or connect BI tools to explore and report on application data.

MongoDB, and other NoSQL databases which organize data in innovative ways, are in a way a throwback to the old days. There is no longer a built-in interface to the data, and plugging in a BI tool requires a “bridge” that can translate the data format to SQL.

Are MongoDB and Other NoSQL DBs Simply not Suited for Data Warehousing and BI?

Putting aside the inconvenience of having to translate from JSON to SQL to connect MongoDB to a BI system—is MongoDB and other similar databases just not built for large scale data warehousing and analysis?

Most NoSQL databases rely on the concept of key-value pairs, which are sharded into nodes based on a unique key. In a system like MongoDB, doing a full table scan—which is required for many analytical queries—is a very expensive operation. Aggregating data, which is also commonly required for BI and analytics, requires Map/Reduce or other techniques that can pull together data from multiple, disparate nodes.

Many experts agree that while MongoDB can support BI, it is not a first choice for use as a data warehouse supporting analytics and reporting operations. MongoDB is first and foremost an OLTP database, and should be considered with care for use in an OLAP solution.

What is the MongoDB BI Connector?

Having said all that, MongoDB does provide a solution for translating JSON to SQL and enabling direct access via BI tools—the MongoDB BI Connector.

The BI Connector provides your BI platform with information about the schema of the relevant MongoDB collection, receives SQL queries from the BI platform, and translates them into appropriate MongoDB queries. It then returns the results in an SQL format that the BI platform can consume.

Image Source

The BI Connector is certified for use with Tableau Desktop, Microsoft Power BI and IBM Cognos, and can also work with Excel, MySQL Client, Qlik Sense, MicroStrategy Desktop and Spotfire Cloud. See a list of connection tutorials provided by MongoDB.

The BI Connector is not available as part of the open source MongoDB product; it is offered as part of the paid MongoDB Enterprise Advanced offering.

Alternatives to MongoDB BI Connector

A new category of tools is emerging, which connects NoSQL databases, including MongoDB, to BI platforms. These can be a viable alternative if you don’t need, or can’t afford, the full MongoDB Enterprise Advanced package, or if you have additional NoSQL databases to connect to your BI.

A few popular solutions are:

  • Stitch—an ETL service built for data teams, Stitch works with both non-relational data sources like MongoDB and relational sources such as and MySQL and Postgres. Stitch stores user data in the user’s warehouse quickly, eliminating the need for API maintenance, cron jobs, scripting or JSON wrangling.

  • SlamData - allows you to connect to NoSQL data sources, select the data that interests you, easily define aggregations or other data operations, and pipe the data to a BI tool of your choice.

  • FiveTran—an ETL tool which connects applications, databases, websites and servers to a central data warehouse without a need for coding. FiveTran works with relational data sources such as and MySQL and Postgres and non-relational data sources such as MongoDB.

  • Knowi - Knowi works with both non-relational data sources like MongoDB and relational sources such as MySQL and Postgres. Unlike the other tools, it offers BI and machine learning capabilities of its own, letting you run queries and analyses across all your data sources.

Challenges Remain: MongoDB is Not a Natural Fit for BI

Accessing MongoDB directly by BI tools is now possible, but is far from ideal. It’s a bit like reading a French newspaper in English using Google Translate. You can do it, but it’s not a lot of fun, and you won’t want to do it daily. At some point you’ll expect the newspaper to provide an English version.

Similarly, BI tools can rely on a translated version of your JSON-based MongoDB data, but at some point you might want to give them a native format they can more easily access and analyze.

To ensure data is represented in a format most suitable for analysis, and already converted to relational format, you can pipe MongoDB data into a SQL-based data warehouse.

Moving MongoDB Data to an Automated Data Warehouse in Just a Few Clicks

Panoply is a cloud-based, automated data warehouse that comes pre-integrated with MongoDB. You can pull MongoDB data into the data warehouse with one click—it is automatically translated to relational format, and also automatically prepared and cleaned for analysis.

This provides a more streamlined ingestion process, compared to MongoDB BI Connector and other solutions we surveyed. Instead of having BI solutions access data over a “bridge”, they access natively relational data, pre-organized and optimized for analysis.

Get a free consultation with a data architect to see how to build a data warehouse in minutes.
Request Demo
Read more in:
Share this post:

Work smarter, better, and faster with weekly tips and how-tos.