MongoDB power BI connector not giving all columns

[shubham-sharma]

Hi,
I'm using MongoDB powerBi connector to connect power BI and MongoDB earlier I was getting data from the MongoDB collection but after making some alteration to the data (some specific fields) suddenly those columns stopped coming in the query editor. I've tried creating a new DSN and fetching the data but still, the same issue occurred. I restarted the connector but the problem didn't solve.

The strange thing is when I'm connecting from the command prompt using mongosqld then I'm able to see all the columns in that collection but from the odbc I'm not getting those columns.

and yes one more thing I would like to ask that the difference between:-

1. MongoSQLD vs MongoDB (power BI connector)
2. When connecting via Mongo(power BI connector)we are only getting a fixed number of columns, that is, 35 or 34 but when connecting from MongoSQLD it is giving 48 columns.

[XavierS]

I had the same issue and could not find a solution on a PBI or MongoDB forum, but found the solution in the mongodb BI connector doc! I am using the self-managed BI connector offered by MongoDB with an on-prem MongoDB install (no Atlas!).

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: https://www.mongodb.com/docs/bi-connector/current/reference/mongosqld/#std-option-mongosqld.--sample...

[Jyo27]

Can we show both parent columns and child columns(arrays) from mongodb to power bi ?

[v-stephen-msft]

Hi @shubham-sharma ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[v-stephen-msft · Accepted solution]

Hello @shubham-sharma ,

You can try to check the permission in the data source settings and connect again.

About the difference, please kindly refer to:

https://www.guru99.com/what-is-mongodb.html

https://docs.mongodb.com/bi-connector/master/reference/mongosqld

https://docs.mongodb.com/bi-connector/master/faq

Best regards

Stephen Tao

If this post helps,then consider Accepting it as the solution to help other members find it faster.