Introduction

In today’s data-driven world, making informed decisions swiftly is essential for businesses of all sizes. This is where Business Intelligence (BI) tools like Power BI come into play. Power BI empowers organizations to collect, analyze, and visualize data from different sources in real-time. As businesses increasingly rely on cloud-based infrastructure, the need for seamless integration between BI tools and databases like MongoDB Atlas has grown substantially.

Recently, I stumbled upon several posts online about people facing difficulties in connecting their Power BI Desktop to MongoDB Atlas. I encountered the same problem too, and after spending two days troubleshooting and looking for answers online, I managed to solve it.

This article offers a detailed walkthrough on how to link Power BI Desktop to MongoDB Atlas. By the end of this guide, you'll have a fully functional connection that enables real-time data analytics, helping you make the most of both tools' powerful capabilities.

A little Background

In Power BI, there are two methods of connecting to MongoDB. The first is with the ODBC (Open Database Connection), and the second is through the Power BI connector for MongoDB Atlas.

These two methods are part of the Atlas SQL connection option. Using the Atlas SQL interface, you can use SQL-style queries to search data on a federated database instance. This capability allows you to visualize, graph, and report on your Atlas data using relational BI tools such as Power BI and Tableau.

Atlas Data Federation is a distributed query engine that allows you to natively query, transform, and move data across various sources inside & outside of MongoDB Atlas. Details about how to set up a federated database are on the MongoDB Atlas website.

For a Power BI connection to be successful, your system must have the MongoDB Atlas ODBC DSN (Data Source Name) configured correctly.

Before we get started, you should:

1. Ensure you have an active MongoDB Atlas account. You can create one on the MongoDB Atlas website.

2. Make sure you have a cluster set up in MongoDB Atlas. If you don’t have a cluster yet, MongoDB provides easy-to-follow instructions for creating a free-tier cluster.

3. Create a user with the necessary privileges in the database access section in MongoDB Atlas.

4. Have the latest version of Power BI installed.

Now let's get the necessary drivers ready.

Download the following drivers from the MongoDB website.

a. MongoDB Power BI connector (Latest Version) - here

b. MongoDB ODBC driver (64/32 bits) - here

c. Atlas SQL ODBC

Next, we'll set up the drivers.

a. MongoDB Power BI connector.

Create a "custom connector" folder for Power BI (If it doesn't exist) in the following path:

C:\Users<user>\Documents\Power BI Desktop\Custom Connectors.

Move the Power BI connector to this folder.

b. Install and configure the MongoDB ODBC driver (64-bit or 32-bit)

Doing this will add a MongoDB data source name (DSN) to your system data sources.

Note: You can install both drivers if you're unsure whether you use a 64-bit or 32-bit PC.

To configure the driver.

1. Search for ODBC in your start menu and open it.

2. Go to system DSN and click on add

3. Select the ANSI driver.

4. Enter a name for your data source

5. Enter a hostname (TCP/IP) - 127.0.0.1

6. Enter the port number - 3307

7. Add a username and password for the DSN and click okay.

c. Install and configure the Atlas SQL ODBC

Note: This driver is needed to connect Atlas SQL to external sources.

1. Enter the data source name you created in the previous step.

2. Add the username and password of the user you created in your atlas cluster.

3. Enter the connection string for MongoDB Atlas SQL. You can get it by clicking Connect on the cluster page and selecting the Atlas SQL option. This will create a federated database instance for you automatically.

4. Enter the database name.

5. You can test your connection to ensure it works.

Finally!!! Time to bring everything together

1. Open Power BI and select Get Data

2. Search for MongoDB and click Connect.

   

3. Enter your connection string and database name.

   

4. Enter your username and password then click OK.

Congratulations!!! You have successfully connected your Power BI to MongoDB. You can now ETL data from MongoDB into Power BI for real-time analytics and reporting.