# Power BI

## Requirements

Before you begin the process of connecting e6data with Power BI, make sure you have the following pre-requisites in place:

* Establish a [Gateway connection](https://docs.e6data.com/product-documentation/connectivity/endpoints) in your cluster.
  * If it’s a serverless cluster, just enable gateway connectivity toggle button under connection details of cluster.
* Power Bi desktop . [Link to download](https://powerbi.microsoft.com/en-us/downloads/)
* Download the Starbrust ODBC driver and install the driver. [link](https://starburstdata-downloads-v2.s3.us-east-2.amazonaws.com/odbc/2.1.7.1009/StarburstODBC64bit-2.1.7.1009.msi)
* Acquire the necessary credentials:

  * User Name: Used for logging into e6data.
  * Password: [Token generated](https://docs.e6data.com/product-documentation/user-settings/personal-access-tokens-pat) by the user.
  * Host Name: The host IP of the cluster for the connection.
  * Database (optional): Name of the database in the given catalog.
  * Catalog (optional): Name of the catalog.

### Steps for connecting to e6data

Follow these simple steps to connect e6data with Power BI:

* **Step 1: Start Power BI desktop**
  * Open and Start Power BI desktop we we already have installed on our laptop
* **Step 2: Go to Get Data Section**
  * Click on get data and search for starburst connector and click on connect

New Report → Home → Get data → starburst connector

<figure><img src="https://3484040590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FeVBYKZm1xFKFFVzS0lRJ%2Fuploads%2Fl2evVcK4AbeUBMA8RD9g%2FScreenshot%202024-11-29%20at%209.50.07%E2%80%AFPM.png?alt=media&#x26;token=a2aecaff-7cc3-4825-a141-ef2a658a68dd" alt=""><figcaption><p>Power BI Desktop</p></figcaption></figure>

* **Step 3: Establish the Connection**

  * Provide the below details to make the connection with e6data
    1. **Host:** The host IP of the cluster for the connection.
    2. **Port:** 443
    3. **Catalog (optional):** Name of the catalog
    4. **import/DirectQuery:** Select among import/DirectQuery based on requirements
       * While using custom SQL query please ensure to give path to table as`db.schemaname.tablename`<br>
  *

  ```
  <figure><img src="https://3484040590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FeVBYKZm1xFKFFVzS0lRJ%2Fuploads%2FeTr1RFu3SmiPgIEXtQXp%2FScreenshot%20(13)-20240605-090528.png?alt=media&#x26;token=2414bc0b-6d33-4a07-bf7a-7e412b814f53" alt=""><figcaption><p>Power BI Desktop</p></figcaption></figure>
  ```

  ```
  * Click on OK and it will redirect to new tab
    * Choose LDAP
    * User Name: Used for logging into e6data.
    * Password: Token generated by the user.
    * click on connect
  ```

<figure><img src="https://3484040590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FeVBYKZm1xFKFFVzS0lRJ%2Fuploads%2F8IHD8gob0rikyNMTP4I5%2FScreenshot%20(14)-20240605-090641.png?alt=media&#x26;token=8f5f7590-3865-4dda-baa1-47360ed4ba5f" alt=""><figcaption></figcaption></figure>

### Step 4 : Adding Databases

* Select the desired catalog and tables for creating visualisation and click load

<figure><img src="https://3484040590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FeVBYKZm1xFKFFVzS0lRJ%2Fuploads%2Fg1u9JjsTnDonY5fYzXI6%2FScreenshot%20(15)-20240605-091104.png?alt=media&#x26;token=4a77d647-330d-4c44-8b1e-9d438c2b6f81" alt=""><figcaption><p>Power BI Desktop</p></figcaption></figure>

<figure><img src="https://3484040590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FeVBYKZm1xFKFFVzS0lRJ%2Fuploads%2F48vFDqEB2fIekijYdDLA%2FScreenshot%20(16)-20240605-091108.png?alt=media&#x26;token=7786d8c6-5ab0-4cb1-b4fd-ab49a2102e45" alt=""><figcaption><p>Power BI Desktop</p></figcaption></figure>

### Step 5 : Utilize Power BI Features

* With the successful connection, you can now create charts and run SQL queries using PowerBI.

By following these steps, you'll establish a connection between e6data and Power BI, enabling you to seamlessly integrate and visualize your data.

**Note**:

If you face below error stating:

`SSL Error: SSL certificate problem: unable to get local issuer certificate`

We need to download and install 5 certificates to tackle this issue by following the below steps:

* Navigate to the [Amazon Trust Repo](https://www.amazontrust.com/repository/) page.
* Under the Root CA’s will be the required 5 certificates.

<figure><img src="https://3484040590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FeVBYKZm1xFKFFVzS0lRJ%2Fuploads%2FA5Rkel1FL44fgOnXkwmV%2Fimage-20241128-152355.png?alt=media&#x26;token=92daf713-c353-4cd1-9742-a07189d3aef2" alt=""><figcaption><p>Root CAs</p></figcaption></figure>

* In that, under the Self-Signed Certificate column, choose the DER option for all 5 certificates to download.
* Once downloaded, open the certificate. A dialogue box will appear.
* Select `Install Certificate -> Current User -> Automatically select the certificate store -> Finish.`
* You should get a installation successful popup.
* To validate the installation, navigate to `Start -> Manage Computer certificates`
* On the left-side panel, `Trusted Root Certification Authorities -> Certificates`
* You must see Amazon Root CA 1, Amazon Root CA 2, etc…
* Restart PowerBI and re-establish the connection.

{% hint style="info" %}

**Limitations:**

* Drag and Drop of date and datetime data type is not supported.
* As of now we don’t support Joining multiple tables in data model of power BI. However we can use custom sql query for joining.
* We may not be supporting all the functions in measures and calculated columns.
  {% endhint %}