Trying to connect to MongoDB using MongoDB ODBC driver

[mohdrizwans]

Team,

I am trying to connect PowerBI to MongoDB database using MongoDB ODBC connector after doing all steps listed in this link: https://docs.mongodb.com/bi-connector/master/local-quickstart/

To create a Data Source Name (DSN)  I have used https://docs.mongodb.com/bi-connector/current/tutorial/create-system-dsn/

After following all steps and doing everything, when I try to connect with PowerBI Desktop, it connects using DSN and brings databases and collections list but when click to any collection(s), following error message is showing.

DataSource.Error: ODBC: ERROR [42S21] [MySQL][ODBC 1.1(w) Driver][mysqld-5.7.12 mongosqld v2.11.0]Duplicate column name 'KEY_COLUMN_USAGE.CONSTRAINT_CATALOG'
Details:
DataSourceKind=Odbc
DataSourcePath=dsn=MONGO 1
OdbcErrors=Table

Please help ASAP

Thanks,

[YogiBear]

Dear All,

Installed ODBC -> BI Connector -> Setup DSN for Remote Server -> Connection Successful (1st screencap attached) -> Able to read Databases & Tables or Documents in Power BI Desktop from MongoDB.

After I selected a particular Table/Document on the lefthandside, the 'Preview is evaluating...' is taking forever and the data is not displayed (2 screencap attached).

Can anyone please let me know if I have missed a step and why is my Power BI Desktop still not able to display the data so that I can go ahead into Transform Data and do the needful before creating Reports & Dashboards?

Thanks in advance for your valuable time.

Kind regards,

YogiBear

[Srikanth7Gunnam]

Hello, We are trying to pull data from MongoDB to Power BI. Do we need to have any license to do that?

[mrlucifer]

I was also stuck with the MongoDB ODBC connector for windows and did not find any help over the internet. So after debugging lots of hours i finally able to connect with the MongoDB ODBC with my server. So, first of all, you need to download these prerequisites.

Download and Install Community Server https://www.mongodb.com/download-center/community
Download and Install https://www.mongodb.com/download-center/bi-connector
Download and Install https://github.com/mongodb/mongo-odbc-driver/releases

Check the Full tutorial here

https://medium.com/knesk/mongodb-can-not-connect-the-database-by-mongodb-odbc-driver-windows-1d27c27...

[Anonymous]

I was experiencing the same problem. I think there's a regression with MongoDB BI Connector v. 2.11. I've downgrade to 2.9 and 2.10, and it solved the problem for me.

Stephane.

[Anonymous]

Indeed, same here. I have this problem since I upgraded to BI connector 2.11.

[mohdrizwans]

Hi,

can you share downloadable link for 2.9/2.10 version?

Thanks

[mrlucifer]

It works when you downgrade to the 2.10.0 i just tested. Here's the download link for the 2.10.0

https://info-mongodb-com.s3.amazonaws.com/mongodb-bi/v2/mongodb-bi-win32-x86_64-v2.10.0.msi

[Anonymous]

Yep, I know, I tried. But...why did the MongoDB team break the BI connector??

[RMDNA]

@Anonymous @mrlucifer ,

I uninstalled my BI Connector and intsalled v2.10 from the provided link. However, I'm getting the same error in Power BI.

Does the section "mysqld-5.7.12 mongosqld v2.11.0" have anything to do with it (since it doesn't say v2.10")?

[mrlucifer]

It's working 100% fine once you downgrade the version. If you still can't make it work we can discuss via chat or email me aman@knesk.com

[ChrisSchoon]

I have the same problem. I would love to find out how to resolve. Here are some details:

• I am using mongo BI connector 2.1.
• ODBC: ERROR [42S21] [MySQL][ODBC 1.2(a) Driver][mysqld-5.7.12 mongosqld v2.11.0]Duplicate column name 'KEY_COLUMN_USAGE.CONSTRAINT_CATALOG'
• I tried both ANSI and unicode ODBC.
• 64bit Windows 10

[mohdrizwans · Accepted solution]

Try following steps:

I used Advanced Options and wrote SQL Statement (select * from zips) to resolve but this is not concrete solutions but still you can work.

1. In Power BI Desktop, click Get Data.

1. In the Get Data dialog box, click other, then select ODBC from the list of data source types, and then click Connect.

1. In the From ODBC dialog box, in the Data Source Name (DSN) drop-down list, select your DSN.

1. Optionally, to specify what data is returned with a SQL statement, expand the Advanced Options area and then, in the SQL Statement (Optional) field, type or paste a SQL statement to specify the data.

1. Click OK. If you are prompted to provide your credentials, then do one of the following:

• If your data store requires credentials for access, then in the Username and Password fields, type your credentials for accessing your data store, and then click Connect.

• Otherwise, from the column on the left, select Default or Custom and then click Connect.

A dialog box opens and displays the data that is returned from the data store. If you used a SQL statement to specify what data is returned, then the dialog box only displays the rows returned by the SQL statement.

1. Choose one:

• If you did not use a SQL statement to select your data, then select the check boxes next to the tables that you want to import and click Load.

• Or, if you used a SQL statement to select your data, click Load.

The selected data is loaded into Power BI Desktop and listed in the Fields task pane. You can now use Power BI to analyze the data.

Thanks,

Rizwan Siddiqui