Connecting via Tableau (Dremio Approach): Companies create data in a wide range of technologies, including relational databases, SaaS applications, NoSQL, Amazon S3, Hadoop, and other systems. In order to make sense of all this data, companies use BI tools like Tableau, Power BI, and Qlik, or data science products like Python and R.
Connecting to Elastic Search directly via Tableau is a lot more complicated if you are going to use Tableau’s Web Data Connector. You will have to build a Connector or find one over the Internet. Although there are many round-about ways to do this, let’s see a much simpler way to do this using Dremio.
Dremio was built to help analysts, data scientists, and data engineers be more effective with data.
Installing Dremio is as easy as clicking a few next buttons provided that you have JDK 1.8 installed. Still, here is a link for directions. There are options for Windows, Mac, and Linux.
If you are the first person accessing Dremio, you’ll be asked to create an administrator account. After setting up the Administrator Account you will be asked to log into Dremio.
1. Once you are authenticated, you will see an empty screen as shown below.
2. Sources are systems where data is managed in your organization.
3. Click on Add Source to connect to a specific source Elastic Search in our case.
4. Enter a name for your Source. This will be used to identify the connection in Dremio and Tableau. Also, input Host and Port No and set Authentication mode as per your Organizational Settings. You can add multiple hosts if needed.
Name: Connection Name
Encrypt connection: UNCHECKED
Managed Elasticsearch Service: CHECKED
5. After adding the source, you will be able to see all indexes available at the supplied host by clicking on it.
6. Our host has only one index as of now, Navigate, to the folder and click on the doc file.
7. Clicking on the doc file will redirect you to a new page as shown below. This may take some time depending on the size of your data source.
8. Click in the SQL editor area and type in the query as you would have used in Traditional SQL.
select * from es.cityevent.doc
9. Click on the drop-down option beside the Preview button on the top right side and select Run.
10. Now click on the drop-down button next to Tableau and install Dremio ODBC connector driver for the Environment you are in e.g. Windows, MacOS etc.
11. After Installing the driver just click on Tableau option from the drop-down. This will download a .tds file
12. This is a configuration file that makes it easy to launch Tableau connected to this virtual dataset over ODBC.
13. Opening the .tds file, which will launch Tableau connected to your virtual dataset.
Note: Tableau is establishing a live connection to Dremio, there is no Tableau extract being created.
14. If you’re on Windows, you should be prompted to log in to Dremio. If you’re on Mac you’ll be prompted to log in after you start to access the data. Enter your credentials for Dremio and you’ll now see your virtual dataset in Tableau, just as if it were a table in a relational database.
Connecting via Power BI (Dremio Approach): Connecting to Elastic Search is similar to tableau with the difference being the connector used to configure.
1. Launch the Power BI application and select Get Data.
2. Search for Dremio ODBC driver and select it.
3. Select continue and Enter the Dremio cluster name you want to connect to. To know about your Dremio Cluster settings go to Admin section on the Dremio UI page.
4. It will ask for Authentication details. After authenticating yourself, we will see all the Dremio Spaces and Sources you created previously listed below in a folder tree structure format.
5. Select the index you want to connect to and click on Load.
Now, you can create visualizations and draw Conclusions as per your need.