Known Issues for MongoDB Connector for BI
SQL Compatibility Issues
BI Connector Returns Empty Set Where MySQL Returns NULL Values
Some SQL aggregate functions
(like SUM, MIN, or COUNT) return NULL if there are no
matching values for that aggregate function. This response is
effectively an empty row.
Given the exact same SQL query, the BI Connector does not return
NULL for any aggregate functions; it returns an empty set instead.
Example
You are analyzing the test performance of students for each of the classes offered in the last year by a university. Using Tableau, you run a SQL query to find statistics for each class, including the highest and lowest test scores as well as the sum and count of all the scores in the class.
SELECT SUM(scores), COUNT(*), COUNT(scores), MAX(scores), MIN(scores) FROM students_2019 GROUP BY class;
Unfortunately, you accidentally run the query on the data for next year's classes, so there are no test scores for the query to find.
If the student data was stored in MySQL, this query would return a
single row with NULL values for fields that are calculated with
the SUM, MAX, and MIN functions and 0 for fields
that are calculated with the COUNT function.
SELECT SUM(scores), COUNT(*), COUNT(scores), MAX(scores), MIN(scores) FROM students_2019 GROUP BY class; +-------------+----------+---------------+-------------+-------------+ | sum(scores) | count(*) | count(scores) | max(scores) | min(scores) | +-------------+----------+---------------+-------------+-------------+ | NULL | 0 | 0 | NULL | NULL | +-------------+----------+---------------+-------------+-------------+
If the student data was stored in MongoDB and accessed via the BI Connector, this query would return an empty set.
SELECT SUM(scores), COUNT(*), COUNT(scores), MAX(scores), MIN(scores) FROM students_2019 GROUP BY class; Empty set (0.00 sec)
BI Connector Returns NULL Values Due to Mapping Error
A known mapping error can occur that causes BI Connector to return all NULL
values. This error occurs when you query a collection that
contains an array field that can be NULL, and the same array contains
an additional field that can also be NULL. A workaround for this error is to
create a view with the $match filter that only returns documents in which
the field $type is array with the createView method.
Example
For a collection named nextDeparture with an array field named
response.schedule that is sometimes NULL, you can create
the following view:
db.createView('nonullsched', '"nextDeparture"', [{'$match': {'response.schedule': {'$type': 'array'}}}])