BI Connector - Complete MongoDB Tutorial

📊 What is BI Connector?

MongoDB BI Connector allows you to query MongoDB data using SQL and connect to popular business intelligence tools like Tableau, Power BI, and Excel for powerful data visualization and analysis.


-- Query MongoDB data using SQL
SELECT name, age FROM users WHERE age > 25

BI Connector Features

🔄

SQL Translation

Convert SQL to MongoDB queries

SELECT JOIN GROUP BY

🔌

BI Tool Support

Connect popular analytics tools

Tableau Power BI Excel

📈

Data Visualization

Create charts and dashboards

Charts Reports Dashboards

⚡

Performance

Optimized query execution

Caching Indexing Aggregation

🔹 Installing BI Connector

Download and install MongoDB BI Connector on your system. The connector acts as a bridge between SQL-based tools and your MongoDB database, translating queries automatically.

🔸 Installation Steps

Download BI Connector from MongoDB website
Extract the archive to your preferred location
Add the bin directory to your system PATH
Verify installation with version command

# Linux/macOS installation
tar -xvzf mongodb-bi-linux-x86_64-v2.14.4.tgz
cd mongodb-bi-linux-x86_64-v2.14.4
sudo cp bin/* /usr/local/bin/

# Verify installation
mongosqld --version

Output:

mongosqld version 2.14.4
git version: abc123def456
OpenSSL version: OpenSSL 1.1.1

🔹 Starting BI Connector

Launch the BI Connector service to enable SQL access to your MongoDB data. Configure connection settings, authentication, and schema mapping for optimal performance.

🔸 Basic Startup

# Start BI Connector with default settings
mongosqld --mongo-uri="mongodb://localhost:27017"

# Start with specific schema database
mongosqld \
  --mongo-uri="mongodb://localhost:27017" \
  --schemaSource=myDatabase

# Start with authentication
mongosqld \
  --mongo-uri="mongodb://username:password@localhost:27017" \
  --auth

🔸 Configuration File

# mongosqld.conf
systemLog:
  path: /var/log/mongosqld.log
  logAppend: true

net:
  bindIp: localhost
  port: 3307

mongodb:
  net:
    uri: "mongodb://localhost:27017"
  
schema:
  source: myDatabase

# Start with configuration file
mongosqld --config=/path/to/mongosqld.conf

🔹 Connecting BI Tools

Connect your favorite business intelligence tools to MongoDB through the BI Connector. Use standard MySQL connection protocols to access your data.

🔸 Connection Parameters

Host: localhost (or BI Connector server IP)
Port: 3307 (default BI Connector port)
Database: Your MongoDB database name
Username: MongoDB username
Password: MongoDB password

🔸 Tableau Connection

1. Open Tableau Desktop
2. Click "Connect" → "MySQL"
3. Enter connection details:
   - Server: localhost
   - Port: 3307
   - Database: myDatabase
   - Username: your_username
   - Password: your_password
4. Click "Sign In"

🔸 Power BI Connection

1. Open Power BI Desktop
2. Click "Get Data" → "Database" → "MySQL database"
3. Enter server: localhost:3307
4. Enter database: myDatabase
5. Choose authentication method
6. Click "OK"

🔸 Excel Connection

1. Open Excel
2. Go to "Data" → "Get Data" → "From Database" → "From MySQL Database"
3. Enter server: localhost:3307
4. Enter database: myDatabase
5. Provide credentials
6. Select tables and load data

🔹 SQL Query Examples

Query MongoDB data using familiar SQL syntax. The BI Connector translates SQL queries into MongoDB aggregation pipelines automatically.

🔸 Basic SELECT Queries

-- Select all documents
SELECT * FROM users;

-- Select specific fields
SELECT name, email, age FROM users;

-- Filter with WHERE clause
SELECT name, age FROM users WHERE age > 25;

-- Sort results
SELECT name, age FROM users ORDER BY age DESC;

-- Limit results
SELECT name, email FROM users LIMIT 10;

🔸 Aggregate Functions

-- Count documents
SELECT COUNT(*) FROM users;

-- Average age
SELECT AVG(age) FROM users;

-- Group by and count
SELECT country, COUNT(*) as user_count 
FROM users 
GROUP BY country;

-- Multiple aggregations
SELECT 
  country,
  COUNT(*) as total_users,
  AVG(age) as avg_age,
  MAX(age) as max_age
FROM users
GROUP BY country;

🔸 JOIN Operations

-- Inner join
SELECT 
  u.name,
  o.order_id,
  o.total
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;

-- Left join
SELECT 
  u.name,
  COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.name;

🔹 Schema Management

BI Connector automatically generates SQL schema from MongoDB collections:

🔸 Generate Schema

# Generate schema for database
mongodrdl --uri="mongodb://localhost:27017/myDatabase"

# Save schema to file
mongodrdl \
  --uri="mongodb://localhost:27017/myDatabase" \
  --out=schema.drdl

# Use custom schema with BI Connector
mongosqld \
  --mongo-uri="mongodb://localhost:27017" \
  --schema=schema.drdl

🔸 Sample Schema File

schema:
- db: myDatabase
  tables:
  - table: users
    collection: users
    pipeline: []
    columns:
    - Name: _id
      MongoType: bson.ObjectId
      SqlName: _id
      SqlType: varchar
    - Name: name
      MongoType: string
      SqlName: name
      SqlType: varchar
    - Name: age
      MongoType: int
      SqlName: age
      SqlType: int

MongoDB BI Connector