Connect MongoDB to Tableau: 3 Easy Methods

Tableau and many other BI tools discover patterns and help visualize data to make it more understandable and boost the analysis process. These help in gaining a better understanding of the metrics, trends, user needs, performance, and play a key role in various data-driven decisions. MongoDB, a high-performance document-oriented database, powered by a NoSQL structure is the perfect choice to complement a powerful BI tool like Tableau to perform an insightful analysis.

This article aims at answering all your queries about connecting MongoDB to Tableau. Follow our easy step-by-step guide to master the skill of connecting MongoDB to Tableau using various methods.

Methods to Connect MongoDB to Tableau

There are multiple ways in which you can connect MongoDB to Tableau:

• Method 1: Using Hevo Data
• Method 2: Using MongoDB BI Connector
• Method 3: Using ODBC to Connect MongoDB to Tableau
• Method 4: Exporting MongoDB Data as JSON
• Method 5: Connect using Tableau Desktop

Method 1: Connect MongoDB to Tableau using Hevo

Hevo is a real-time, No-code Data Pipeline ELT platform that cost-effectively automates data pipelines, which can be flexible based on your needs. With the help of  150+ integrations (40+ free sources), you can export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

One more unique way to connect MongoDB to Tableau is to use MongoDB as a source in Hevo and then use Tableau with BigQuery connector as the destination to analyze the data.

Step 1: To configure MongoDB as a source on the Hevo Platform

Perform the following steps:

• Go to the Navigation Bar and click on the Pipelines.
• Click on the + CREATE button in the Pipelines List View.
• Select the MongoDB variant on the Select Source Type page.
• Select the MongoDB Variant that you want to use for managing your databases
  • Generic Mongo Database: Database management can be done by any service provider or by yourself.
  • Mongo Atlas: You can get a managed database service from MongoDB.
• Specify the necessary information required to setup the source database. 
• Click the link here to learn more about MongoDB source setup on Hevo.

To configure Tableau to your managed BigQuery using Hevo, follow these steps:

Step 2: Configure BigQuery as destination

Learn more about configuring BigQuery as destination here.

Step 3: Connect to Tableau

• Download Tableau connection settings from Hevo
• Get access to the Tableau application

Image source

• Under Connect on the left navigation pane, select To a server
• Choose Google BigQuery
• Log in to your tableau account with Hevo
• Click on the ALLOW button to approve tableau to access BigQuery data
• Select the valid Billing project mentioned under Credential by Hevo 

Image source

• Finally, select the dataset mentioned under Credentials by Hevo to configure Tableau to manage the BigQuery database using Hevo.

Method 2: Using MongoDB BI Connector

MongoDB BI connector allows you to use MongoDB as your data source and connect to a BI tool of your choice. Using MongoDB’s BI connecter, you can discover & visualize data with SQL-based analytics tools such as Tableau.

MongoDB BI connector is available as part of the enterprise edition and can be downloaded from here. Using MongoDB’s BI connector, you can connect MongoDB to Tableau with/without authentication enabled.

Before you start the connection, collect this information:

• Name of the server
• If MongoDB authentication is enabled, gather username, password and Database credentials.

To make the connection and set up the data source, follow these steps

• Launch Tableau and, navigate to the Connect pane, select MongoDB BI Connector. For a list of data connections, click on the More button under To a server. Then follow the steps given below;
  • Provide the server’s name.
  • If MongoDB authentication is activated, input your username, associated database name, and password. For the correct format, refer to the Sign-in options. If MongoDB authentication is inactive, move to the next step.
  • Check the Require SSL box for SSL server connections. For detailed information on SSL connections to MongoDB, refer to Connecting Tableau to MongoDB on the MongoDB website.
  • Selecting Initial SQL is an option to specify a SQL command that executes at the start of each connection, such as when opening the workbook, refreshing an extract, signing in to Tableau Server, or publishing to Tableau Server. More details can be found in the Run Initial SQL documentation.
  • Click on Sign In.
  • If the connection fails, ensure your credentials are accurate. If the issue persists, your computer might struggle to locate the server. Contact your network or database administrator.

On the data source page:

• Optionally, click on the default data source name at the page’s top and assign a unique name for use within Tableau.
• Choose the sheet tab to commence your analysis.

Sign-in Options

In MongoDB, user accounts are linked to specific logical databases. Consequently, when users log into MongoDB, they must specify the database name associated with their credentials. This is accomplished by including special options in the username string. For instance, if the user “henrywilson” is affiliated with the “example” database, the entry in the Username field would be:

henrywilson?source=example

If you wish to employ an authentication mechanism other than the default SCRAM-SHA-1, you can use the “mechanism” option to define it. For instance, if the user “henrywilson” intends to connect to the “example” database using challenge/response as the authentication mechanism, the entry in the Username field would be:

henrywilson?source=example,mechanism=MONGODB-CR

Connecting without Authentication Enabled

• Launch Tableau on your workstation.
• Select MongoDB BI connector from the connect column on the left.
• A connection without authentication doesn’t require you to enter a username and password. Directly provide the default host server and port value on which mongosqld is running. MongoDB, by default, uses port 3307 for such connections.
• Now click on sign in to establish the connection.

Connecting with authentication enabled

To connect with an authentication-enabled instance of MongoDB, you need to establish an SSL encrypted connection from MongoDB to Tableau. This method is called pass-through authentication.

This can be done using the following steps:

• Step 1: Setting up mongosqld
• Step 2: Connecting to Tableau

Step 1: Setting up mongosqld

You can use the –auth parameter in the mongosqld command to activate the pass-through authentication. Use the mongosqld command as follows:

$ ./mongosqld --schemaDirectory /home/vagrant/schema - --addr=10.11.12.13:3307 --auth --sslPEMKeyFile=/mongosqld-server.pem

The mongosqld command will use the -auth parameter to check and authenticate the connection between the BI connector and various tools such as Tableau.

Bi-directional authentication is maintained using mongosqld-server.pem, a self-signed certificate.

Step 2: Connecting to Tableau

To connect with Tableau, you need to provide the username and password to use the desired database. MongoDB uniquely manages its users by associating them with the databases they work on.

The username consists of the user’s name along with the dataset name as a key-value pair. The basic syntax is as follows:

user_name?source=database_name

Example username: biUser?source=admin, here the name of the client is biUser and admin represents the associated database.

Now once you have figured out your username, enter it along with the password. Ensure you check the Require SSL box before signing in.

If you want to run a specific SQL command every time a new connection is established, you can use the Initial SQL option. This will open a dialogue box, where you can specify your desired SQL query.

For further information on the Initial SQL command, you check the official site here.

Click on the no-custom configuration option and sign in to establish the connection. The MongoDB database will now be available to you on the right-hand side.

This is how you can use the MongoDB BI connector to connect MongoDB to Tableau.

Some Limitations of this Method

• It is only available as a part of MongoDB’s enterprise offerings. It is not a cost-effective option.
• MongoDB BI connector faces difficulty in processing certain SQL statements. It is not able to correctly map queries, especially the ones that have aggregate functions such as sum, max, avg, etc. Therefore, queries need to be simple to avoid such errors.

You can check the supported SQL functions and operators here.

Method 3: Using ODBC to Connect MongoDB to Tableau

Through the seamless connectivity of MongoDB databases and Tableau made possible by the MongoDB Tableau Connector, customers may effectively visualize and analyze data for improved business insight. ODBC drivers can also be used to connect MongoDB to Tableau. The ODBC driver can add a data source such as MongoDB and then connect the MongoDB instance with Tableau. 

Before connecting Tableau to MongoDB, you need to download a set of files to recognize Tableau and make the connector available.

• Step 1: Initially, you have to download the MongoDB JDBC Driver and check the *.all.jar version from the list. You need to move this file to your specific operating system driver. C:\Program Files\Tableau\Drivers or /Library/Tableau/Drivers
• Step 2: Download the Custom Tableau connector (TACO file). This file also should be moved to a location based on your operating system. C:\Program Files\Tableau\Connectors or /Library/Tableau/Connectors
• Step 3: Download the Federated Database instance within MongoDB and use the connection information in Tableau. For more information, click this link.

This can be implemented using the following steps:

• Step 1: Installing BI Connector
• Step 2: Installing MongoDB ODBC Driver and DSN Setup
• Step 3: Running the Mongo Instance
• Step 4: Running the mongosqld Instance
• Step 5: Connecting to Tableau

Step 1: Installing BI Connector

The first step to import MongoDB data to Tableau is installing the MongoDB BI connector from the official site here. Follow the steps carefully to install the BI connector on your system.

Step 2: Installing MongoDB ODBC Driver and DSN Setup

Download the latest version of ODBC driver for MongoDB on your system using the Github repository. Use the following link: https://github.com/mongodb/mongo-odbc-driver/releases/.

Once the ODBC driver is installed on your system, launch it to start the DSN setup process. Choose the correct 64 or 32-bit version and select the System DSN tab.

Add the MongoDB ODBC driver using the add button. You can either select the MongoDB ODBC 1.1.0 ANSI driver or the MongoDB ODBC 1.1.0 UNICODE driver.

Carefully choose the correct driver. The ANSI driver delivers the best performance but has a limited character set, whereas, the UNICODE driver supports the entire character set but has slightly poor performance.

Fill the necessary fields as follows:

• Data Source: Give the name of the data source you want to connect to.
• TCP/IP Server: Give the details of the server hosting mongosqld. It has a default value of 127.0.0.1.
• Port: MongoDB uses port 3307 by default to run the mongosqld instance.
• Username: Enter the username in the correct format as follows: user_name?source=database_name
• Password: Enter your password here.

Step 3: Running the Mongo Instance

Open the command prompt to run the mongo instance. Use the following commands to start the mongo server:

cd /usr/local/cellar/mongodb/Version/bin/mongod

$ mongo -u username -p user_password <server_ip>/database_name

In the first command, you need to provide the MongoDB version you are using. For example, if you’re using v3.4, write as follows:

cd /usr/local/cellar/mongodb/3.4/bin/mongod

In the second command, you need to specify the server, port, username, and password used in the DSN setup.

Step 4: Running the mongosqld Instance

Open another command prompt and run the following command to start the mongosqld program:

mongosqld — mongo-uri mongodb://serveraddres.com:portnumber — auth — mongo-username username — mongo-password “password”

You need to specify the server, port, username, and password used in the DSN setup. Carefully specify your password in quotes while using this command.

Step 5: Connecting to Tableau

Open Tableau and select the other databases (ODBC) option from the connect column on the left.

Select your DSN from a drop-down list available in the ODBC dialogue box. Click on the sign-in option.

This is how you can use ODBC to connect MongoDB to Tableau.

Some Limitations of this Method

• This method sometimes requires using third-party tools, which essentially is an extra tool that needs to be maintained. Such tools may even require some initial investment.
• There is always confusion about choosing the right driver. The ANSI delivers in terms of performance however lacks the entire character set whereas the UNICODE driver contains the entire set but suffers on the performance end.
• When dealing with complex JSON, errors related to the table normalization & joins occur often.

Method 4: Exporting MongoDB Data as JSON

One unique way to connect MongoDB to Tableau is to export data from MongoDB in JSON format and then use the Tableau JSON connector to analyze the data.

This can be done using 2 simple steps:

• Step 1: Exporting Data Using the monogoexport Command
• Step 2: Using Tableau’s JSON Connector

Step 1: Exporting Data Using the mongoexport Command

Start the MongoDB instance on your system. Once the instance is up and running, navigate to the directory where MongoDB utilities are located. You can use the following command for this:

cd /usr/local/cellar/mongodb/Version/bin/mongod

To transfer the data, you can use the mongoexport command as follows:

$ sudo mongoexport --host <Cluster or Host Name> --ssl --username <Username> --password <Password> --db <Database Name> --c <Collection Name>  --out <Output JSON File Name>

Example query:

$ sudo mongoexport --host mongodb0.example.com --port=27017 --db director --c movies --out movies.json

For more information on how to export MongoDB data into JSON, have a look at our easy step-by-step guide to help you out. Have a look here!

Step 2: Using Tableau’s JSON Connector

Open the JSON connector and select the JSON file you have just exported. Specify/select the required schema as JSON is unstructured in its design and requires users to manually select them.

Once you have selected the schema, you will get an output representing data in a flattened way as follows:

You can now analyze data using various visualizations and gain a better understanding of what your data represents.

This is how you can connect MongoDB to Tableau in a unique way.

Some Limitations of this Method

• This method requires a lot of manual inputs and requires the users to update the JSON file every time they want to make a change in the visualization.
• JSON file connector has limited functionality. It is not possible to join multiple JSON files together.
• This dependence on the files stored on the system hampers the performance.

Method 5: Connect using Tableau Desktop

Now, you will go through the 3 different modes to connect MongoDB to Tableau. The initial steps are the same for these modes. Let’s get through the process to connect MongoDB to Tableau Desktop.

• Download and install the MongoDB ODBC driver here.
• Create a system DSN to securely connect Tableau to the BI Connector. For this first, you have to start the Microsoft ODBS Data Program that is appropriate for your system.
• Then select the System DSN tab.
• Now, Click on the Add button.

• Here, select the MongoDB ODBC Driver from the list of drivers you have on your system. For this, you can either select the MongoDB ODBC 1.1.0 ANSI Driver or the MongoDB ODBC 1.1.0 Unicode Driver.
• Then, click on the Ok button.

• Connect without Authentication
• Connect with Authentication
• Connect with Authentication and TLS/SSL

Connect without Authentication

• Fill in the form fields by clicking on the Details button. 
• Fill in the Data Source Name of your choice.
• Then, fill the TCP/IP Server where your mongosqld process is running.
• Fill the Port as the port number of the mongosqld process.
• Then click the Test button and click on the OK button.
• Start the Tableau application or restart the application.
• In the left navigation under To a Server click on the More…  then click on the Other Databases (ODBC).
• From the dialog box select the DSN from the dropdown and click Connect.
• Click Sign In to connect MongoDB to Tableau Deskop.

Connect with Authentication

• Fill in the form fields by clicking on the Details button. 
• Fill in the Data Source Name of your choice.
• Then, fill the TCP/IP Server where your mongosqld process is running.
• Fill the Port as the port number of the mongosqld process.
• Now, fill in the required authentication form fields when running with –auth enabled.
• Enter the authenticated username of the user to the target Database in the User field.
• Enter the Password and then provide the authentication method.
• Click on the Test button to test the ODBC Connection. If the connection is successful then click on the Ok button to add the DSN.
• Now, start or restart the Tableau Desktop.
• In the left navigation under To a Server click on the More…  then click on the Other Databases (ODBC).
• From the dialog box select the DSN from the dropdown and click Connect.
• Click Sign In to connect MongoDB to Tableau Deskop.

Connect with Authentication and TLS/SSL

• Fill in the form fields by clicking on the Details button. 
• Fill in the Data Source Name of your choice.
• Then, fill the TCP/IP Server where your mongosqld process is running.
• Fill the Port as the port number of the mongosqld process.
• Now, fill in the required authentication form fields when running with –auth enabled.
• Enter the authenticated username of the user to the target Database in the User field.
• Enter the Password and then provide the authentication method.
• Now, you have to provide some details on SSL form fields. 
• Click on the Details>> option and then select the SSL tab to access the TLS/SSL details. 
• Fill in the location of the client/server in the SSL Key.
• Provide the location of the client/server certificate in the SSL Cert field.
• Similarly, provide the location of the SSL CA File and SSL Cipher.
• Then, set the SSL Mode to required and then provide the location of the PEM file that contains the RSA public key in the RSA Public Key.
• Click on the Test button to test the ODBC Connection. If the connection is successful then click on the Ok button to add the DSN.
• Now, start or restart the Tableau Desktop.
• In the left navigation under To a Server click on the More…  then click on the Other Databases (ODBC).
• From the dialog box select the DSN from the dropdown and click Connect.
• Click Sign In to connect MongoDB to Tableau Deskop.

Some limitations of this method

Black Box

There is a black box involved in the translation of SQL to MongoDB queries, making data inconsistencies challenging to diagnose.

Performance

This method frequently has issues with performance.

Nested Elements

The results of the columnar format are forced into strange ways since SQL does not accept arrays or nested elements.

Pricing

Only MongoDB Enterprise (or M10+ clusters for MongoDB Atlas) may use the BI connection. The alternatives to the ODBC driver need to be bought individually.

Separation

Your transactional datastore and reporting source are one and the same. 

That’s it! You can use any of the listed methods to connect MongoDB to Tableau. Before wrapping up, let’s cover some basics.

What is MongoDB?

It is a high-performance document-oriented database that is powered by a NoSQL structure. It makes use of collections (tables) each having multiple documents (records) & allows the user to store data in a non-relational format. 

MongoDB stores its data as objects which are commonly identified as documents. These documents are stored in collections, analogous to how tables work in relational databases. MongoDB is known for its scalability, ease of use, reliability & no compulsion for using a fixed schema among all stored documents, giving them the ability to have varying fields (columns). 

Introduction to Tableau

Tableau is a powerful business intelligence tool used to turn raw data into an understandable format. It is a tool popularly used to visualize data and can be understood even by a non-technical user. 

It creates visualizations with the help of dashboards and worksheets, helping users perform real-time analysis in a very fast and secure manner. It doesn’t require a user to have any programming skill or technical background to operate it.

Why Connect MongoDB to Tableau is Needed

Connecting MongoDB to Tableau is like merging leading Visual Analytics with modern Databases for rapid application development.  enabling effortless import of MongoDB data to Tableau for comprehensive data visualization. Below are some use cases of MongoDB Tableau integration.

A Sea of Unstructured Data

More than 80% of the data is available in an unstructured or semi-structured format, and most of this data is in JSON format because of its simple syntax and usage. The growth of unstructured data is going to increase rapidly because of the popularity and usage of web applications, IoT devices, mobile apps that use JSON data format.

Connect MongoDB to Tableau for Easy Data Consumption and Analysis

Today, almost every company needs to apply data-driven business decisions in action to stay ahead of the competition and grow faster. The data stored in MongoDB Database is transferred to Relational Database or Hadoop so that it can transform into a format interpretable by Tableau for Data Analysis. Connecting MongoDB to Tableau allows easy and fast access to analyzing and visualizing JSON data opens doors to data-driven decisions.

MongoDB is a document Database used for developing flexible mobile apps, IoT apps, etc. It enables companies to build dynamic schemas resulting in faster and reliable integration, testing, development, and greater agility. Flowing data from MongoDB to Tableau allow companies analyze theri data at larger scale and access enterprise tools specially designed to handle complex Data Analytics, Visualization and Reporting.

Access JSON Data in MongoDB

Now MongoDB comes with a pre-built connector for flowing data from MongoDB to Tableau that makes the job even easier and allows users to easily connect MongoDB to Tableau for data flow. The connector first creates an initial relational view definition of the JSON data. After that, it either converts a SQL statement to a MongoDB Aggregation Pipeline query or a straight find operation based on the query. Flowing data from MongoDB to Tableau automates the Data Integration process and can be used to feed real-time data to the Dashboard.

Conclusion

This article introduces you to the various methods used to connect MongoDB to Tableau. It also provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently. These methods, however, can be challenging especially for a beginner & this is where Hevo saves the day.

Hevo Data, a No-code Data Pipeline helps to combine data from MongoDB along with multiple data sources and visualize it in Tableau. Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Visit our Website to Explore Hevo

Want to take Hevo for a spin? Sign Up for the 14-day free trial!

Tell us about your preferred way to connect MongoDB to Tableau! Share your thoughts with us in the comments section below.