Forum Discussion

jjlek's avatar
jjlek
Frequent Visitor
7 years ago
Solved

Directquery: Large dimension tables 1 million row limit issue

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!

 

 

 

 

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

4 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      jjlek
      Frequent Visitor
      7 years ago

      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's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        hi, jjlek 

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

         

        Best Regards,

        Lin