MongoDB Atlas Connector <> Power BI Service

Flavia_Santos_de_Almeida · 2023-06-30T13:02:06.538Z

Hi Carlos

I usually download the report of mongodb compass and import the data into google sheets, to use in power bi.

It has been necessary because I still don’t know how to solve this issue of power bi service

Carlos_DI · 2023-06-30T16:25:36.169Z

I see - Thanks for sharing, Flavia!

Flavia_Santos_de_Almeida · 2023-07-03T16:17:31.692Z

Hi everyone!

Any solution…?

Peter_Harrison · 2023-07-24T11:19:38.299Z

Hi, I’m also trying to query Mongo (just a test db at the moment) with Power BI and also getting nowhere, same message “Missing client library for datasource”.

PowerBI (desktop at the moment) asks for a MongoDB URL, and a “database” which as an SQL guy I assume is a tablename. I use the Atlas DB connector string, name the table, and get that result.

Has anyone tried this with Tableau?

Peter_Harrison · 2023-07-24T11:22:15.504Z

And what’s the best way of downloading and importing to google sheets? Mongo newbie here, trying to get to grips with a world where pretty much everything is in the cloud!

Flavia_Santos_de_Almeida · 2023-08-02T14:07:27.803Z

Hi!

I haven’t tried with Tableau…

And answering the second question, I download the reports of mongodb using mongodb compass.

In mongodb compass I can make some queries and then download this queries in a report format, just clicking in a button

Anyway, I haven’t found a solution for the main problem, did anyone have found?

Shuja · 2023-08-21T11:48:10.488Z

Hi,
I am in the same situation and not able to setup refresh using the latest power bi connector. any solutions so far?

SergioMagnettu · 2023-08-22T16:55:02.354Z

Hi everyone, same problem here.

I’m able to connect Power BI desktop to Atlas, but not Power BI Service. Same error message. That’s a shame because I cannot use dataflows and datamarts functionalities.

I have two questions, with the intention to find a workaround for all of us together:

Creating the report in Power BI desktop and publishing it to Power BI Service makes the dataset available in Power BI Service. However, this dataset can be used only for reports. It cannot be consumed from a dataflow or a datamart. So, it’s not a solution. Am I right?
A second approach could be using a third tool as a bridge. Some SQL database that works as a charm when connected to Atlas on one side and Power BI on the other. There should be plenty of solutions. Any recommendation? (preferably free and straightforward)

Some folks above talked about using Compass + Google Sheets this way, but it seems a little manual to me (opening a program, exporting a file, pasting the content to google sheets or whatever it takes…)

I hope we all find a solution.
Thank you!

Joel_Zehring · 2023-08-22T18:15:25.434Z

I’ve been engaged with Microsoft support around this. We’ve had several calls to reproduce the issue, troubleshoot, and collect logging. I’ll update here if they determine a solution.

Akhil_Vadiyala · 2023-08-25T12:42:39.139Z

Hi @Joel_Zehring

Do you have any updates from the Microsoft Team?

Gabriel_Nogueira · 2023-09-04T11:17:35.315Z

Hi Flavia, how are you? I’m Gabriel Nogueira, I’m starting work here at the company with Power BI and we use Mongo DB as a database. I’m having several problems (for being a beginner).

We could make a call, if you can, to understand how you’ve been importing data into Power BI.

Best

Alexi_Antonino · 2023-09-08T12:41:09.722Z

Hi All - I’m the product manager for the Atlas SQL Interface and Power BI Connector.

First up, the error “Missing client library for datasource” most probably means you may not have downloaded and installed the Atlas SQL ODBC driver. Here is the download for that driver: Download Atlas SQL ODBC Driver | MongoDB

Next, when the connection dialog asks for “database” it is referring to a database name, not a table. This is just the database to authenticate against and you will be able to build upon other databases within that cluster/Federated db, so long as you have permissions to those.

Screenshot 2023-09-08 at 8.28.36 AM1280×702 120 KB

And if you want to publish and refresh this data, a Power BI Gateway is required. So if this gateway is on a server, the connector and driver must also reside on that server. You can download the connector and driver from our download center (as mentioned above).

And finally, Data flows does work with this connector+driver+on-premise gateway, here are the requirements and instructions:

Requirements:

On-premise Gateway (installed and configured for the MongoDB Atlas Connector)
MongoDB ODBC Driver (downloaded and installed on same server/pc as Gateway)
Atlas Cluster (or Atlas Datasource)
Atlas Federated DB (if you created a SQL Quickstart this is fine too)
MongoDB URI for connection to your Atlas Federated DB or your SQL Quickstart Federated DB
Power BI Service
Workspace (not “My Workspace”)

Instructions:

From Power BI App, navigate to Workspaces (must have a workspace other than “My Workspace)
Select New->Dataflow
At the top of the screen, select “Add tables”
On the side navigation panel, select “Blank query”
At the bottom of the Query Window, make sure your On-Premise Data Gateway is selected from the drop down
Enter in an M and SQL statement to access your MongoDB Atlas Data

let
Source = MongoDBAtlasODBC.Query(“mongodb://atlassqlsandbox-rotpc.a.query.mongodb.net/Supplies?ssl=true&authSource=admin”, “Supplies”, “select * from Sales”, null)
in
Source

Your table should open up in the online Power Query and now you may transform your data, save and then close it.
You can refresh this data manually or on a scheduled basis

I will monitor this thread, please let me know if you get stuck.
Best,
Alexi

Alejandro_Cortes · 2023-09-19T08:05:11.332Z

This solution works!
Thanks.

Tunde_Morakinyo · 2023-10-25T17:15:46.451Z

Hi Alejandro,

I’d appreciate if you could do provide a step by step way you did it.

I’ve been trying to refresh the power bi service for mongoatlasSQL but to no avail.

Thanks in anticipation.

SergioMagnettu · 2023-10-30T20:00:05.809Z

The steps described by @Alexi_Antonino are working fine for me also. Thank you!!

@Tunde_Morakinyo, the steps described are precise and should work also for you. Let me add some details to try to help.

Install Power BI desktop.
Install and configure the Atlas SQL ODBC Driver as explained here: https://www.youtube.com/watch?v=v8W3lX1BLkY&t=283s

Now you’re ready to fetch and use your MongoDB data from Power BI desktop.

Install On-premise Gateway. In my case, I used this: Install an on-premises data gateway | Microsoft Learn
Jump to Power BI Service and follow instructions provided by Alexi_Antonino.

Pay attention to your M/SQL statement. You must replace the fields inside the statement. The url is the Power BI Connector Connection String that you will find in Atlas (see screenshot provided by Alexi_Antonino). You already used that in step 2. “Supplies” is the Database Name and “Sales” is the specific collection you want to fetch. You probably need to replace the quote markers by " instead of “ and ”.

Hope it helps.

felipegs31 · 2023-11-07T18:51:17.747Z

Hello everyone, I followed steps by @Alexi_Antonino and @SergioMagnettu , I was able to make powerBi work and auto-update with a local gateway (running on my pc), the problem now is how to put all this online in a server

Should I use a service from Azure for that? Do we have any tutorial or guide on how to do that?

With this way of doing things that @Alexi_Antonino proposed, I have now data flows as data source, I already built my reports on PowerBI desktop using MongoDB Atlas SQL (Beta) connector, do I need to rebuild my reports from scratch? Or there is another way of doing that?

From what I understood, if we want a dashboard that the data refreshes automatically, the new MongoDB Atlas SQL (Beta) connector is useless, am I correct?

Thank you for all the Help!

Tunde_Morakinyo · 2023-11-08T06:39:30.508Z

This worked perfectly well. Thank you so much Sergio.

Vincent_Quillet · 2023-11-15T18:43:12.966Z

Hi Alexi,
I am ending up to this error (translated from french language) in the Power Query online interface :
"The MongoDBAtlasODBC.Query import does not match any exports. Did you forget a module reference ?
Any idea ?
And when I try in power query connexion settings, I get this error

power-query-online-connexion1764×301 18 KB

Thanks
Vincent

Vincent_Quillet · 2023-11-17T11:04:02.958Z

Hello
I found that I forgot to install Visual C++ Redistributable for Visual Studio 2015 as described here.

Here is the flow which is not far from working.

pbi-mongo-atlas-odbc-gateway-model-report-flow1289×201 25.7 KB

Now the gateway connexion works but when refreshing the Model, I get this error
Erreur de source de données: {“error”:{“code”:“DM_GWPipeline_Gateway_MashupDataAccessError”,“pbi.error”:{“code”:“DM_GWPipeline_Gateway_MashupDataAccessError”,“parameters”:{},“details”:[{“code”:“DM_ErrorDetailNameCode_UnderlyingErrorCode”,“detail”:{“type”:1,“value”:“-2147467259”}},{“code”:“DM_ErrorDetailNameCode_UnderlyingErrorMessage”,“detail”:{“type”:1,“value”:“Désolé… Nous n’avons pas pu convertir une valeur de type Binary en type Text.”}},{“code”:“DM_ErrorDetailNameCode_UnderlyingHResult”,“detail”:{“type”:1,“value”:“-2147467259”}},{“code”:“Microsoft.Data.Mashup.ValueError.Reason”,“detail”:{“type”:1,“value”:“Expression.Error”}}],“exceptionCulprit”:1}}} Table: tokheimTransactions.
URI du cluster: WABI-FRANCE-CENTRAL-A-PRIMARY-redirect.analysis.windows.net
ID d’activité: 0e704ddf-68c9-4650-933c-5ac41e76e087
ID de demande: ce30e412-256a-09f7-c597-7d4e9deda40b
Heure: 2023-11-17 10:11:06Z

I don’t know why some type fields are detected as binary though they are text.
The same model works perfectly using Power BI desktop installed on the gateway.

Don’t know what to do now.
Any idea ?
Thanks
Vincent

Vincent_Quillet · 2023-11-17T19:39:37.823Z

Hi
Still having the same issue with On premise Gateway.
: cannot convert binary value type in text type.
Surprisingly it works perfectly with On premise Gateway (personal mode)

image918×425 29 KB

Any help would be great.
Thanks
Vincent