Forum Discussion

Aruna_ln's avatar
Aruna_ln
Helper III
8 years ago
Solved

what is Maxium limits Direct query using rows size?

HI Team, My datasource is Sql server Max row size is in tables:20 Million.   its support Direct query rows 20 Millions(its table rows size 20 millions in my sql server database)   what is Maxiu...
Need Help
  • zoloturu's avatar
    zoloturu
    8 years ago

    Hi Aruna_ln,

     

    In a DirectQuery mode, your Power BI Report sends SQL query per each visual element (tile) separately.

     

    For instance, you have a table where show two fields: City and SalaryUSD. In the same time, your SQL table with cities data has 20 mln rows. If you drop to visual element (table) City and not aggregated SalaryUSD then Power BI will send a query like below

    select City, SalaryUSD
from cities

    which will return 20 mln rows and will try to show it via table visual in Power BI, but it has a limit of 1 mln rows to render.

     

    And if you are able to change SQL query and do aggregation like below

    select City, sum(SalaryUSD) as SalaryUSD
from cities

    and in case result output of this query will have less than 1 mln rows then it will be rendered on the report.

     

    There is a possibility to track which query is sending by Power BI to SQL Server using SQL Server Profiler tool. In case of Azure SQL then you can use only Extended Events.

     

zoloturu's avatar
zoloturu
Memorable Member
8 years ago

Hi Aruna_ln,

 

In a DirectQuery mode, your Power BI Report sends SQL query per each visual element (tile) separately.

 

For instance, you have a table where show two fields: City and SalaryUSD. In the same time, your SQL table with cities data has 20 mln rows. If you drop to visual element (table) City and not aggregated SalaryUSD then Power BI will send a query like below

select City, SalaryUSD
from cities

which will return 20 mln rows and will try to show it via table visual in Power BI, but it has a limit of 1 mln rows to render.

 

And if you are able to change SQL query and do aggregation like below

select City, sum(SalaryUSD) as SalaryUSD
from cities

and in case result output of this query will have less than 1 mln rows then it will be rendered on the report.

 

There is a possibility to track which query is sending by Power BI to SQL Server using SQL Server Profiler tool. In case of Azure SQL then you can use only Extended Events.

 

  • Aruna_ln's avatar
    Aruna_ln
    Helper III
    8 years ago

    Hello Zoloturu,

     In case of Azure SQL then you can use only Extended Events(last answer) what is mean?

    my datasource is Azure Sql.

      • Aruna_ln's avatar
        Aruna_ln
        Helper III
        8 years ago

        Hi ZOTOTURU,

        thanks you replay.thanks you lot.

         

        But row size same in azure also. or its change 

        sql server how much capacity same also in azure sql server its change.

        when we used aggration function its limit 1 million records only in azure also

         

        Thanks

        Aruna