Directquery: Large dimension tables 1 million row limit issue

[jjlek]

Hi there,

I've been running into an issue while using Power BI in DirectQuery mode (with Amazon Redshift). The issue is as follows: We have developed a star schema for our sales reporting and are joining a large dimension table to our sales fact table within the modelling feature of Power BI. Overall we have over 2 million rows in the dimension table.

Our problem is that if we only want to report last year's data, for which we have less than 1 million matches in the dimension table and it returns an error of having reached the limit of 1 million rows. This seems to be caused by the fact that Power BI running a query which selects all rows from this dimensions, we are actually not needed. 

The query that seems to be the problem is structured as follows:

select top 1000001 "sk_entity", "entity_name" from "star"."d_entity" group by "sk_entity", "entity_name"

Is there way to avoid Power BI from querying all rows, even though they're not needed?

Your help is highly appreciated!

[v-lili6-msft · Accepted solution]

HI, @jjlek 

1 million row limitation in direct query is the number of rows returned.

So for your case, please add some filter in the report to filter needed data.

Best Regards,

Lin

[jjlek]

Hi @v-lili6-msft , 

Thanks for your reply. Maybe I haven't made it explicitely clear in my initial post, but I'm not asking Power BI to return a million rows at all. I only want to return the few thousand rows, containing this year's sales numbers, with their relevant values from the dimension table. In other words, the report is already filtered. 

However, my dimension table in total contains over 1 million rows. Power BI seems to run a query selecting ALL values from the dimension table and their relevant join keys, before making the join to the fact table. Thereby causing a million rows limit error because of the way that the query is split up. 

The interesting thing is that when I hit "Refresh all" in Power BI desktop, the issue disappears. Only to reappear at a later stage. So maybe it's cache related?

Any further help would be appreciated.

[v-lili6-msft]

hi, @jjlek 

What do you use to filter the report? You may try to add a filter in Report level filter.

Best Regards,

Lin

[jjlek]

This issue still has not been resolved. It does however seem like the query behaviour from Power Bi is very inconsistent. When working with a star schema in direct query mode it sometimes selects the entire dim table before joining, but other times it does not do this.  Meaning that in some cases having a dimension table larger than 1 million rows results in an error and in other cases it does not. This seems to be completely random however.

How could this behaviour be avoided?