5
read 7 min
Jun 2023
40 / 40
Mar 4
Mar 4
felipegs31felipe scandiffio
Nov 2023

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!

Vincent_Quillet
Nov 2023

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-flow
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 :slight_smile:
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
Nov 2023

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)

image
image918×425 29 KB

Any help would be great.
Thanks
Vincent

Vincent_Quillet
Nov 2023

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

Alexi_AntoninoMongoDB Employee
Nov 2023

@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
Nov 2023

@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_AntoninoMongoDB Employee
Nov 2023

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):

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

  1. Select New->More options
  2. Select Dataflow Gen2 from the “Data Factory” group
  3. At the top of the screen, select “Get data”
  4. Select “More” then enter in the Search bar “Blank Query”
  5. 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
  6. At the bottom of the connection window, make sure your On-Premise Data Gateway is selected from the drop down
  7. Select “Next” (you may need to edit/add in user credentials if they are not saved already)
  8. Power Query Online will open and present you with your data per your sql statement.
Vincent_Quillet
Dec 2023

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

4 months later
Neeraj_Chaudhary1Neeraj Chaudhary
Mar 2024

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”

11 days later
17 days later
t_t1T T
Apr 2024

I was struggling with this issue for a while, until I realised my Power BI installation was 32-bit and not 64-bit. Hope this can help someone.

SIREESHA_TALASILA
Apr 2024

Hi,

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”

2 months later
9 months later
Anitha_SahayaRani
Mar 4

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.


Want to read more? Browse other topics in MongoDB Charts or view latest topics.