MongoDB Atlas Connector <> Power BI Service

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

Vincent_Quillet · 2023-11-24T11:02:35.057Z

Hello
Still having the same issue.
Any help would be great !
Thanks
Regards
Vincent

Alexi_Antonino · 2023-11-27T13:47:07.109Z

@Vincent_Quillet - Is what you are saying this works fine on the Personal Gateway, but not the standard Gateway where you receive this error? Is your standard gateway installed on another computer/server or is it on the same? Do you know if the 2 gateways are running different versions of the Connector/ODBC Driver?

We did recently release a new connector version that handles native sql queries differently. I am wondering if the 2 different gateways are using 2 different connector versions.

Vincent_Quillet · 2023-11-27T15:51:16.561Z

@Alexi_Antonino - Yes it is working fine on the Personnal Gateway but not standard Gateway.
Personnal Gateway is on my developper computer (physical machine Lenovo Thinkpad with Windows 10) and the Standard Gateway is on a Windows server 2022 std (virtual machine)
The connector versions are differents :
Personnal Gateway is MongoDBAtlasODBC-1.0.1.pqx downloaded in october
Standard Gateway is MongoDBAtlasODBC-1.1.2.pqx downloaded in november

I have tried to switch to MongoDBAtlasODBC-1.0.1 on the Standard Gateway but the connexion refresh throws an exception
We were unable to convert a value of type Record to type Text.

I am a little bit stuck while the connection on power bi desktop works perfectly on both gateways

Alexi_Antonino · 2023-11-27T17:26:36.079Z

OK yes I figured they were using 2 different versions. The Windows server has the newer version that supports the native query differently. This is in preparation of the upcoming Direct Query support (Should land sometime next year, Quarter 1). Please make sure the ODBC Driver on the Windows Server is up to date as well.

For this latest version, the “.query” in the M Code it is problematic.Try this instead:

Instructions (selecting the connector):

From Power BI App, navigate to Workspaces (must have a workspace other than “My Workspace)
Select New->More options
Select Dataflow Gen2 from the “Data Factory” group
At the top of the screen, select “Get data”
Select “More” then enter in the Search bar “MongoDB”
Find and paste in your Atlas SQL MongoDB URI, then enter in the Database Name
At the bottom of the connection window, make sure your On-Premise Data Gateway is selected from the drop down
Select “Next” (you may need to edit/add in user credentials if they are not saved already)
You will be presented with the navigation menu to select tables from your virtual database.
Once you select a table(s), you can then transform the data using Power Query Online.

Instructions (selecting blank query):

From Power BI App, navigate to Workspaces (must have a workspace other than “My Workspace)

Select New->More options
Select Dataflow Gen2 from the “Data Factory” group
At the top of the screen, select “Get data”
Select “More” then enter in the Search bar “Blank Query”
In the query window, make sure your query syntax is as follows:
Let
Source = Value.NativeQuery(MongoDBAtlasODBC.Contents(“mongodb://atlassqlsandbox- rotpc.a.query.mongodb.net/Supplies?ssl=true&authSource=admin”, “Supplies”, ). {[Name=“Supplies”,Kind=“Database”]}[Data], “select * from Sales limit 10”, null, [EnableFolding=false])
in
Source
At the bottom of the connection window, make sure your On-Premise Data Gateway is selected from the drop down
Select “Next” (you may need to edit/add in user credentials if they are not saved already)
Power Query Online will open and present you with your data per your sql statement.

Vincent_Quillet · 2023-12-02T17:34:28.260Z

Finally I was able to make the semantic model work correctly in power bi service by removing the custom connector file (MongoDBAtlasODBC-*****.pqx) on the On Premise Gateway).
Now I no longer have the error "cannot convert Binary Type into into Text type " on Text column containing null value(s).
So it happens that following this documentation is not working in my case.
However I would like to investigate further because this is not the kind of situation we like in production.
Waiting for an explanation if there is one.
Thanks

Neeraj_Chaudhary1 · 2024-03-27T09:59:57.883Z

You can install ODBC DRIVER and POWERBI CONNECTOR from mongodb and relaunch the POWERBI application. In my case it worked.

You can get url to connect to database by clicking DATABASE > click on CONNECT tab > get url for ATLAS SQL

You can use this url to configure POWERBI Application.
By default database name is “test”

Donald_Moore_Jr · 2024-04-07T18:36:51.296Z

Thank you so much!!!