I am a beginner working on a project in which I aim to connect Power BI to MongoDB Atlas using the MongoDB Atlas SQL interface. Within the Power BI Desktop environment, everything functions seamlessly; I am able to retrieve and transform data without any issues. However, the challenge arises when I publish my report to the Power BI Service. When attempting to set up a scheduled refresh, I’m met with the following error:
For clarity, here’s the M code I utilize to fetch my data:
let
Source = MongoDBAtlasODBC.Contents("mongodb://-ftpuq.a.query.mongodb.net/?ssl=true&authSource=admin", "db", null, []),
db = Source{[Name="collection",Kind="Database"]}[Data],
properties_COPY_Table = scrapy_quibble_Database{[Name="properties_COPY",Kind="Table"]}[Data]
in
properties_COPY_Table
Being relatively new to this, I’m seeking guidance. Has anyone encountered a similar issue or have insights on potential solutions or workarounds? Your assistance would be invaluable and greatly appreciated.
Hi There - thanks for posting your question. You must install the on-premise data gateway to use the Power BI refresh Service. Also, on the computer/server where the MS Data Gateway is installed and configured, you must also have the MongoDB Connector and ODBC Driver Installed. You can download both of these from our download center.
Here are some instructions that should help you through that:
Once the Gateway is installed and configured, you then go into the Power BI Web settings for the data set and attach the Gateway. Then you can refresh on demand or on a schedule.
Yes I do know why some fields may be missing. We generate a sql schema so that Power BI (or any relational tool) can take our MongoDB Schema and represent it in a relational way. When we automatically generate the SQL Schema, we take a small sample size of your documents to build that schema. And this sample may not represent all of your fields. So you have the option to manually generate the SQL Schema which gives you more control over the schema and your specific data needs. We allow users to generate the sql schema, get the current schema and set the schema. While users do this in Mongo Shell today, we plan to release some UI that will allow users to do this from Atlas in the future.
Here is some information on how you would regenerate the SQL Schema:
Hello @Sidney_Guaro I will try to answer these questions:
Can I safely assume that the scheduled refresh will proceed even if my laptop is turned off? your laptop needs to be on if this is where the gateway is installed.
The concern with this approach is that our collection contains over approximately 726 million documents. Wouldn’t that pose a problem? The import mode of data connection in Power BI may limit your data set. To combat this, your initial query (created within Power Query) should filter your data as much as possible. When we support the direct query mode, which connects to the database live (coming in 2024) you will still want a query to efficiently narrow the data, otherwise it will take a long time to execute the report or dashboard.
Alternatively, you can create views within MongoDB to limit/filter the data so it is a more targeted dataset for the report author as well.
will it work online? Our custom connector will work with Power Query online, but the gateway install and configuration is still required for this option.
