February 4, 2021

Using the Mongo BI Connector (mongosqld) to access an Azure Cosmos DB / Mongo API - Helpful for PowerBI and Tableau

Yeah, that's a mouthful and it sounds easy, but it wasn't.  First of all PowerBI has tons of documentation about how to connect Cosmos to PowerBI. The only issue is, they are all for the Azure Cosmos SQL API.  Currently the Mongo API isn't supported!

Bummer.

Well, that stinks.

So over the MongoDB site.  There are some very promising articles there.  This looks super simple:  Connect from Microsoft Power BI Desktop — MongoDB Connector for BI

Then you click that prerequisites link which takes you to a page showing how to setup and ODBC connection which is also super simple and straight forward.

Except that pesky prerequisites section.  It turns that first one is a bit of a bear and now is where you learn how this really works.

The MongoDB BI Connector is actually a command line service that connects to your mongo server and presents a mySql compatible service that the Mongo ODBC driver connects to.  Then PowerBI (or other apps) can use that ODBC driver to connect to Mongo.  Yeah, that's a mouth full.

Configuring the BI Connector is non-trivial because all the documentation is for Atlas and none really for Azure Cosmos DB / Mongo API.

So, I'll cut to the chase, the hard bit is the configuration file for the mongosqld (that is the EXE name for CLI tool).  The contents of that file are below.  NOTE: It is a YAML file so leading spaces matter!

systemLog:
  verbosity: 3
security:
  enabled: true
mongodb:
  net:
    uri: "Azure DB - Connection String Blade - HOST:PORT inside quotes"
    ssl:
      enabled: true
    auth:
      username: "Azure DB - Connection String Blade - USERNAME inside quotes"
      password: "Azure DB - Connection String Blade - PRIMARY PASSWORD inside quotes"
schema:
  sample:
    namespaces: "your-database-name.*"

The values for uri, username, and password are all in the Connection String blade for your cosmos database in the Azure Portal.  Yes, this isn't super secure for now, but it will get it running.

The command line I use to run this pretty straight-forward.  mongosqld.conf is the name of the file I stored the above config in.

"C:\Program Files\MongoDB\Connector for BI\2.14\bin\mongosqld.exe" --config mongosqld.conf

Now that the Mongo BI Connector is running, it's time to configure the Mongo ODCB connection with driver you previously downloaded.  This is pretty straight forward stuff once the connector is running.

That said, notice that you're connecting to localhost:3307 (default for the connector) because the connector is running locally.  For anything production you'd want to host this and update the configuration for security.   The username and password are the same you used for the connector.  These are passed through the connector to the database.  Yes, I know another security challenge I'm not addressing here.  Finally, make sure you select your database in the drop down!  It will fail with an unknown database error otherwise.

Now you can connect to this ODBC source from PowerBI, Tableau or whatever.

Hope this helps out someone.  My real goal is to see if Google and Bing index this very specific article and actually show it to someone.  We'll see.