Connectors → MongoDB BI

About MongoDB and the MongoDB Connector for BI

MongoDB is a NoSQL document database. A document database stores records where a record is a document. A document consists of data in a standard data structure or format such as XML or JSON.

In MongoDB, each record is a document. A document consists of data contained in a structure of field-value pairs. The values of fields may include other documents, arrays, and arrays of documents. In this regard, MongoDB documents are similar to JSON objects and key-value pairs. MongoDB encodes documents in a binary serialization format known as BSON.

MongoDB stores documents in collections. Collections are analogous to tables in a relational database. Application developers typically interface with a MongoDB database using JavaScript APIs. The mongo shell itself is an interactive JavaScript interface to a MongoDB server. A read operation for retrieving records from a given MongoDB database using the mongo shell is similar to the following:

Copy
db.myCollection.find(
 { age: { $gt: 18 } },
 { name: 1, address: 1 }
)limit(5)

The MongoDB Connector for BI provides a structured schema for SQL queries. The MongoDB Connector for BI translates the SQL queries into MongoDB read operations for the given MongoDB database. In other words, by interfacing with the MongoDB Connector for BI, you can query a collection of MongoDB documents with SQL. The MongoDB Connector for BI accepts SQL queries using the MySQL protocol. In this manner, common Business Intelligence (BI) tools such as Tableau or Microsoft Power BI are able to query a MongoDB database using SQL.

About the Incorta MongoDB BI Connector

The MongoDB BI Connector, available in Incorta, connects to an external data source for a MongoDB database or MongoDB Atlas database. The Incorta MongoDB BI Connector interfaces with the MongoDB Connector for BI when querying a MongoDB instance. The Incorta MongoDB BI connector operates with the 5.1.48 MySQL driver and supports the following Incorta specific functionality:

Deployment Steps for the MongoDB Connector for BI

If you are connecting to a database in a MongoDB server, a Database Administrator or System Administrator needs to install and configure the MongoDB Connector for BI.

Here are the high-level steps:

• Configure the MongoDB Connector for BI for your database.
• Configure TLS for MongoDB Connector for BI.
• Complete the necessary authentication configurations between the MongoDB database and the MongoDB Connector for BI.
• Create a system Data Source Name (DSN). See Authentication and Security for more information.
• Generate a mapped relational schema of the database.

After deployment of the MongoDB Connector for BI, you will be able to create an external data source using the Incorta MongoDB BI connector. The connector will use the DSN and mapped relational schema.

Deployment Steps for the MongoDB Atlas BI Connector

A MongoDB Atlas cluster can enable a MongoDB Atlas Connector for BI. This Connector for BI has a built-in functionality for valid MongoDB Atlas clusters. For a MongoDB Atlas cluster to have access to the Connector for BI, it must be at least a M10 Tier cluster. Here are the high-level steps:

• For your MongoDB Atlas cluster, enable the MongoDB Atlas Connector for BI.
• In the MongoDB Atlas cluster control, select Connect.
• In the Choose a connection method menu, select Connect Your Business Intelligence Tool.
• In the Connect window, you will use the hostname and port to create an external data source for the MongoDB Atlas database. You, your Database Administrator, or System Administrator should take note of the hostname and port number for later use.

Authentication and Security

The MongoDB Connector for BI uses administrative credentials to authenticate with MongoDB and generate a schema. When a client connects to the MongoDB Connector for BI, the connector will pass the client authentication credentials to MongoDB. The MongoDB Connector for BI will then display or restrict access to data according to the user permissions.

The MongoDB ODBC Driver for BI Connector provides connectivity between a SQL client and MongoDB Connector for BI. The Open Database Connectivity (ODBC) driver is used with a DSN. The DSN will store connection details for the ODBC data source. Credentials from the client are passed through the ODBC and MongoDB Connector for BI.

MongoDB Atlas Authentication and Security

Atlas utilizes whole disk encryption for data, including cluster and backup data. Atlas requires TLS encryption for all client and intra-cluster communications. Atlas utilizes an IP Access List for clients, which is a required configuration for security. In addition, client connections require user authentication. MongoDB Atlas requires Transport Layer Security protocols (TLS) in communications. When configuring the data source for Incorta you will adjust the connection properties to enable TLS communication.

Steps to connect MongoDB Connector for BI and Incorta

To connect MongoDB and Incorta, here are the high level steps, tools, and procedures:

• Create an external data source
• Create a schema with the Schema Wizard
• or, Create a schema with the Schema Designer
• Load the schema
• Explore the schema

Create an external data source

In the connection string property, ensure that the database_name is added. Failing to do so may result in a false-positive connection as there will be no specified database in the connection.

Here are the steps to create a external data source with the MongoDB connector:

• Sign in to the Incorta Direct Data Platform.
• In the Navigation bar, select Data.
• In the Action bar, select + New → Add Data Source.
• In the Choose a Data Source dialog, in Database, select MongoDB.
• In the New Data Source dialog, specify the applicable connector properties.
• To test, select Test Connection.
• Select Ok to save your changes.

MongoDB connector properties

Here are the properties for the MongoDB connector:

Connection Properties

The connection properties allow for customized connection to your MongoDB database. The properties are accepted in a key=value format. The type of connector properties can range from username and password to SSL settings. The 5.1.48 MySQL driver determines the available connection properties. For a list of connection properties available with Incorta and a MongoDB database refer to Configuration Properties for Connector.

Create a schema with the Schema Wizard

Here are the steps to create an MongoDB schema with the Schema Wizard:

• Sign in to the Incorta Direct Data Platform.
• In the Navigation bar, select Schema.
• In the Action bar, select + New → Schema Wizard.
• In (1) Choose a Source, specify the following:
  • For Enter a name, enter the schema name.
  • For Select a Datasource, select the MongoDB external data source.
  • Optionally create a description.
• In the Schema Wizard footer, select Next.
• In (2) Manage Tables, in the Data Panel, first select the name of the Data Source, and then check the Select All checkbox.
• In the Schema Wizard footer, select Next.
• In (3) Finalize, in the Schema Wizard footer, select Create Schema.

Create a schema with the Schema Designer

Here are the steps to create an MongoDB schema using the Schema Designer:

• Sign in to the Incorta Direct Data Platform.
• In the Navigation bar, select Schema.
• In the Action bar, select + New → Create Schema.
• In Name, specify the schema name, and select Save.
• In Start adding tables to your schema, select SQL Database.
• In the Data Source dialog, specify the MongoDB table data source properties.
• Select Add.
• In the Table Editor, in the Table Summary section, enter the table name.
• To save your changes, select Done in the Action bar.

MongoDB table data source properties

For a schema table in Incorta, you can define the following MongoDB specific data source properties as follows:

View the schema diagram with the Schema Diagram Viewer

Here are the steps to view the schema diagram using the Schema Diagram Viewer:

• Sign in to the Incorta Direct Data Platform.
• In the Navigation bar, select Schema.
• In the list of schemas, select the MongoDB schema.
• In the Schema Designer, in the Action bar, select Diagram.

Load the schema

Here are the steps to perform a Full Load of the MongoDB schema using the Schema Designer:

• Sign in to the Incorta Direct Data Platform.
• In the Navigation bar, select Schema.
• In the list of schemas, select the MongoDB schema.
• In the Schema Designer, in the Action bar, select Load → Load Now → Full.
• To review the load status, in Last Load Status, select the date.

Explore the schema

With the full load of the MongoDB schema complete, you can use the Analyzer to explore the schema, create your first insight, and save the insight to a new dashboard.

To open the Analyzer from the schema, follow these steps:

• Sign in to the Incorta Direct Data Platform.
• In the Navigation bar, select Schema.
• In the Schema Manager, in the List view, select the MongoDB schema.
• In the Schema Designer, in the Action bar, select Explore Data.
• Manually address Type Conflicts generated by the schema mapping.

Additional Considerations

Incremental Load

When you enable incremental load for a MongoDB BI Connection there is one type of incremental load available.

Time-Based Incremental Loading: Fetch updates since the last time the tables were loaded. Determined by the difference between the current time and the database timestamp.

Incremental Load Example

In this example, the invoices table must contain a column of the type Date or Timestamp in order to load the table incrementally with a last successful extract time strategy. In this case, the name of the date column is ModifiedDate and the format of the column is Timestamp.

Here are the data source property values for this example:

Incremental is enabled

Query contains SELECT * FROM `invoices`

Update Query contains SELECT * FROM `invoices` WHERE `ModifiedDate` > ?

Incremental Field Type = Timestamp

Valid Query Types

When creating a query for your MongoDB Connector, only SELECT statements are valid. You are not able to use INSERTS or UPDATES as a query in your MongoDB Connector.

Type Conflicts

MongoDB’s flexible schema model allows for multiple data types. When converting to a relational schema type conflicts can arise. The MongoDB Connector for BI uses a lattice prioritization to resolve any data type conflicts. Be aware of how these type conflict resolutions can affect data types once in Incorta. If necessary, you can change the Incorta data type of columns in your schema.