Accessing Your Data Using Business Intelligence (BI) Connectors

Overview

Your applications capture a large amount of data when end-users interact with them. From submission data to user activity to metadata, this volume of data can be overwhelming. But this data is useful if arranged in a way that is easy to digest. If you want to aggregate this data to make business decisions, you might connect your MongoDB or MySQL databases to a BI (Business Intelligence) tool. For example, the MongoDB Connector for BI is software that translates SQL queries to MongoDB queries. It gives you actionable BI from their data. Unqork can configure a database connections to your Production environment. These connection create SQL-like views that work with popular BI tools. It also works with other tools that can handle Open Database Connectivity (ODBC). You have the flexibility to select the tools that work best for you.

What You'll Learn

In this article, you'll learn about MongoDB and MySQL connectors and how to set them up for BI.

How the Connectors Work

Your end-users produce data every time they interact with your application. You can access this data live or create a periodic extract to store the data in your own data warehouse. Your MongoDB or MySQL database stores this data. An ODBC driver connects your MongoDB or MySQL database to your BI tool. For example, the MongoDB Connector for BI translates your SQL queries between your BI tool and MongoDB when you query data. Your BI tool retrieves and visualizes the data from your database. The MongoDB Connector for BI acts as a layer between your reporting tool and your MongoDB.

TIP  Use the MongoDB Connector for BI to extract critical data required for reporting or BI analysis. Extracting large sets of data from your MongoDB database with the MongoDB Connector for BI might cause performance issues.

How to Set Up the MongoDB and MySQL Connections for BI

Unqork sets up the MongoDB and MySQL connectors for you, so you only need to provide information to Unqork.

Setup

If you decide the MongoDB or MySQL connectors can benefit your organization, notify your Unqork representative. Your Unqork representative provides you with the BI Connector View Creation or Update Request Form. Complete the form and return it to your Unqork representative.

You'll provide Unqork with an IP address or ranges to include in the allowlist. Allowlists must be specific to each environment.

NOTE  If you anticipate <NULL> values or empty arrays, you'll need to notify Unqork.

After you provide your allowlist, Unqork sets up your connection and any Views you request. Unqork also provides you with a server address, username, and password so you can access your data. When that's complete, you can connect your chosen BI tool to MongoDB or MySQL.

NOTE  You need at least one View available before connecting your BI tool.

The last thing you need to do is validate your Views to ensure you have everything you need.

TIP  For information on how to connect the MongoDB Connector for BI to a compatible BI tool, see https://docs.mongodb.com/bi-connector/current/client-applications/. For more information on how to connect MySQL to a compatible BI tool, see https://learn.microsoft.com/en-us/power-query/connectors/mysql-database.

Testing

Unqork configures the connectors for each environment. Connection testing can occur before a Production deployment. To test data, you'll need to ensure that lower environments have comprehensive and quality data.

NOTE  Unqork needs an allowlist for lower environments if you want to perform testing in them. All changes must be promoted sequentially through lower environments. For example, if the MongoDB Connector for BI was set up before Production, you'll need to test the Views in each environment before promotion to Production.

When testing, consider the following:

  • Connectors are a SQL-like layer on top of your data. They are not a database. If you need to perform complex manipulations, extract the data to your data warehouse and manipulate it inside your ecosystem.

  • Don't set up connectors until configuration is stable.

  • If an application update results in a change to the data model, the BI tool View might break. Notify your Unqork representative if you make major configuration changes after your initial launch.

  • The MongoDB Connector for BI supports array data types. In a relational format, it creates Child Views that can join to the Main View.

  • All fields that you want to extract must have values in the submission data.

  • VPN for data transfer is not supported.

  • When BI Views update, validate the potential impact to connected downstream BI tools. Update downstream BI tools at the same time Unqork updates BI Views.

NOTE  If you need to make any updates to your Views after launch, you must submit a Zendesk ticket. In the ticket, complete and attach the BI Connector View Creation or Update Request Form.