Hello @Alexi_Antonino ,
Following the solution you preconize, I manage to make working a Power BI Desktop connexion to MongoDB Atlas using Atlas SQL with Power BI connector installed on my local machine.
Now I would like to deploy to Power BI Service and have data refreshed automaticaly.
I understand that I could do the same kind of connexion using de Power BI Gateway on premise.
So I am wondering if I could use MongoDB Atlas Business Intelligence Connector.
Hello @Vincent_Quillet - welcome to the community. While you may use the BI Connector, it too requires an on-premise gateway when using Power BI Service. Also, Atlas SQL will eventually replace Atlas BI Connector.
With the Atlas SQL Power BI Connector you can download the Connector and Driver and configure your on-premise gateway with this connector. Download and install the Connector and ODBC Driver here.
Here are some steps to follow for the Gateway:
Thanks for the explanation above. However, I followed the instructions on the official documentation pages and had no issues connecting Power BI Desktop to Atlas. But I cant seem to have the same success with Power BI service.
I am having trouble getting to connect using Power Query in the cloud. I am getting the below error message
SQLSTATE: 01000
NativeError: 444
Error message: ODBC: ERROR [01000] The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 03.80 (Session ID: 8bf13af0-bffe-47ba-b745-fdbc64d2c3c3, Region: europe)
Do you think you can help me with a solution for this?
Thanks and regards
Hello @Andy_M!
This error (444) usually gets invoked because of bad user/login credentials or the network IP address has not been whitelisted within Atlas. Because you stated that it worked in Power BI Desktop (you are saying you in the same spot that Royce was?), I am wondering if you need to whitelist the server IP where you have installed the power BI Gateway.
Let me know if you need any guidance on opening up the network access within Atlas. Or if you want some tips on proving out connection. I usually guide users to try to connect to the federated database via Compass or Mongo SH. I can assist you if needed.
Hi Alexi - that would be great. I think I may need guidance on opening the network access within Atlas, but potentially some tips on proving connection would be ideal too.
Here is also my Calendly link so you may schedule some time with me. We can do a screen share and I can get you closer to connection if needed.
And if you email me, I can email you a doc (I can’t attach docs here) that contains a ton of tips and troubleshooting for Power BI. alexi.antonino@mongodb.com
If you are using an M0, this is supported by Atlas SQL, but not the BI Connector. The BI Connector requires M10 or greater. Please verify you are using the Atlas SQL Power BI Connector with our Atlas SQL ODBC driver. Here are the docs to assist you: https://www.mongodb.com/docs/atlas/data-federation/query/sql/powerbi/connect/
@Flavia_Santos_de_Almeida thanks for letting other users know of this. I also want others to know that IP Address 0.0.0.0/0 is great to test connections, but then you can isolate the IP Address (say from your computer or the computer running the BI Tool - like Power BI Desktop) and add that instead of all zeros.
while using direct query form mongo db bi connector in power bi desktop, unable to get data in tabular format, some column level information is missing and also some columns are showing in Json format, can you assist me to over come from this. Thanks in advance.
Hello @Kuppili_Kishore I will answer here, but this isn’t really a “connection” problem as the first post was. It sounds like you connected without a problem, but missing fields or Json fields. If there are fields not showing up, you should manage the SQL Schema within Atlas. You can do this within the the Atlas UI, within the Data Federation tab.
If you want to know how to transform the nested objects and arrays, you can do this using the passthrough SQL on connection or within PowerQuery. Here are some instructions of the mongosql dialect: https://www.mongodb.com/docs/atlas/data-federation/query/sql/query-with-asql-statements/
You will want to use the “Flatten” command or dot notation to split out fields nested within an object into their own columns. Then you will want to use the “Unwind” command to unwind arrays.
Here is some sample mongosql for example:
Select Cast(_id as string) as _id,
couponUsed, customer_age, customer_gender, customer_satisfaction, items_name, items_price as price, items_quantity,
(items_quantity * items_price) as totalPrice,
storeLocation, saleDate, purchaseMethod ,
cast(saleDate as Timestamp)as testDateTimestamp,
SUBSTRING (Cast(DATETRUNC(DAY,saleDate)as string),0,10) as DateOnly
from Flatten(Unwind(Supplies.Sales with Path=>Sales.items))
where items_quantity = 2
limit 100
I am having the same error. I tried your solution to this issue but it did not work. Is it possible to do a zoom call for help. Please advise. Many thanks.