[SOLVED] PowerBI DAX query only returns result based on 200 rows

PowerBI DAX query only returns result based on 200 rows

I am using the OpenSearch ODBC connector (ODBC driver v. 1.5.0.0) to import data into PowerBI (v. 2.128.751.0). In the "Transform data" view, I can view all 4000 rows. However, in the Table View, I can only see 200 rows. This limitation also applies to DAX queries.

The simplest query I tried is this:

EVALUATE
    ROW(
        "DistinctCountSalesOrder", CALCULATE(COUNTROWS('my-index'))
    )

It is supposed to return ca. 4000, but only returns 200 . This also affects all visuals I tried to create, as only the top 200 rows are used for everything.

It does work correctly when I choose Direct Query (though I run into different problems with that), but not when I Import the data.

After some investigation, I have now gotten closer to the issue

I debugged the requests the PowerBI OpenSearch connector sends to OpenSearch. In the "Transform data" view, the first request will be something like

{"query":"SELECT * FROM my-index LIMIT 1000"}

Once you scroll past item 1000, it sends a query of the form

{"query":"select * from my-index limit 50 offset 1000"}

i.e. it retrieves more items in batches of 50 as you scroll down. However, once you apply the query in PowerBI and the data is to be imported, a query of the following form is sent:

{"query":"SELECT * FROM my-index LIMIT 0"}

And this query only returns 200 rows. I was able to confirm this behavior by querying the OpenSearch SQL plugin directly with Postman. It thus seems to be a configuration around OpenSearch.

It could be discussed if the way the PowerBI connector retrieves the data is correct, but for now my new question would be, where one would change the number of rows returned on a query with limit = 0?

Solution

I found a workaround (or possibly the solution?) for the problem. I've already edited the question with some investigation/debugging I had done. After a bit of searching I found the relevant doc page: https://opensearch.org/docs/latest/search-plugins/sql/settings/

The relevant setting of the OpenSearch SQL plugin is plugins.query.size_limit which defaults to 200. Modifying this limit directly influences how many rows appear in PowerBI.

Since other PowerBI connectors work in a similar way (e.g. the Azure SQL connector sends a single query to fetch all the data), it can be argued that this is indeed not just a workaround but actually the solution for importing an arbitrary number of rows into PowerBI.