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:

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:

FeatureSupported
Chunking
Data Agent
Encryption at Ingest
Incremental Load
Multi-Source
OAuth
Performance Optimized
Remote
Single-Source
Spark Extraction
Webhook Callbacks

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:

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

After enabling the MongoDB Atlas Connector for BI, to properly read your cluster’s database, restart your cluster.

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

Note

For a MongoDB Atlas datasource: In the connection properties, enter the key/value pair enabledTLSProtocols=TLSv1.2. MongoDB Atlas requires security protocols (SSL/TLS). The key/value property configures the JDBC driver to use TLS version 1.2 and above.

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 + NewAdd 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:

PropertyControlDescription
Data Source Nametext boxEnter the name of the data source
Usernametext boxEnter the database username
Passwordtext boxEnter the database password
Connection Pooltext boxEnter the connection pool. The default is 30.
Connection Stringtext boxEnter the connection string for the MongoDB JDBC driver. The format is: jdbc:MongoDB://<HOST>:<PORT>/<DATABASE_NAME>
The default JDBC port for MongoDB Connector for BI is: 3307. Verify port numbers with your datasource configurations. Ensure that the database_name is included. An erroneous successful connection is possible without a specified database.
Connection Propertiestext boxEnter the customized key/value pair properties, as applicable. See below for details on connection properties.For MongoDB Atlas, you must include the key/value pair: enabledTLSProtocols=TLSv1.2
Use Data AgenttoggleEnable using a data agent to securely ingest data from an external data source that is behind a firewall.
For more information, please review Tools → Data Agent and Tools → Data Manager.
Data Agentdrop down listEnable Use Data Agent to configure this property. Select from the data agents created in the tenant, if any.

Important: Data Agent

A data agent is a service that runs on a remote host. It is also a data agent object in the Data Manager for a given tenant. An authentication file shared between the data agent object and the data agent service enables an authorized connection without using a VPN or SSH tunnel. With a data agent, you can securely extract data from one or more databases behind a firewall to an Incorta cluster. Your Incorta cluster can reside on-premises or in the cloud. A CMC Administrator must enable and configure an Incorta cluster to support the use of Data Agents. Only a Tenant Administrator (Super User) or user that belongs to a group with the SuperRole role for a given tenant can create a data agent that connects to a data agent service. To learn more, see Concepts → Data Agent and Tools → Data Agent.


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:

PropertyControlDescription
Typedrop down listDefault is SQL Database
Data Sourcedrop down listSelect the MongoDB external data source
IncrementaltoggleEnable the incremental load configuration for the schema table. See incremental load.
Fetch Sizetext boxUsed for performance improvement, fetch size defines the number of records that will be retrieved from the database in each batch until all records are retrieved. The default is 5000.
Querytext boxEnter the SQL query to retrieve data from the MongoDB database
Update Querytext boxEnable Incremental to configure this property. Enter the SQL query to retrieve data updates from the MongoDB database.
Incremental Field Typedrop down listEnable Incremental to configure this property. Select the format of the table data column:
  ●  TImestamp
  ●  Unix Epoch (seconds)
  ●  Unix Epoch (milliseconds)
CallbacktoggleTurn this option on to enable the Callback URL field
Callback URLtext boxThis property appears when the Callback toggle is turned on. Specify the URL.

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

Since MongoDB is a NoSQL database with no relationships, the translation to a SQL styled schema can leave desired joins unidentified. After creating the schema, identify and create any desired joins in the Join Editor.

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.

Note

Changing the incremental load strategy requires a full load to ensure data integrity.

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` > ?

Note

? is a variable in the update query that contains the last schema refresh date.

Incremental Field Type = Timestamp

Note

If running an update query for an incremental load, you are able to use the ? reference character. The ? character will be replaced with the last incremental reference to construct a valid query to the database. The ? reference character is not valid in a standard query.

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.