Forum Discussion

PremK's avatar
PremK
Resolver 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 Data - MongoDB Atlas SQL ( Beta ) - Atlas SQL URL ,,||  2. Using Get Data - ODBC - Atlas SQL ODBC Drivers  )

 

While selecting the required Tables ( Collections ) from the DB,  Only some tables  ( 4 out of 11 Tables ) previews ALL The FIELDS with its respective Data Values in it ( Columns and Rows ) But Other Tables showing error like most of the COLUMNS WERE EMPTY

 

 

 

But  actually All the Columns have Data Values in MongoDB  Data Base, and the Data types and Structure are compatible with PBI. i have no idea why the error is showing

I have tried multiple ways to resolve this error like - cleared Cahe,  in Data Source Setting Cleared All permissions and Reconnected Them,  But all my efforts gone waste.

 

Please guide me to Resolve this error and bring all the Table data's into PBI....

Need Help

2 Replies

  • Shivu-2000's avatar
    Shivu-2000
    Responsive 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi PremK ,

    I notice you have marked a reply as a solution, is the problem solved? Does running the sqlGenerateSchema commands help?


    Solved: DATA SOURCE ERROR With MongoDB || EMERGENCY - Microsoft Fabric Community

    Also try Thomas_Russell's solution under this thread:
    I don't receive all fields in Power BI from MongoDB Atlas using ODBC - Working with Data / Connectors & Integrations - MongoDB Developer Community Forums
    System Variables — MongoDB Connector for BI

    Please feel free to contact me if the issue is not resolved.

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group