Home Services Partners Company
New Tableau to MongoDB ODBC connection that respects NoSQL data

New Tableau to MongoDB ODBC connection that respects NoSQL data

July 31, 2014 0 Comments

Tableau analysts may or may not know about the challenges in exposing NoSQL data sources, such as MongoDB, to data visualization tools like Tableau, Qlikview, Spotfire, Microstrategy, SAP Lumira, SAS, Birst, etc.  Progress DataDirect loves a good challenge and engineered the first reliable SQL connector to MongoDB using ODBC or JDBC.  Progress Software acquired the Modulus Node.js and MongoDB development platform, so we might know a thing or two about MongoDB data - rather than follow the widely unpopular flattened schema approach.

Getting Started on reliable Tableau connectivity to MongoDB:

1- Download and install the DataDirect MongoDB ODBC driver (32-bit or 64-bit depending on version of Tableau). Tutorial shows Tableau 8.2 working with the 64-bit MongoDB ODBC driver.

2- Create an ODBC Data Source to connect to MongoDB (see end of tutorial for sample data with arrays of embedded documents)

mongodb-normalized-sql-schema

3- Launch Schema Tool and select "Normalized View" to create an intelligent virtual schema with a single click of the OK button

mongodb-normalized-sql-schema2

mongodb-normalized-sql-schema3

4- Launch Tableau and connect to MongoDB ODBC data source

tableau-mongodb-odbc1

5- Bring in normalized virtual tables WORLDCUP and WORLDCUP_TEAMS.  Note, this data resides in MongoDB within a single collection.

tableau-mongodb-odbc2

6. Now, you can ask a simple question such as goals by country and last name.

tableau-mongodb-odbc3

7. For comparison, it's impossible to ask the same question as step #6 when the data is exposed in a flattened schema like existing SQL connectors.  Note how each individual team's players and goals are all separate measures and dimensions from the flattened view of the Worldcup data (TEAMS1_GOALS, TEAMS2_GOALS, TEAMS3_GOALS, etc).  This approach does not represent MongoDB or NoSQL technology very well from Tableau.

tableau-mongodb-odbc4

What's the alternative to DataDirect's normalized and scalable virtual schema for Tableau?

To get an equivalent reliable connectivity experience from Tableau, you would need to:

  • Sample MongoDB data to understand the data model
  • Write mapping code to extract and normalize the data
  • Physically load it into a relational database
  • Create some kind of CDC capability for near real-time data connectivity

In summary, use the DataDirect MongoDB ODBC driver. It's easy to get started today. All you need to do is start a free trial, and you're off!

Try Now

Sample Data:

> db.worldcup.insert({ "_id" : 1, "year" : 2014, "location" : "Brazil", "teams": [ {"country" : "Brazil", "first" : null, "last" : "Jefferson", "goals" : 0 },{"country" : "Brazil", "first" : "Dani", "last" : "Alves", "goals" : 0 }, {"country" : "Brazil", "first" : "Thiago", "last" : "Silva", "goals" : 1 } , {"country" : "Chile", "first" : "Claudio", "last" : "Bravo", "goals" : 0 }, {"country" :"Chile", "first" : "Eugenio", "last" : "Mena", "goals" : 0 } , {"country" : "Colombia", "first" : "David", "last" : "Ospina", "goals" : 0 }, {"country" : "Colombia", "first" : "Cristian", "last" : "Zapata", "goals" : 0 }, {"country" : "Colombia", "first" : "Mario", "last" : "Yepes", "goals" : 0 } , { "country" :"Germany", "first" : "Manuel", "last" : "Neuer", "goals" : 0 }, {"country" : "Germany", "first" : "Kevin", "last" : "Grosskreutz", "goals" : 0 } , {"country" : "Mexico", "first" : "Jose", "last" : "Corona", "goals" : 0 }, {"country" : "Mexico", "first" : "Francisco", "last" : "Rodriguez", "goals" : 0 }, {"country" : "Mexico", "first" : "Carlos", "last" : "Salcido", "goals" : 0 }, {"country" : "Mexico","first" : "Rafael", "last" : "Marquez", "goals" : 1 } , { "country" : "USA", "first" : "Tim", "last" : "Howard", "goals" : 0 }, {"country" : "USA", "first" : "DeAndre", "last" : "Yedlin", "goals" : 0 }, {"country" : "USA", "first" : "Omar","last" : "Gonzalez", "goals" : 0 }, {"country" : "USA", "first" : "Michael", "last" : "Bradley", "goals" : 0 }, {"country" : "USA", "first" : "Matt", "last" :"Besler", "goals" : 0 } ] } )

WriteResult({ "nInserted" : 1 })

> db.worldcup.insert({ "_id" : 2, "year" : 2010, "location" : "South Africa", "teams" : [ {"country" : "Uruguay", "first" : "Fernando", "last" : "Muslera", "goals" : 0 }, {"country" : "Uruguay", "first" : "Diego", "last" : "Lugano", "goals": 0 } , { "country" : "Netherlands", "first" : "Maarten", "last" : "Stekelenburg", "goals" : 0 }, {"country" : "Netherlands", "first" : "Gregory", "last" : "van der wiel", "goals" : 0 }, {"country" : "Germany", "first" : "Manuel", "last" :"Neuer", "goals" : 0 }, {"country" : "Germany", "first" : "Marcell", "last" : "Jansen", "goals" : 1 }, { "country" : "Spain", "first" : "Iker", "last" : "Casillas", "goals" : 0 }, {"country" : "Spain", "first" :"Raul", "last" : "Albiol","goals" : 0  } ] })

WriteResult({ "nInserted" : 1 })

Sumit Sakar

Sumit Sarkar

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others. 

Read next Big Data Scoop: AI, Data Science, MongoDB, Google Analytics
Comments
Comments are disabled in preview mode.