#  Connecting OpenSearch to Microsoft Power BI Desktop

## Prerequisites
* Microsoft Power BI Desktop
* [OpenSearch](https://docs-beta.opensearch.org/opensearch/install/index/)
* [OpenSearch SQL ODBC driver](https://docs-beta.opensearch.org/search-plugins/sql/odbc/)
* [OpenSearchProject.mez](OpenSearchProject.mez) or [AmazonOpenSearchService.mez](AmazonOpenSearchService.mez)
* Optional: [sqlodbc_import.pbids](PBIDSExamples/sqlodbc_import.pbids) to help with repeated connections to the same server 

## Setup
* Copy `mez` file in the `<User>\Documents\Power BI Desktop\Custom Connectors\` folder. This will let Power BI access custom connector.
* Open Power BI Desktop.
* Change the security settings. Click on **Files** > **Options and settings** > **Options** > **Security** > Select **Allow any extension to load without validation or warning** for Data Extensions. This will allow the custom connector to load data into Power BI.

<img src="img/pbi_settings.png" width="500">

* Restart Power BI Desktop.

## Load Data

> **NOTE**: Currently only import mode is supported. Direct query support will be added soon.

* Open Power BI Desktop.

* Disable parallel loading of tables. Click on **Files** > **Options and settings** > **Options** > **CURRENT FILE** > **Data Load** > Deselect **Enable parallel loading of tables** and click **OK**.

<img src="img/pbi_disable_parallel_loading_tables.png"  width="500">

* Click on **Home** > **Get Data** > **More** > **Other**. Select **OpenSearch Project**. Click on **Connect**.

<img src="img/pbi_select_connector.png" width="500">

* You will get a warning for using a third-party service. Click on **Continue**.

<img src="img/pbi_third_party_warning.png" width="500">

* Enter host and port values and select your preferred SSL option. Click on **OK**.

<img src="img/pbi_connection_string_options.png" width="500">

* Select authentication option. Enter credentials if required and click on **Connect**.

<img src="img/pbi_auth.png" width="500">

* Select required table. Data preview will be loaded.

<img src="img/pbi_data_preview.png">

* Click on **Load**.

* Select required columns for creating graph.

<img src="img/pbi_simple_graph.png">

## Using .PBIDS Files

More info: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-sources#using-pbids-files-to-get-data

Example PBIDS file for OpenSearch: (available here: [sqlodbc_import.pbids](PBIDSExamples/sqlodbc_import.pbids))
```json
{
    "version": "0.1",
    "connections": [
        {
            "details": {
                "protocol": "sqlodbc",
                "address": {
                    "server": "localhost:9200"
                }
            },
            "mode": "Import"
        }
    ]
}
```

The only part you should change is the `server` attribute, to point to the location of your OpenSearch server.
* For AWS connections, this will be the full path of your OpenSearch instance (ex: `https://aws-opensearch-instance.us-west-1.com`).
* Otherwise, this will be the `host:port` combination for your instance (ex: `localhost:9200`).

Save this as a `.pbids` file. Double-click on it to open up your connection in Power BI Desktop.
It will take you straight to the **Navigator** window for selecting the tables from the OpenSearch server.
* If this is the first time you are connecting to this instance, you will be prompted for your credentials.

<img src="img/pbi_auth.png" width="500">

## Troubleshooting 

* If you get an following error, please install [OpenSearch SQL ODBC Driver](https://docs-beta.opensearch.org/search-plugins/sql/odbc/).

<img src="img/pbi_error_driver_not_installed.png" width="350">

* If you get an following error,

<img src="img/pbi_error_conn.png" width="350">

1. Check if host and port values are correct.
2. Check if auth credentials are correct.
3. Check if server is running.