When I Import the data from Mongodb to powerbi , entire tables are not loading into Powerbi

[sharath123]

when I load data from Mongo ODBC , I am not getting all the columns/fields.I can't able to fetch the newly updated data in powerbi ,
if you make an update/add new data MongoDB  , why it is not reflecting in BI?Im having an issue when importing data from  MongoDB ODBC driver. Most of the data are modeled fine in PowerBI, but there are some columns missing.

Does PowerBI analyze all documents/rows when creating a data model from ODBC? Or does it only scan a certain amount of the documents to create the model?

[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: mongosqld — BI Connector (mongodb.com)

[sharath123]

@Syndicate_Admin @v-yingjl we have contacted powerbi support team, and we checked with both excel and powerbi.
the same situation repeated(newly added columns/fields (at source mongodb level) are not loading into powerbi as well as excel) 

finally concluded that it is not a problem with excel/powerbi. the problem with MongoDB ODBC BI connector.
till now no solution found for this. actual data is there but not loading into BI tools.
why MongoDB provides an intermediate connector between Database and BI tool? why can't it provide have direct connection?

[v-yingjl]

Hi @sharath123 ,

From Mongodb official document, seems like ODBC driver is the provided way to connect to Power BI.

Or you can try to use some third party connector like CDData etc.

In addition, you can submit a new idea here about this feature to help us improve Power BI.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sharath123]

@v-yingjl  

we have to go for a third-party driver 

Apart from MongoDB ODBC, I found also

• CData: https://www.cdata.com/drivers/mongodb/odbc/

• devart: https://www.devart.com/odbc/mongodb/
• EasySoft: https://www.easysoft.com/products/data_access/odbc-mongodb-driver/index.html
• Simba: https://www.simba.com/drivers/mongodb-odbc-jdbc/
• Dremio connector - https://www.dremio.com/drivers/odbc

But none of them is for free, you have to spend some money I guess.


Cdata MongoDB Bi Connector gave me this error. any idea about this?

[v-yingjl]

Hi @sharath123 ,

You can refer this document about the third-party connector to connect to Mongodb:

Real-Time Analytics on MongoDB Data in Power BI 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sharath123]

@Syndicate_Admin @v-yingjl we have contacted powerbi support team, and we checked with both excel and powerbi.
the same situation repeated(newly added columns/fields (at source mongodb level) are not loading into powerbi as well as excel) 

finally concluded that it is not a problem with excel/powerbi. the problem with MongoDB ODBC BI connector.
till now no solution found for this. actual data is there but not loading into BI tools.
why MongoDB provides an intermediate connector between Database and BI tool? why can't it provide have a direct connection?

[v-yingjl]

Hi @sharath123 ,

Under Import mode, if you want to get the latest data from data source in Power BI Desktop, you need to click 'Refresh' manually.

In addition, please consider whether connecting to the data source correctly and there is 1 GB limitation of Import mode in Power BI to load data.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sharath123]

@v-yingjl After Refreshing manually also, i am not getting new columns.

[v-yingjl]

Hi @sharath123 ,

Did these columns work fine in the data source? Also considering 1GB limitation of import mode as the previous post.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sharath123]

@v-yingjl yes. these columns (fields)showing in the MongoDB database. but when i loaded it into powerbi. it is not showing. 

 select *
 from table_name
where createdon ="2021-10-27"
I am only accessing one field, so no matter of 1GB limitation
i have cleared the cache. in powerbi data settings,
I have tried with a new DSN.
I have tried freshly installing powerbi in other laptop.
I have tried updating the MongoDB odbc drivers.
None of the above steps worked to fetch new columns.
what could be the problem? why it is not retrieving?

[ArturoRR97]

HI @sharath123.
It might be a little to late, but have you try to refresh the data schema?
When adding new columns to mongodb it is necesary to rebuild the schema for the bi connector to be able to see it. If you have a mongosqld service installed and running you need to stop and restart it.
Launch BI Connector — MongoDB Connector for BI
mongodrdl — MongoDB Connector for BI

[Syndicate_Admin]

Hi @sharath123 ,

Could not reproduce it in my side as far as my test, not certain the specific resaon that cause this issue. Have you tried to use the previous version or the latest version of Power BI Desktop to check? 

If different versions of Power BI still have the same issue, would suggest you create a support ticket here for further fast help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yingjl]

Hi @sharath123 ,

Could not reproduce it in my side as far as my test, not certain the specific resaon that cause this issue. Have you tried to use the previous version or the latest version of Power BI Desktop to check? 

If different versions of Power BI still have the same issue, would suggest you create a support ticket here for further fast help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.