I have been testing the power bi SQL connector. It works great on a dataset level, however when I try to connect in a Power BI Dataflow or Datamart I get this error everytime;
An exception occurred: The given data source kind is not supported. Data source kind: MongoDBAtlasODBC.
Is this a issue with the connector? It would be good to get dataflows working with the connector as we could then use incremental refreshes, as currently powerbi with ODBC connection does a full COLSCAN on every refresh.
Hi there @Colin_Mallon welcome to the community and thanks for posting. My name is Alexi Antonino and I am the Product Manager for Atlas SQL and the new customer Power BI Connector. I have not tried the connector with DataFlows or Datamart yet. I will try it out and see what I can figure out. I will see if I can get some time today or tomorrow to explore.
Based on the error you have reported, it would indicate that Dataflow does not work with our ODBC driver. But it might just be that it can’t be accessed or something to that nature. I will let you know what I find.
Hi There - I am working on getting this to work. I have a message into MS to ask if the data in my collection already needs to be flattened as I keep getting an error: An exception occurred: We cannot convert a value of type Record to type Text.
It is my expectation that our connector works with Dataflows, but I need to see if it can accept our nested data as is, or some upstream transformation is necessary (based on the error I am getting, this is what I am thinking).
I will let you know what what I find out.
Good News! I was able to get this to work. Let me document the steps I took and add them here for you. It might take me a bit to recreate this as I was trying a lot of different things, so give me a few hours and I will let you know how I got this working.
@Alexander_Najem and @Colin_Mallon
As mentioned in my last post, I was able to get Dataflows working with our new MongoDB Atlas SQL Connector. I will discuss with Microsoft if this approach is correct, because while it’s working, the steps I took were a bit out of the ordinary. I got it working with “Blank Query” as opposed to connecting to the database and selecting a table from the navigation list. And it might be that the navigation list (list of databases and tables) is just not supported with our connector just yet (more to come on this). Here are the steps for connection:
Power BI DataFlows
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 (you will have already set up the on-premise gateway within Power BI App/Service)
Enter in an M and SQL statement to access your MongoDB Atlas Data (See my example below)
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
Hi @Colin_Mallon Have you gotten the gateway to work prior to trying it with Dataflows? I want to understand if this error is coming from the on-premise gateway or is Dataflows specific. Also, if you can tell me when you get this error (list your steps or based on the ones I provided above tell me at which point this error occurs).
And finally, you may want to make sure you are running the most up to date versions of the connector and ODBC driver - you can check our download center to verify.
If you’d like to share any screenshots or need to provide information that you don’t want on the public forum, feel free to email me as well: alexi.antonino@mongodb.com
I have now got it working thank you!
I had the most up to date ODBC connector but had 1.0.0 custom powerbi connecter and not 1.0.1.
Updating solved this.
@Alexi_Antonino Amazing that you responded and got this working!!! One additional question, can this connector be used with on premise MongoDB Installations?
@Alexander_Najem This SQL Interface connector and the driver only support Atlas at this point in time. Our existing on-premise BI Connector does work with Power BI, but I don’t know if it would work with DataFlows - I can check. Our on-premise BI Connector does support the on-premise gateway though (so that is half of the equation).
Thanks @Alexi_Antonino We already use the existing connector for on-premise (via data flows and via on-prem gateway) it works fine, but its very kludgy, and difficult to use and maintain, we were hoping for a more modern solution (which this new connector seems to be). It would be nice if there was a unified way to work with hybrid environments so we didn’t need to learn 2 solutions, but we’ll take whatever we can get.
@Alexander_Najem thanks for the feedback. I will definitely thank this into consideration when deciding what the eventual replacement of on-prem BI Connector looks like.
Thanks @Alexi_Antonino for this information, it helped me solve my issue.
After finally getting the gateway setup properly with the connector, I was still getting the error message “We cannot convert a value of type Record to type Text” every time I tried to to refresh my published dataset on PowerBI cloud/service. I converted all my Power Query tables to use your method instead of using the navigation list. After re-publishing, it refreshed with no errors!
@Rob_Shuter I’m so glad this was helpful. I need to understand why the typical connection to the db using the navigation list doesn’t work and the blank query does. Hoping to get that answered soon.
