I don't receive all fields in Power BI from MongoDB Atlas using ODBC

It could be filters or other settings causing issue
What is the query you are using in editor
Check BI forums.You may get more help

Hello @Ramachandra_Tummala,
I already check all this but the problem is not come from Power BI, I had a discussion with Power BI support Team. And we conclude that the problem come more the MongoDB BI connector using ODBC.
This is why I am here to understand the issues.
It will be very helpful for me if you make me in contact with the Engineer Team.
Thank you.

Best,

1 Like

HI, @MARWEN_FATNASSI
did you get any solution to this problem?

when you update/add new data at MongoDB, it is not reflecteing in powerbi.
and there is intermediate connector between mongo and powerbi i.e MongoDB bi connector

Hello, same issue for me. Didn’t found the solution. I use ODBC Connector on Power BI, works good, after that i add one collections but i can’t see this collection on Power BI. I delete and recreate the ODBC Connector and refresh, but did’nt work. Thx

1 Like

I have having the exact same problem, and I have done all the steps you provided. I am experiencing this anytime I use the ODBC connector (Excel, Tableau), I can use the exact same permissions in Compass and see the missing collections.

To me, this narrows down that is in fact the ODBC connector.

How do I fix this???

Were you able to resolve this? I’m having the same problem.

Does anyone know how to fix it? I had the same problem

I have not found a solution. However, I opened a case with MongoDB about a week ago. They’re currently investigating.

@Shmub @Alice_Nguy_n @Jake_Shivers
instead of using the ODBC connector, I moved to an alternative option i.e Python
By using pymongo library in python, we can connect to the MongoDB collections and pull the data.
you just need a search on the internet - how to connect MongoDB to python using pymongo?
once the data is fetched to the python (i have used visual studio software)environment, you can copy-paste the same python code in the powerbi ,so the data fetches into powerquery.

Run Python scripts in Power BI Desktop - Power BI | Microsoft Learn.

you can read this document to activate python supported option in powerbi and to fetch the data.

The problem here is probably the sampling size system variable on the BI Connector:

If you are using MongoDB Atlas, this can be configured within the Atlas admin web interface in an optional configuration text box when you first set up your BI Connector. It’s set my default to 100 but you can change this to 0 to force the connector to sample all the objects in a collection.

5 Likes

This worked for me . change the value to 0 in atlas

Thank you @Thomas_Russell

1 Like

Hi, Having the exact same problem but in SSIS. We are losing metadata a bit randomly. We do not have “MongoDB Atlas.” I havnt been able to understand how the sample size can be set to 0. Someone who knows how to do it?

I can confirm setting this to Zero within your Atlas configuration has resolved the problem. It may take a bit longer to pull down data given some collections are quite large and it’s resampling all collections, however, that is better than it failing when attempting to work on the data.

1 Like

Hello @Seth_Helgeson how mush time it takes?

Very helpful @Thomas_Russell, thanks for sharing :100:
This fixed the issue.

Hi,

Could you provide step by step how you set the variable to zero(0).

Thanks.

Hi Travis,

I am facing the same problem now.

Could you provide like a step-by-step process of how you achieved this?

Thanks in anticipation.

@Tunde_Morakinyo - they just set the sample to 0, which would sample through all docs to then build out the normalized view for the BI Connector. Here are the steps:
Within Atlas, from the cluster card, select the 3 dots button, then select “edit configuration”, then go to the Additional Settings section. Find the BI Connector section, and change the sample size to 0. Then review changes and save. This will take a bit of time to scan (depends on how large your cluster is) and while this is happening you/your users can’t use the BI Connector.


As suggested by Thomas_Russell this can be coming from the sampling size.

For those who do not use Atlas but use the self-managed MongoDB BI connector with an on-prem MongoDB install, the reason why some columns are not loaded could come from how the MongoDB BI connector builds a schema (columns) based on your data. By default (as of version 2.14.12), it only uses 1000 documents of each collection to identify columns. Meaning that if documents other than these 1000 documents have extra columns, they would be missing in your Power BI report!

This default value of 1000 can be changed when you start mongosqld using the argument “–sampleSize”. By setting its value to 0, it uses ALL documents of the collection when building the schema, so all your columns will be identified and loaded!

This is the command to run when you start mongosqld (first you might have to stop it from the Task Manager services if it’s already running!):
mongosqld.exe --sampleSize 0

By default mongosqld.exe should be on that folder: C:\Program Files\MongoDB\Connector for BI\2.14\bin\

See more info on the command in the link below. Note that you can specify a DB and collection for the sampleSize. If you don’t specify it, it will be applied on all collections. Note that if you want the schema to be refreshed if it is likely that new keys/columns would be added to your mongodb DB, you can also set --schemaRefreshIntervalSecs, see the doc below.

Doc: mongosqld — BI Connector (mongodb.com)

Same problem – but not Mongo, not Power BI.

Opened the same table using LINUX ODBC and the same columns Power BI omits are also omitted in Linux, testing with the UNICODE iusql command.

Of note, /etc/mongo_odbc.log does not show an error.

I repeated the ODBC test on Windows using a simple C# query. Same results; near identical log.