Skip to content

Latest commit

 

History

History
100 lines (65 loc) · 3.78 KB

power_bi_support.md

File metadata and controls

100 lines (65 loc) · 3.78 KB
Raw

Connecting OpenSearch to Microsoft Power BI Desktop

Prerequisites

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.

  • 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.

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

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

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

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

  • Select required table. Data preview will be loaded.

  • Click on Load.

  • Select required columns for creating graph.

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)

{
    "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.

Troubleshooting

  • If you get an following error,

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