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.

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.

Methods to Connect MongoDB to Tableau

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

• Method 1: Using MongoDB BI Connector
• Method 2: Exporting MongoDB Data as JSON
• Method 3: Connect using Tableau Desktop

Method 1: 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. 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.

You can also lean about how to run Initial SQL command.

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.

Method 2: 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.

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 3: 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.
• 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

1. Black Box

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

2. Performance

This method frequently has issues with performance.

3. Nested Elements

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

4. 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.

5. 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.

Why Link MongoDB and Tableau for Analytics?

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.

1. 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.

2. 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.

3. 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.

Additional Resources for MongoDB Integrations and Migrations

• Stream data from mongoDB Atlas to BigQuery
• Move Data from MongoDB to MySQL
• Connect MongoDB to Snowflake
• Sync Data from MongoDB to PostgreSQL
• Move Data from MongoDB to Redshift

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. Try a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also, check out our unbeatable pricing to choose the best plan for your organization.

FAQ on Connect MongoDB to Tableau