Forum Discussion

PremK's avatar
PremK
Icon for Resolver I rankResolver I
2 years ago

DATA EMPTY ERROR With MongoDB || Emergency

  In MongoDB  i have 11 Collections  In a DB named OPTIORX. All Collections contains multiple documents and values.    I had connected this DB  with PBI ( throug 2 methods - 1. Using  Get Da...
Need Help
Shivu-2000's avatar
Shivu-2000
Icon for Responsive Resident rankResponsive Resident
2 years ago

Hi PremK,

The error "most of the COLUMNS WERE EMPTY" when connecting Power BI to your MongoDB Atlas database (either via Atlas SQL URL or ODBC drivers) for some collections (tables) points to a data preview issue. Here are some steps to resolve this and bring all your collection data into Power BI:

1. Investigate Data Preview Limitations:

  • Preview Size: There might be limitations on the amount of data Power BI previews when connecting to external sources. Check if the collections showing empty columns have a significantly larger number of documents compared to the ones previewing correctly.

  • Data Complexity: Very complex data structures within the collections might be causing issues with Power BI's initial preview process.

Solution:

  • Try filtering the collections with the empty columns to a smaller subset of documents. This can help Power BI preview a manageable amount of data and potentially display all columns correctly.

  • Consider using aggregation pipelines within MongoDB to pre-process your data and potentially reduce the size of individual documents. This can improve Power BI's ability to preview the data.

2. Verify Data Type Compatibility:

  • While you mentioned data types are compatible, double-check for any potential mismatches between MongoDB data types and how Power BI interprets them. Ensure there are no unexpected conversions causing columns to appear empty.

3. Check Connection Credentials:

  • Re-verify your connection credentials for both Atlas SQL URL and ODBC methods. Ensure you have the necessary read permissions on the OPTIORX database and all relevant collections.

4. Consider Alternative Methods:

  • Import Data: If the preview issue persists, explore importing the data directly into Power BI instead of using live connections. This might involve exporting data from MongoDB (e.g., using tools like Mongoexport) and then importing it as a .csv or .json file into Power BI.

5. Check for Atlas SQL Beta Issues:

  • Since you're using the Atlas SQL (Beta) method, there might be known limitations or bugs with this functionality. Check the official MongoDB Atlas documentation for any updates or reported issues related to data previewing with Power BI.

 

If you find this insightful, please provide a Kudo and accept this as a solution.