Elasticearch and Power BI

[palko.balazs (Balazs Palko)]

Hi,

I'm trying to import our data stored in Elasticsearch into a BI Tool. I tried Power BI with ODBC using this guide and this guide for Tableau.

I was able to get our data into both PowerBi and Tableau, however I'm struggling with nested documents.

All of the fields are imported into the BI tools correctly(include object like madeBy in my example) except those that are defined as nested type in the index mapping. The tools are not even listing contributions(which is our nested field).

As an alternative I've tried out this Connector as well. By the looks of it it is also using ODBC. In case of this connector, the contributions field was showing up and it's value was a JSON Array text. I was able to tetect that as JSON and then expand on the items in that array. However the licensing fee is greater in this case. Are there any limitations to nested fields when it comes to getting data via ODBC?

Our simplified index mapping looks like the following:

{
  "index_patterns": [
    "volumesource*"
  ],
  "template": {
    "aliases": {
      "volumesources": {}
    },
    "mappings": {
      "properties": {
        "id": {
          "type": "keyword"
        },
        "externalId": {
          "type": "text"
        },
        "name": {
          "type": "keyword"
        },
        "value": {
          "type": "float"
        },
        "madeBy": {
          "properties": {
            "id": {
              "type": "keyword"
            },
            "externalId": {
              "type": "keyword"
            },
            "name": {
              "type": "text",
              "analyzer": "autocomplete",
              "search_analyzer": "standard"
            }
          }
        },
        "contributions": {
          "type": "nested",
          "properties": {
            "target": {
              "properties": {
                "id": {
                  "type": "keyword"
                },
                "externalId": {
                  "type": "keyword"
                },
                "name": {
                  "type": "keyword"
                }
              }
            },
            "type": {
              "type": "keyword"
            },
            "level": {
              "type": "integer"
            }
          }
        }
      }
    }
  }
}