Just Released: MongoDB ODBC Driver

Seth Payne
July 12, 2018 | Updated: December 13, 2022
#Releases#BI

Earlier this month, we released the new ODBC driver for the MongoDB Connector for Business Intelligence (BI Connector). This driver makes it incredibly simple to connect to MongoDB data using analytics tools such as Microsoft Excel, Qlik, Microstrategy Desktop, Tableau Desktop, and PowerBI. But before diving into specifics of the driver, it will be helpful to review some information about the BI Connector, how it functions, and what use cases it is intended to serve.

MongoDB Connector for Business Intelligence

The MongoDB BI Connector provides access to MongoDB data by abstracting rich, hierarchical document structure and presenting it as a tabular, relational schema via a MySQL interface. The BI Connector acts as a SQL proxy and converts incoming SQL queries to equivalent MongoDB Query Language (MQL). Results are then returned in a standard tabular format and can be consumed by any application that supports direct connection to MySQL.

Of course, there are many applications -- used primarily by analysts and other business users -- which do not support direct MySQL connections and therefore, rely on ODBC to gain access to external SQL data sources. These ODBC connections are managed by underlying drivers that handle communication and the transfer of data between client applications and a defined data source.

A new ODBC driver?

Given that the BI Connector provides a MySQL interface we have been asked why not just utilize the existing MySQL, or similar, ODBC drivers? There are a few reasons for this but our primary motivation was to improve the end-user experience by making installation and set up simple and easy.

Prior to the release of our ODBC driver, setting up a connection to the BI Connector using the MySQL ODBC driver involved multiple steps, and several different software components. As a result, the process was error-prone and did not represent an ideal user experience. To address this, we bundled all components together into a simple click-through install process on both Mac OSX and Windows 7/10.

Now, within just a few seconds, users can configure their applications to access MongoDB as an available data source.

Installing the Driver and Importing MongoDB Data into Excel

Installing the ODBC driver on both Mac and Windows is quick and simple. If you are using Windows, you should install the driver that matches the architecture of your application. For example, if you are running a 32bit version of Excel, install the 32bit ODBC driver. Likewise, the 64bit version of PowerBI requires the 64bit ODBC driver. If you are running a mix of both 32bit and 64bit applications, there is no problem with installing both driver versions. On Mac OSX, the ODBC driver 64-bit.

In this post, we’ll walk through installation and setup of an ODBC connection on Windows 10 running the 32bit version of Excel.

First, download the MongoDB ODBC driver here. Make sure to download the driver that matches your version of Excel. In our case, we’ll select the 32bit version of the ODBC driver.

Once downloaded, double click the .msi file to begin.

Accept the license agreement and click through a few options. When the installation is complete, click “Finish.” Now that the driver is installed, we can set up our Data Source Name (DSN) using the MongoDB ODBC driver.

To do this, open the “ODBC Data Source Administrator (32-bit)” application.

Click “Add” to set up a new DSN. You will be presented with a list of available drivers. Select the MongoDB ODBC 1.0 Unicode Driver. We encourage all users to select the unicode driver unless there is a specific need to use the ANSI driver.

Once you click “Finish” you will be prompted to input information to connect to MongoDB via the BI Connector.

Input both a name and description for your datasource. In this example, we are connecting to an instance of BI Connector running in the MongoDB Atlas database service and will be working with data from the “nba” database. In the server and port fields, input the address and port number for the BI Connector. Add your MongoDB username and password and database. Once these values are input, click “OK.” This DSN will now be available to Excel, and other apps that support ODBC.

To import data into Excel, open a new workbook. Click on the Data tab. Now select Get Data → From Other Sources → From ODBC. Once selected you will be prompted to choose a DSN.

We will select the newly created DSN - “MongoDB Atlas - NBA” and click OK. If you are prompted to input username and password credentials, do so and click Connect. Excel will then present you with a database and table listing where you can select the data you want to import.

For this demo we’ll select historical National Basketball Association statistics. Specifically, player box score data for the past 20+ years.

Once selected, we click “Load” to return the data to Excel. After a moment we can see that Excel has loaded in just over 500k rows into our worksheet - from MongoDB.

And now that the data is in Excel, we can create Pivot Tables and Charts, or apply conditional formatting rules to perform an analysis of the data. For example, we want to find out which NBA player averaged the most minutes played per game. This is very easy to do, using a Pivot Table. Each row in our dataset represents the stats for a single player during a specific game. So, in our Pivot Table we set player names as rows, and the average of minutes as our values.

With this Pivot Table in place, we sort by average minutes and can see that Allen Iverson played the highest average minutes at 42. Lebron James, Anthony Mason, and Latrell Sprewell averaged 39 minutes followed by several players -- including Michael Jordan and Kevin Durant -- at 38 minutes.

Conclusion

If you would like to try the new ODBC driver to perform analytics with data in MongoDB, you can sign up for a free Atlas account by visiting this page. There you can claim $100 worth of free Atlas credits and be up and running with the BI Connector and MongoDB within a few minutes.

NOTE: The BI Connector is only available on Atlas M10 instances and above.

← Previous

Using Power BI to Gain Insight Into your MongoDB Data

We are happy to announce updates to the MongoDB Connector for Business Intelligence for use with Microsoft’s Power BI Desktop. Now, it is simpler than ever for Power BI users to access data stored in MongoDB and use Power BI’s powerful analytical and visualization tools to gain insights into the data, and then effectively share these insights with colleagues. Within just a few minutes, you can expose MongoDB data directly to Power BI to begin creating meaningful charts, dashboards, and reports. MongoDB as a Data Platform for Business Intelligence As both MongoDB’s popularity and adoption continue to rapidly grow, organizations are choosing MongoDB as the data platform that supports a variety of applications where tabular, or relational, database systems had been used historically. MongoDB’s document data model presents the best way to work with data for business critical applications, while distributed systems design allows users to intelligently put data where they want it, and enable the freedom to run anywhere - on premise or in the cloud. But data platform environments are rarely, if ever, homogenous. And, given both the longevity and large install base of enterprise RDBMS, it is common for MongoDB to be part of a larger ecosystem that includes a variety of data sources, many of which are tabular in nature. Because of the need to manage data from multiple systems, administrators are looking for ways to expose all their data to their non-technical business users in a consistent and friendly way, no matter where that data is physically stored. The MongoDB BI Connector enables Power BI users to easily query, analyze and visualize data stored in MongoDB in the same manner as with other Power BI data sources. No knowledge of MongoDB or the MongoDB Query Language (MQL) required! Get access to our Power BI Rapid Start → Exposing MongoDB Data To Power BI Desktop One of the benefits of using MongoDB as a platform for BI, is that it eliminates the need for complex ETL operations. BI data workloads can be isolated; separating operational workloads from analytics on separate replica nodes, all operating as a part of the same cluster. The data can also be exposed directly to end users without the need to modify and transfer data before it becomes available to analysts. Power BI can import MongoDB data through a direct connection to the MongoDB BI Connector or, via ODBC. Once a data connection has been defined, simply select the data you want to work with and import it. After the import is complete, you can begin working with the data in Power BI Desktop as you would with any data source. If you need to refresh your data, you can easily do so at any time. Data that resides right in your app is a gold mine when it comes to understanding your customers, but who has time to wait for nightly ETL workflows? With the MongoDB Connector for BI you can take control of your data and get to insights faster. See just how quickly you can discover something! Get access to our Power BI Rapid Start →

July 11, 2018
Next →

Building AI With MongoDB: Integrating Vector Search And Cohere to Build Frontier Enterprise Apps

Cohere is the leading enterprise AI platform, building large language models (LLMs) which help businesses unlock the potential of their data. Operating at the frontier of AI, Cohere’s models provide a more intuitive way for users to retrieve, summarize, and generate complex information. Cohere offers both text generation and embedding models to its customers. Enterprises running mission-critical AI workloads select Cohere because its models offer the best performance-cost tradeoff and can be deployed in production at scale. Cohere’s platform is cloud-agnostic. Their models are accessible through their own API as well as popular cloud managed services, and can be deployed on a virtual private cloud (VPC) or even on-prem to meet companies where their data is, offering the highest levels of flexibility and control. Cohere’s leading Embed 3 and Rerank 3 models can be used with MongoDB Atlas Vector Search to convert MongoDB data to vectors and build a state-of-the-art semantic search system. Search results also can be passed to Cohere’s Command R family of models for retrieval augmented generation (RAG) with citations. Check out our AI resource page to learn more about building AI-powered apps with MongoDB. A new approach to vector embeddings It is in the realm of embedding where Cohere has made a host of recent advances. Described as “AI for language understanding,” Embed is Cohere’s leading text representation language model. Cohere offers both English and multilingual embedding models, and gives users the ability to specify the type of data they are computing an embedding for (e.g., search document, search query). The result is embeddings that improve the accuracy of search results for traditional enterprise search or retrieval-augmented generation. One challenge developers faced using Embed was that documents had to be passed one by one to the model endpoint, limiting throughput when dealing with larger data sets. To address that challenge and improve developer experience, Cohere has recently announced its new Embed Jobs endpoint . Now entire data sets can be passed in one operation to the model, and embedded outputs can be more easily ingested back into your storage systems. Additionally, with only a few lines of code, Rerank 3 can be added at the final stage of search systems to improve accuracy. It also works across 100+ languages and offers uniquely high accuracy on complex data such as JSON, code, and tabular structure. This is particularly useful for developers who rely on legacy dense retrieval systems. Demonstrating how developers can exploit this new endpoint, we have published the How to use Cohere embeddings and rerank modules with MongoDB Atlas tutorial . Readers will learn how to store, index, and search the embeddings from Cohere. They will also learn how to use the Cohere Rerank model to provide a powerful semantic boost to the quality of keyword and vector search results. Figure 1: Illustrating the embedding generation and search workflow shown in the tutorial Why MongoDB Atlas and Cohere? MongoDB Atlas provides a proven OLTP database handling high read and write throughput backed by transactional guarantees. Pairing these capabilities with Cohere’s batch embeddings is massively valuable to developers building sophisticated gen AI apps. Developers can be confident that Atlas Vector Search will handle high scale vector ingestion, making embeddings immediately available for accurate and reliable semantic search and RAG. Increasing the speed of experimentation, developers and data scientists can configure separate vector search indexes side by side to compare the performance of different parameters used in the creation of vector embeddings. In addition to batch embeddings, Atlas Triggers can also be used to embed new or updated source content in real time, as illustrated in the Cohere workflow shown in Figure 2. Figure 2: MongoDB Atlas Vector Search supports Cohere’s batch and real time workflows. (Image courtesy of Cohere) Supporting both batch and real-time embeddings from Cohere makes MongoDB Atlas well suited to highly dynamic gen AI-powered apps that need to be grounded in live, operational data. Developers can use MongoDB’s expressive query API to pre-filter query predicates against metadata, making it much faster to access and retrieve the more relevant vector embeddings. The unification and synchronization of source application data, metadata, and vector embeddings in a single platform, accessed by a single API, makes building gen AI apps faster, with lower cost and complexity. Those apps can be layered on top of the secure, resilient, and mature MongoDB Atlas developer data platform that is used today by over 45,000 customers spanning startups to enterprises and governments handling mission-critical workloads. What's next? To start your journey into gen AI and Atlas Vector Search, review our 10-minute Learning Byte . In the video, you’ll learn about use cases, benefits, and how to get started using Atlas Vector Search.

April 25, 2024