Connect Elasticsearch to Power BI: An Easy Guide

on BI Tool, Data Engineering, ETL, Power BI, Tutorials • August 7th, 2020 • Write for Hevo

ELASTICSEARCH TO POWER BI- Featured Image

Are you struggling to connect Elasticsearch to Power BI? Have you looked all over the internet to get the right information? If yes, then you have landed at the right blog. This blog introduces you to Elasticsearch, Power BI, and the steps to connect Elasticsearch to Power BI. As Power BI doesn’t support a direct connector for Elasticsearch, you will use an ODBC driver for this connection. 

Table of Contents:

What is Elasticsearch?

Elastisearch Logo: Elastisearch to Power BI
Image Source: KindPNG

Elasticsearch is an open-source real-time distributed engine. It is developed on top of Lucene. It is famous because it offers a simple Rest API. These REST APIs help you in responding to the request. Elasticsearch is based on documents and can be used in single-page applications, business analytics, enterprise search, etc. Elasticsearch uses indexing for running complex queries. Elasticsearch index is a collection of related documents. Each document correlates with a set of keys with corresponding values.

Key Features of Elasticsearch

  1. Multiple Search Options: Elasticsearch offers multiple searches such as customized splitting text into words, autocompletion, instant search, etc. 
  2. Scalable: It is scalable across multiple nodes. You can scale it horizontally and extend resources to balance loads between nodes and clusters.  
  3. Fast: It is fast as compared to other search engines. It is capable of executing complex queries in minutes. 
  4. Document Oriented: Elasticsearch stores data in structured JSON documents. It provides default indexing for your data.

What is Power BI?

Power BI Logo: Elastisearch to Power BI
Power BIImage Source: Meme Arsenal

Microsoft Power BI is a business analytics tool. You can visualize and analyze the data from various sources using the in-built connectors. It supports integration from sources such as MySQL, Oracle, Azure, Excel, etc. It provides a simple and intuitive user interface so that the customers can build their reports and dashboards. It helps us to get better insights into the organization’s data. It is available in three forms – Power BI Desktop, Power BI Service, and Power BI Mobile. Power BI can connect multiple data sets, transform it, and clean it to form a self-explanatory model.

Key Features of Power BI

  1. Customized Visualization: You can use custom visualize SDK to create your visualization in Power BI.
  2. Multiple Integrations: Power BI supports integration from multiple platforms using in-built connectors. 
  3. Secure: Power BI offers row-level security, which let’s grant rights access at multiple levels.
  4. Real-Time: Your data is analysis-ready always. Power BI can solve various problems and capture opportunities from them. 

If you would like to understand more about Microsoft Power BI, check out our helpful guide here.

Prerequisites

  1. Java Runtime Environment with version 7 or above. 
  2. Download and install Elasticsearch from this link.
  3. Download Elasticsearch ODBC driver from here
  4. Power BI Desktop.

Hevo Data: Integrate and Visualize your Data Conveniently

Hevo is a No-code Data Pipeline. Hevo supports pre-built integrations from 100+ data sources. It can automate your data flow easily. Hevo provides a reliable, secure, and consistent solution to its customers.

Get Started with Hevo for Free

Let’s discuss some unbeatable features of Hevo:

  1. Simple: Hevo provides a simple and intuitive user interface. It offers a minimal learning curve.
  2. Secure: Hevo makes sure that your data is safe by using two-factor authentication and end-to-end encryption.
  3. Scalability: Hevo is built to handle millions of records per minute without any latency.
  4. Fault-Tolerant: Hevo can automatically detect anomalies and informs you instantly. All affected rows are kept aside for correction.
  5. Real-Time: Hevo supports data migration in real-time. So, your data is always ready for analysis.
  6. Data Transformation: It provides a simple interface to perfect, modify, and enrich the data that you want to export. 

Give Hevo a try!

Sign up here for a 14-Day Free Trial!

Steps to Configure Elasticsearch SQL DSN

To establish a connection between Elasticsearch and Power BI, you need to install and configure the ODBC driver. Follow these steps to configure your DSN:

  1. Go to your administrative tools and select your ODBC driver. 
  2. In the panel, select “System DSN” and click “Add..”.
System DSN: Elastisearch to Power BI
Image Source: Elasticsearch Guide
  1. Click on “Elasticsearch Driver” and “Finish”
Select Data Source: Elastisearch to Power BI
Image Source: Elasticsearch Guide
  1. Now in the Elasticsearch SQL ODBC Driver DSN Editor, select the “Basic” tab. Specify the name, description, cloud ID, hostname, port, username, and password for your Elasticsearch. 
    • Name: The name that you will refer to.
    • Description: The description for your DSN. 
    • Cloud ID: It can be obtained from your Elasticsearch cluster in Cloud Console. 
    • Hostname: Specify the IP address for your DSN.
    • Port: It specifies the port that listens to Elasticsearch. By default, it is 9200.
    • Username and Password: Provide your credentials for access.
Provide Credentials: Elastisearch to Power BI
Image Source: Elasticsearch Guide
  1. In the Security tab select any one of the following SSL.
Enabled Certificate: Elastisearch to Power BI
Image Source: Elasticsearch Guide
  1. In the “Logging” tab, select “Enable Logging” and select “Debug” in Log Level.
Select DEUG: Elastisearch to Power BI
Image Source: Elasticsearch Guide
  1. Click “Test Connection”. If it is successful, you can click “Save”.
Download the Whitepaper on Automating Data Integration
Download the Whitepaper on Automating Data Integration
Download the Whitepaper on Automating Data Integration
Learn the key benefits of automating Data Integration

Steps to Connect Elasticsearch to Power BI

You will use the Elasticsearch SQL ODBC driver in this module. Follow these steps to connect Elasticsearch to Power BI:

  1. Go to Power BI, select “Get Data” from the home ribbon.
  2. In the left-side panel, select “Other” and then select the “ODBC” on the right side and click “Connect”.
Select ODBC: Elastisearch to Power BI
Image Source: Elasticsearch Guide
  1. In the ODBC panel, select your DSN for Elasticsearch and click the “OK” button.
Select DSN and Click Ok: Elastisearch to Power BI
Image Source: Elasticsearch Guide
  1. Now, in the navigator panel, select your data and click “Load”.
Click on Load: Elastisearch to Power BI
Image Source: Elasticsearch Guide
  1. You will get the data on the right side of your screen in Power BI. Visualize your data as you need. 

Limitations

Connecting Elasticsearch to Power BI has a lot of limitations as mentioned below:

  1. As there is no direct connector for Elasticsearch in Power BI, so you have to download and configure the ODBC driver on your system to establish a connection.
  2. Installation and configuration of the ODBC driver is a time-consuming process.

Conclusion

In this blog, you have learned about Elasticsearch, PowerBI, and steps to connect Elasticsearch to Power BI. Even though Power BI doesn’t support a direct connector for Elasticsearch, you can still establish a connection between these two platforms to get better insights into the data using the ODBC driver. If you want to integrate data from multiple sources, including Elasticsearch, before you visualize it in Power BI, you can try Hevo.

Hevo is a No-code Data Pipeline. It will integrate data from multiple sources such as Elasticsearch and transport it to your desired destination in real-time. 

Visit our Website to Explore Hevo

Give Hevo a try and Sign Up for a 14-day free trial today.

Share your experience of connecting Elasticsearch to Power BI in the comment section below. 

Visualize your Data in Power BI Easily