MongoDB Overview

The ElastiCube Manager enables easy and quick access to databases, tables and views contained within MongoDB databases.

Sisense provides a MongoDB connector for the ElastiCube. Click here to download the driver.

Note: Before connecting to MongoDB database with Sisense, please note that MongoDB is an unstructured database, and therefore tables may be flattened with additional tables being created for nested items.

To connect and define MongoDB, follow these steps:

  1. Download the MongoDB ODBC Driver and connect to the data source in the ElastiCube Manager.
  2. Add a DSN.
  3. Review and edit the DSN schema.
  4. Preview and select the tables generated by the MongoDB ODBC Driver.
  5. (Optional) Change advanced DSN configurations.
  6. (Optional) Connect to MongoDB ODBC Driver using a Connection String (alternative method to step 2 above).

Upgrading to MongoDB v2.0

The latest version of MongoDB provides enhanced security and improved handling for complex MongoDB models.

The latest version of MongoDB makes significant breaking changes, if you use a version of MongoDB prior to version 2.0, and you are satisfied with your service, you can continue without upgrading.

When to Upgrade?

In certain circumstances it is recommended or necessary that you upgrade your driver for  MongoDB version 2.0, for example:

  1. You are new to Sisense and MongoDB
  2. You want to connect with the Sisense Native REST Connector
  3. You experience security-related issues in MongoDB v1.0
  4. You have a really complex MongoDB model that v1.0 can’t handle

Before Upgrading

When upgrading to MongoDB v2.0, your current DSN names configured locally may be deleted. As a workaround, you can export the ODBC registry entry and then import after completing the upgrade.

To migrate your ODBC Data Source registry file:

  1. In Windows, open the Run box with by pressing the Windows key + r.
  2. In the Run line, enter regedit.
  3. Click OK.
  4. Navigate to the ODBC Data Source registry entry at the following location:
    HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
  5. Right click the entry and select Export.
    regexport
  6. Save the registry file on your machine.
  7. After installing MongoDB v2, click the exported registry file. The Register Editor warning message is displayed.
    regedit
  8. Click Yes to import the entry.

After Upgrading

After upgrading to MongoDB v2.0, note the following:

  1. The new ODBC schema creation is different than v1.0. MongoDB v2.0 will not create virtual tables (_vt_ tables) in the ElastiCube Manager. As a result, your current ElastiCube schema will not match schemas created before upgrading. Create a new schema using the new v2.0 DSN.
  2. The MongoDB v2.0 driver saves a JSON format of the extracted schema, as opposed to the v1.0 driver which uses XML.
  3. The MongoDB v2.0 driver stores the schema extracted in MongoDB by default. This allows  multiple users to work with the same extracted schema from different servers without extracting a new schema individually each time. However, any changes to the schema affects the schema for everyone else.
  4. Storing the schema in the MongoDB means your DB is not ReadOnly. Administrators should be aware of this and protect their database with authentication and access control, or communicate this store action and its effects to all users.

Downloading and Connecting to the MongoDB ODBC Driver

To connect to MongoDB, complete the following procedures:

  1. Download and install the MongoDB ODBC Driver.
  2. In the ElatiCube Manager, click Add Data, and then Generic ODBC Driver.
  3. Select DSN. If a DSN file has already been created, look for it in the drop-down list, and click Test Connection.
  4. If you need to add a DSN, see the next section. If your connection has been set up, you can click OK and review the tables generated by the MongoDB ODBC Driver.

png;base644de15833e2ace415

Adding a DSN

If a DSN has not yet been created, you will need to add it as follows:

  1. Click Add DSN.

Note: To add a DSN you must run the Sisense ElastiCube Manager as an administrator.

  1. Select the System Data Source option.  The created file will apply to all users in a specific machine only. Click Next. 
    png;base644de15833e2ace415
  2. Select the previously installed Sisense MongoDB ODBC Driver, and click Next, and then Finish.

create new data

  1. Open the MongoDB ODBC Driver from the ODBC Data Source Administrator.
  2. Provide a Data source name. The DSN file will be available thereafter when the user/system data source is chosen.
  3. In the Host field, enter the DB hosting machine’s IP address (if the DB is stored locally, enter localhost).
  4. In the Port field, enter the MongoDB default port which is 27017.
  5. In the Database field, enter the name that can be found by connecting to the DB using Mongovue/RoboMongo. Note: The name is case sensitive.
  6. If authentication is required, then enter the Username.
  7. Use Additional servers when connecting to a shared cluster or a replica set. Type in a comma-separated list of additional servers that you need, indicate the port on which a server is listening by appending a colon (:), and the port number to the server name or IP address.
  8. Select Connect to a replica set if you are connecting to a replica set, and type the name of the replica set in the Replica set name field. Make sure that the names of the secondary servers in the replica set are listed in the Additional servers field, as described in the previous step.
    data name

DSN Schema Definition

  1. Open the MongoDB ODBC Driver from the ODBC Data Source Administrator.
  2. Click Schema Definition to review the schema and make changes if needed:
  • External schema file: The driver automatically generates the schema if not manually defined (path to file is displayed). To edit an existing DSN schema, click Browse to locate and open the file in the ODBC Data Source Administrator.
  • Export options include:
    • Export Existing: Exports metadata that has already been generated.
    • Generate All: Exports metadata for all the tables in the database.
    • Generate Missing: Exports metadata for tables that have not been included in the schema.

pngbase64b9edec97add7b373_1

  • Edit Schema File: Use this option to edit the schema before updating metadata (change field types, hide columns etc.).

edit schema

  • Upload Metadata: Updates metadata after settings are complete. Current metadata source displays the source that has been set in Advanced Options.
  • Clear Existing Metadata: Deletes all the metadata that the driver has generated for the MongoDB instance.
  • Virtual Tables Options: Sisense enables virtual tables creation by default. Virtual tables are created when Sisense detects an array within a field of the main table. Virtual tables can be disabled or custom configured. By default, virtual tables will receive the name MAINTABLE_vt_FIELD and the main table will receive the suffix “main”. We recommend leaving these settings as is.
    png;base642f771fe853862cb2
  1. When done, click Test Connection, and OK.

Selecting Tables in the ElastiCube Manager

After setting up the DSN, you can preview and customize the query (similar to other ODBC connections) of the imported MongoDB table.

In the ElastiCube Manager You can view the SQL syntax in the Query Preview section. Click Edit to customize it.

Select the tables created by the Sisense MongoDB ODBC Driver (tables including MAINTABLE_vt_FIELD), as opposed to the table that includes all the raw data (with suffix main).

mongo table

DSN Advanced Options

Click Advanced Options if you require additional settings:

  • Document fetched per block: Maximum number of documents (rows) that the query returns each time.
  • Nested column separator: Characters/strings used to separate names of nested columns.
  • Read preference: Specifies how the driver routes read operations to the members of a replica set.
  • Sampling strategy: Location in the table to sample rows from when detecting the schema (beginning, end or random position in table). End is recommended.
  • Documents to sample: Number of documents to sample when detecting schema for a table (0 scans all documents).
  • Use SQL_WVARCHAR for string data types: Select this option for the driver to map the MongoDB String data type to SQL_WVARCHAR instead of SQL_VARCHAR.

png;base64fb8ce7e684222946

Connecting to Sisense MongoDB ODBC Driver using a Connection String

If you have a driver already defined, then you can connect to Sisense MongoDB ODBC Driver using strings (without using a DSN).

  1. Select Connection String (DSN-Less).

png;base64fcec2941347a2a69

  1. Type in the connection details as strings, as follows:

Example with minimum required attributes

Driver=Sisense MongoDB ODBC Driver;
Host=localhost;
Port=27017;
Database=aff;

Example with with advanced options:
Driver=Sisense MongoDB ODBC Driver

Host=192.168.100.100;
Port=27017;
Database=MyDatabase;
UID=MyUsername;
PWD=MyPassword;
RowsFetchedPerBlock=4096;
DefaultStringColumnLength=255;
DefaultContainerColumnLength=511;
UseSqlWVarchar=0;
CacheMetadata=1; VirtualTableDetection=1
  1. When done, click Test Connection, and OK.