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?
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
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
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)
@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.
@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
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.
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
Do you have any other suggestions on how to fix this error? I’m trying to connect from Power BI desktop to Mongodb using Atlas SQL Connector and running to this same error? I have tried the solutions you have provided earlier, but nothing seem to work. Can we connect pls?
Details: “Data source error occurred.
SQLSTATE: 01000
NativeError: 444
Error message: ODBC: ERROR [01000] The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 03.80”
This error usually occurs because of problems with the connection. So it could be your user credentials (bad password) or as @Flavia_Santos_de_Almeida mentions your IP Address needs to be whitelisted. Also, Power BI holds this error in cache, so even if you correct the problem, you will need to flush cache to continue.
It seems like the issue persists even after installing the ODBC driver and MongoDB BI Connector. The error message still indicates Missing client library for datasource
Visit MongoDB Atlas SQL Interface | MongoDB for more information. »
I’m attempting to establish this connection in a virtual machine (Parallels), but I’m encountering the same error described in this post. Do you have any workaround for this issue?
I have a use case that, my mongo DB is in cloud , and i have used power bi connector account and inside Power BI Desktop , Native SQL statements were used to create a dataset/semantic model. When i try to schedule inside power bi service, i am getting an error like , unsupported data source. Without power bi gateway, is there a provision to enable a data refresh scheduler inside power bi service with cloud based solution. let
Source = Value.NativeQuery(MongoDBAtlasODBC.Contents(“mongodb://atlas-sql-xxxxx?ssl=true&authSource=admin”, “xxxx”, ){[Name=“xxxx”,Kind=“Database”]}[Data], “select * from xxx”, null, [EnableFolding=true])
in
Source
For cloud based Mongo DB, looking for complete cloud solution. Please support.
