MongoDB Atlas SQL interface

Note

This connector is owned and provided by MongoDB.

Summary

Property Value
Connector MongoDB Atlas SQL
Products Power BI (semantic models)
Power Query Online
Release state General availability
Category Database
Authentication kinds Basic
Data source kind MongoDBAtlasODBC
Function Reference Documentation MongoDBAtlasODBC.Contents
Support link Support

Note

When using authentication mechanisms other than Username/Password (such as X.509 Certificates, OAuth (OIDC), or AWS IAM), you can leave the username and password fields blank in the connection dialogue. The appropriate credentials are handled through the chosen authentication method.

Prerequisites

To use the MongoDB Atlas SQL connector, you must have an Atlas federated database setup.

Obtaining connection information for your federated database instance

  1. Navigate to your federated database instance. In Atlas, select Data Federation from the left navigation panel.
  2. Select Connect to open the federated database instance connection modal.
  3. Select Connect using the Atlas SQL Interface.
  4. Select Power BI Connector.
  5. Copy your federated database name and MongoDB URI. You need them in a later step.

Note

If some or all of your data comes from an Atlas cluster, you must use MongoDB version 5.0 or greater for that cluster to take advantage of Atlas SQL.

The MongoDB Atlas SQL ODBC Driver is required to use the MongoDB Atlas SQL Connector.

Supported capabilities

  • Import
  • DirectQuery (Power BI semantic models)

Parameters

Entry point: MongoDBAtlasODBC.Contents

Required parameter count: 2

Parameter Display name Required Type Description Sample values Nullable
mongodbUri MongoDB URI Yes text MongoDB connection URI "mongodb://hostname:port/?ssl=true&authSource=admin" false
database Database Yes text Database to connect to "db" false
options No nullable record true

Connect from Power Query Desktop

To connect using the Atlas SQL interface:

  1. Select Get Data from the Home ribbon in Power BI Desktop.

  2. Select Database from the categories on the left, select MongoDB Atlas SQL, and then select Connect.

    Screenshot of the Get data dialog with the MongoDB Atlas SQL connector selected.

  3. If you're connecting to the MongoDB Atlas SQL connector for the first time, a third-party notice is displayed. Select "Don't warn me again with this connector" if you don't want this message to be displayed again.

    Select Continue.

  4. In the MongoDB Atlas SQL window that appears, fill in the following values:

    • The MongoDB URI. Required

      Use the MongoDB URI obtained in the prerequisites. Make sure that it doesn't contain your username and password. URIs containing username and/or passwords are rejected.

    • Your federated Database name. Required

      Use the name of the federated database obtained in the prerequisites.

    • A SQL query. Optional

      To execute immediately, enter a native Atlas SQL query. If the Database is the same as previously used, you can omit it from the query.

      SELECT * FROM orders

    • Select either Import or DirectQuery for your desired Data Connectivity mode.

    Select OK.

    Screenshot of the MongoDB Atlas SQL connection dialog where you enter the connection information.

  5. Enter your Atlas MongoDB Database access username and password and select Connect.

    Screenshot of the MongoDB Atlas SQL authentication dialog where you enter your credentials.

    Note

    Once you enter your username and password for a particular Atlas federated database, Power BI Desktop uses those same credentials in subsequent connection attempts. You can modify those credentials by going to File > Options and settings > Data source settings.

  6. In Navigator, select one or multiple elements to import and use in Power BI Desktop. Then select either Load to load the table in Power BI Desktop, or Transform Data to open the Power Query editor where you can filter and refine the set of data you want to use, and then load that refined set of data into Power BI Desktop.

Connect from Power Query Online

To connect using the Atlas SQL interface:

  1. Select MongoDB Atlas SQL from the Power Query - Choose data source page.

  2. On the Connection settings page, fill in the following values:

    • The MongoDB URI. Required.

      Use the MongoDB URI obtained in the prerequisites. Make sure that it doesn't contain your username and password. URIs containing username and/or passwords are rejected.

    • Your federated Database name. Required

      Use the name of the federated database obtained in the prerequisites.

    • Enter a Connection name.

    • Choose a Data gateway.

    • Enter your Atlas MongoDB Database access username and password and select Next.

    Screenshot of the online Connect to data source dialog where you enter the connection settings.

  3. In the Navigator screen, select the data you require, and then select Transform data. This selection opens the Power Query editor so that you can filter and refine the set of data you want to use.

    Screenshot of the online Navigator where you choose the data you want to transform.

Troubleshooting

When the connection can't be established successfully, the generic error message The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 03.80 is displayed. Start by checking your credentials and that you have no network issues accessing your federated database.

Related content

You might also find the following information useful:

  • Last updated on