Data can be collected, measured, and analyzed using various tools to extract hidden patterns and gain better insights. These are used for the growth of a product, a team, or an organization.
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.
Table of Contents
What is MongoDB?
Image Source: MicroStrategy Community
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).
For further information on MongoDB, you can check the official site here.
Introduction to Tableau
Image Source
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.
For further information on Tableau, you can check the official site here.
Hevo Data, a No-code Data Pipeline helps to combine data from MongoDB along with multiple sources and visualize it in Tableau. Hevo is fully managed and completely automates the process of not only transferring data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.
It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis using BI tools such as Tableau and many more.
Get Started with Hevo for Free
Check out what makes Hevo amazing:
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to export.
- Completely Managed Platform: Hevo is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
Simplify your data analysis with Hevo today!
Sign up here for a 14-Day Free Trial!
Why Connect MongoDB to Tableau is Needed
Connecting MongoDB to Tableau is like merging leading Visual Analytics with modern Databases for rapid application development.
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.
Prerequisites
- Working knowledge of MongoDB.
- MongoDB installed at the Host Workstation.
- Working knowledge of Tableau.
- Tableau installed at the Host Workstation.
- A general idea of using the command-line.
Methods to connect MongoDB to Tableau
There are multiple ways in which you can connect MongoDB to Tableau:
Method 1: Using MongoDB BI Connector
Image Source: Medium
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.
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.
Image Source: Intellipaat
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
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.
Image Source: Intellipaat
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.
Image Source: Tableau Help
For further information on the Initial SQL command, you check the official site here.
Image Source: Intellipaat
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 2: Using ODBC to Connect MongoDB to Tableau
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.
This can be implemented using the following steps:
Step 1: Installing BI Connector
You can install 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.
Image Source: MongoDB Docs
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.
Image Source: Medium
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.
Image Source
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 3: 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 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.
Image Source: Flerlage Twins
Once you have selected the schema, you will get an output representing data in a flattened way as follows:
Image Source: Flerlage Twins
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 4: 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.
Image Source
- 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
- 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.
That’s it! You can use any of the listed methods to connect MongoDB to Tableau.
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 sources and visualize it in Tableau. Hevo with its strong integration with MongoDB (among 100+ sources) allows you to not only transfer & load data but also transform & enrich your data & make it analysis-ready in a jiff.
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.