Content
- Objective
- Technology Background
- Prerequisites
- Architecture
- Register MongoDB Atlas Account
- Install Devart ODBC for MongoDB
- Get MongoDB Connection in MongoDB Atlas
- Create ODBC Connection in PowerBI Desktop
- ODBC Driver Configuration
- Conclusion
Objective
This tutorial explains how to build Credit Card Analytics by leveraging Power BI Desktop, Devart ODBC for the MongoDB driver, and the MongoDB database. It will not instruct you on how to work on PowerBI Desktop or MongoDB; instead, we will demonstrate how to work with Devart ODBC for the MongoDB driver.
Technology Background
Power BI Desktop
Microsoft Power BI Desktop is built for the analyst. It combines state-of-the-art interactive visualizations with built-in industry-leading data query and modeling. Create and publish your reports to Power BI. Power BI Desktop helps empower others with timely critical insights anytime, anywhere.
MongoDB Database & MongoDB Atlas
MongoDB is a source-available, cross-platform, document-oriented database program. It is classified as a NoSQL database product and utilizes JSON-like documents with optional schemas. MongoDB is a document database with the scalability and flexibility you want and the querying and indexing you need.
MongoDB provides the MongoDB Atlas product as a developer data platform built around a fully managed MongoDB service. It addresses transactional, search, and analytical workloads.
Devart ODBC for MongoDB
Devart ODBC Driver for MongoDB is a high-performance connectivity tool with enterprise-level features for accessing MongoDB databases from ODBC-compliant reporting, analytics, BI, and ETL tools on both 32-bit and 64-bit Windows, macOS, and Linux. Our ODBC driver fully supports standard ODBC API functions and data types, enabling easy and secure access to live MongoDB data from anywhere.
Prerequisites
Before starting to connect the Oracle database by using Devart ODBC for MongoDB and building Analytics, make sure to download the necessary tools and drivers:
- Click here to download Power BI Desktop
- Click here to download Devart ODBC for MongoDB Driver
- Click here to register a MongoDB Atlas account to create a document database on the Cloud.
Architecture
Power BI Desktop uses the Devart ODBC for MongoDB driver to connect to the MongoDB database hosted in the Cloud and query data for building analytics.
Register MongoDB Atlas Account
If you do not have a MongoDB Atlas Account, you should access the link to register an account. After you have the MongoDB Atlas account, you can create a MongoDB database and use Devart ODBC Driver for MongoDB to connect to the database. Below is an example of the MongoDB Atlas dashboard.
Install Devart ODBC for MongoDB
Download and run the installer file to install and follow the instructions below:
Select Destination Location
Select Full Installation
Click Next to proceed to the start of the installation
After the installation, we continue configuring the driver.
Get MongoDB Connection in MongoDB Atlas
To set up the ODBC Configuration in the next step, we need to identify the connection of the MongoDB database in the MongoDB Atlas.
On the main page of MongoDB Atlas, select Connection -> Drivers
In the drop-down list of drivers, select the required one and copy the DNS name from the connection string. In this case, the DNS name should be:
cluster0.hvkaglr.mongodb.net and use it in ODBC Configuration
Notice that when we create MongoDB databases in the cloud, it means they are being run in a Clustering mechanism. So, we will not connect to an IP address or Server Name of any specified MongoDB database; instead, we will connect via the DNS Server Name. As the image below shows, there are three servers in Cluster 0: primary and secondary servers.
On the main page, click Browse Collection to explore the list of databases and their collections. We have on the sample database sample_mflix and its collections: users, movies, theaters, sessions, and embedded_movies.
We can understand that sample_mflix is a database, and we use it for ODBC configuration. Its collections are tables reflecting relational database concepts.
ODBC Driver Configuration
In this step, we must configure ODBC Connection in your environment. In this tutorial, we are installing on a Windows machine
- Click Start, Run
- Type the C:\Windows\System32\odbcad32.exe if the system is 64bit to open ODBC Data Source Administrator
- Click on the Driver tab and make sure the Devart ODBC Driver for MongoDB is in the list of drivers
- Select the User DSN or System DSN tab.
- Click Add. The Create New Data Source dialog will appear.
- Select Devart ODBC Driver for MongoDB and click Finish. The driver setup dialog will open.
- Enter the connection information in the appropriate fields
- Data Source Name: ODBC_MongoDBAtlas
- Server: cluster0.hvkaglr.mongodb.net -> This information is what we get in MongoDB Atlas
- Port: 27017 is the default port to connect
- User ID: mongdb_admin -> This user is created during we create a cluster and MongoDB in MongoDB Atlas
- Password: password of mongodb_admin user
- Database: sample_mflix -> This information is what we get in MongoDB Atlas
- Connection Format: DNS Seed List. This setting is important because we connect to the MongoDB databases in the Cloud we created in MongoDB Atlas.
- Click on Test Connection to correct the connection.
Create ODBC Connection in PowerBI Desktop
In this step, we will use Devart ODBC for MongoDB driver to establish the connection bridge between PowerBI Desktop and MongoDB database in MongoDB Atlas. Make sure that PowerBI Desktop is installed completely.
- Open PowerBI Desktop -> Click on Get Data -> Get Data window appears -> Choose Others. On the left panel, there are a lot of drivers that PowerBI supports
- Choose ODBC driver -> Connect
- From the Data Source drop-down list -> Choose the DNS: ODBC_MongoDBAtlas which is created in 9.
ODBC Driver Configuration
- Click OK. Then enter username and password if they are required
- Click Connect. The Navigator window appears, and we will the list of tables in sample_mflix database -> select Comments and Users collection to add them to PowerBI Desktop
- We are ready to build the report with data from Cloud MongoDB databases
Conclusion
Devart ODBC Driver for MongoDB is a high-performance connectivity tool with enterprise-level features for accessing MongoDB databases from ODBC-compliant reporting, analytics, BI, and ETL tools on 32-bit and 64-bit Windows, macOS, and Linux. Our ODBC driver fully supports standard ODBC API functions and data types, enabling easy and secure access to live MongoDB data from anywhere.
