I would like to present the results of my little analysis of the performance of Tableau reports built on two different data sources: SQL Server and MongoDB. The most important result is that for simple case scenario the difference of Tableau reports rendering times grow highly with the amount of test data but only in MongoDB case. Additionally Tableau report built on MongoDB fails to render in acceptable time when using SQL joins. This might be caused by imperfections of MongoDB ODBC connector.
- Introduction
The rendering time of BI reports is one of the biggest problems BI developers and users encounter today. These delays can have several causes, from reports complexity through the amount of data until DB models complexity. This performance issue appeared in several of my projects and that is why I decided to do a little investigation in search of the best solution. My goal was to compare Tableau reports rendering times for both SQL and NoSQL solutions. For this, I used “SQL Server 2017” and “MongoDB 4.0.10”. For reports building and performance measuring I used “Tableau Desktop 2018”. The biggest problem in creating an appropriate environment was Tableau Desktop – Mongo DB integration via MongoDB ODBC connector. I recommend using this “MongoDB BI Connector & ODBC driver” as a guide, it saves plenty of IT Engineer’s precious time. The environment was set up on a local machine thanks to that there were no network issues that could disturb performance results. In the case of DB structures, I limited models to the simple case: 2 tables in SQL Server and 2 collections for MongoDB.
- Tests
Tableau Desktop has built-in functionality to measure the performance of reports rendering. Thanks to that it is possible to monitor several performance components like: ’time of query execution’, ’rendering’, ’time required to connect to data source’ etc. I monitored only significant values – higher than 0.1 [s].
2.1 Test Data
For the purpose of performance analysis, I used historical data of US Interest Rates for 10 Year, 2 Year and 3 months treasuries. Originally 10 thousand records were multiplied by means of Cartesian Product, reaching 1 million records. The rendered data are present in [Figure 1].
2.2 Test Cases
I decided to measure the performance of Tableau reports for the following three test scenarios:
Performance Analysis of Tableau Reports built on SQL Server and MongoDB Data Sources
Figure 1: Test Data – 10-Year Treasury Minus 3-Month Treasury (top) and 10-Year Treasury Minus 2-Year Treasury (bottom)
- How much time Tableau Desktop needs to render reports built on both databases with the loading of test data. Starting with 250k records and finishing with 1 million records.
- How much time Tableau Desktop needs to render reports built on both databases when joining two tables/collections.
- How much time Tableau Desktop needs to render reports built on both databases when using a custom query.
- Results
I have noticed that data volume significantly affects rendering of reports built on MongoDB and only slightly SQL Server ones. With an increasing number of data time needed for reports to generate grows almost exponentially in MongoDB case as seen in [Figure 2]. In [Figure 3] and [Figure 4] I show how times of reports major components varies with different Data Sources. In fact, the only difference is on the ’Query Execution’ while ’Rendering’, ’Connecting to Data Source’ and ’Computing Layout’ remain similar with different data volume and complexity of the query.
- Conclusions
As we can see from test results, Tableau reports built on SQL Server is a better choice than MongoDB, at least in case of simple case DB model. This is mostly due to several translations of queries in MongoDB case. I have noticed that in order to query MongoDB via MongoDB ODBC connector Tableau needs to produce SQL query than MySQL query and finally MongoDB query. Obviously this causes the time difference. More complex DB model would definitely answer the question of which solution is better. It is also important to remember in preparation of additional test scenarios that MongoDB was created for slightly different purposes than SQL, hence we should not directly translate SQL DB model to MongoDB model.
Acknowledgements
I would like to thank Marek Januszkiewicz and Michalina Smolarkiewicz for helpful comments and advice.
Performance Analysis of Tableau Reports built on SQL Server and MongoDB Data Sources
Figure 2: Time [s] needed to generate a report against data volume
Figure 3: Time [s] needed for major components to complete – Joined two tables with 10k records each
Performance Analysis of Tableau Reports built on SQL Server and MongoDB Data Sources
Figure 4: Time [s] needed for major components to complete – Custom query on two tables with 1k records each
References
“MongoDB 4.0.10”. In: https: // www. mongodb. com/ download-center/ community .
“MongoDB BI Connector & ODBC driver”. In: https: // www. mongodb. com/ products/ bi-connector & https:
// github. com/ mongodb/ mongo-odbc-driver/ releases .
“SQL Server 2017”. In: https: // www. microsoft. com/ en-us/ sql-server/ sql-server-editions-express .
“Tableau Desktop 2018”. In: https: // www. tableau. com/ products/ desktop/ download .
