<Update: Since this post, Tableau has announced the upcoming availability of a MongoDB Connector by the end of 2015, looks promising…>
I am using the Alteryx MongoDB Connector to generate TDE files to feed Tableau, since Tableau does not offer dedicated extractors. I have to admit that the ramping up was rocky, and that anyone in that situation should benefit from the findings exposed in this post. Extraction is now swift from a AWS hosted MongoDB.
First things first, ensure you did set the proper source collection, and be aware of one flaw with the current version of the connector, as of Alteryx 9.5: it is missing the flag to send to the DB that would let you read from a Read Only configured MongoDB. It is on the priority list for V10. In the meantime, you should connect to the DB that can be written to. To ease your DBA, show that Alteryx workflows can’t write to the DB unless you drag into the workflow the MongoDB Output Tool.
Also, do yourself a favor and use the Properties / Criteria window to set filters on the indexed fields of your MongoDB. Since each MongoDB ObjectId contains at least an embedded timestamp of its creation time, this should be a required use.
After much research, I found out the precise syntax for filtering the extraction by date:
To use a Starting Date:
{_id: {$gt: ObjectId(“54a4fe800000000000000000”)}}
If you wonder what date it is, you can use this excellent website: http://steveridout.github.io/mongo-object-time/
Where inputting 54a4fe800000000000000000 in the ObjectID window would show you it translates to Jan 1, 2015.
To use a Date Range, the syntax is { $gt: value1, $lt: value2 }, or for an example that selects all records between Nov 1, 2014 and Nov 2, 2014:
{_id: {$gt: ObjectId(“545484f00000000000000000”),$lt: ObjectId(“5455d6700000000000000000”)}}
Last, if you need fancier filters, here is some additional help with the syntax: http://www.querymongo.com/
Ensure that the field you filter on are indexed in MongoDB, otherwise the extraction performance will be terrible. The next step will entail parsing the data using the Alteryx RegEx tool. Here is an example that will provide a table of Users ready to be loaded into Tableau:
RegEx code used is: .+?oid”.:.”(.*)”.\}
For more info on using RegEx, Tableau has provided a great starter kit, which is now part of Tableau 9:
https://www.tableau.com/about/blog/2015/6/become-regular-regular-expressions-39802
Pingback: Create a delta extractor for MongoDB with Alteryx | Insights Through Data