Getting "Accumulated string array getting too large" querying Kusto from Power BI

Question

We are trying to use Power Bi (Desktop, May 2019) to build a report on top of data in Azure Data Explorer\Kusto database and constantly receiving "Accumulated string array getting too large" errors.

Our data includes at able with about 350M rows - let's call it FACT, and a small table with 30 rows - let's call it DIM.

We defined a Many-to-one relationship between these tables in Power BI on the relevant columns. Built a simple report containing a singlMe atrix object based on the FACT table and a siTngle able object based on the DIM table for filtering the Matrix. When nothing is selected on the Table object the Matrix shows the results and everything is working quickly and nicely. However, when selecting any value from the Table object to filter the Matrix by we get an "Accumulated string array getting too large" error on the Matrix object.

Since we want to build a flexible report we cannot manually write the queries and must rely on Power BI to automatically generate them according to the users interactions.

How can we overcome this problem? It looks like Power BI is not sending optimized queries to Data Explorer but we found no way to control this.

Thanks, H.G.

Answer 1

Unless there is something that can be improved on the Power BI side to send better queries, I think you should investigate baking the calculation into a table in Kusto. This could be a good use case for update policy. As new data arrives in your FACT table, the update policy would trigger and join it with the DIM table. Flattened tables work quite well in Kusto and then your Power BI query would be much simpler since all the data is available in a single table.