Analyzing Data in Microsoft Excel with the MongoDB Connector for BI

Andrew Morgan

Business
Facebook ShareLinkedin ShareReddit ShareTwitter Share

There are many great BI tools out there that enable powerful analytics and data visualization, but sometimes the right tool for the job is the one you already have sitting on your laptop.

MongoDB 3.2 introduced the MongoDB Connector for BI which presents a SQL API to allow BI tools to read data from your database in real time. Typically, this functionality will be showcased with sophisticated analytics and visualization platforms such as Tableau, but it's equally applicable to Microsoft's ubiquitous Excel.

This video demonstrates how to connect Excel running on Microsoft Windows to the BI connector and then fetch data from MongoDB.

The instructions in the demo assume that you already have a MongoDB database running together with a configured instance of the MongoDB Connector for BI – the [documentation](https://docs.mongodb.org/manual/products/bi-connector/) explains how to set that up.

Note that Excel is not able to handle the . character or capital letters in table or column names. To overcome this, it was necessary to edit the DRDL file produced by the mongodrdl tool to map names to lowercase equivalents and to replace each . (used to flatten embedded documents) with a _; this requires v1.1 or later of the BI connector. Additionally, Excel cannot access the BI connector directly and so the demo shows how to download the required ODBC driver.

The MongoDB Connector for BI is part of MongoDB Enterprise Advanced; it can be freely downloaded for evaluation – why not try it out for yourself.

More information on the BI connector as well as other MongoDB 3.2 features can be found in MongoDB 3.2: What's New.


Learn about live data visualization with Tableau and MongoDB, watch Andrew's presentation.
Watch live data visualisation with Tableau & MongoDB

About the Author - Andrew Morgan

Andrew is a Principal Product Marketing Manager working for MongoDB. He joined at the start of last summer from Oracle where he’d spent 6+ years in product management, focussed on High Availability. He can be contacted at @andrewmorgan or through comments on his blog (clusterdb.com).