what is Maxium limits Direct query using rows size?

[Aruna_ln]

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 Maxium limits Direct query using rows size?

any body help on this?

Thnaks

Aruna

[zoloturu · Accepted solution]

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 · Accepted solution]

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]

Hello Zoloturu,

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

my datasource is Azure Sql.

[zoloturu · Accepted solution]

Hi @Aruna_ln,

SQL Profiler tool not able to connect to Azure SQL. So you can use only Extended events in this case, see a link - https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/03/13/extended-events-capture-step-by-step-w... . 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

[Aruna_ln]

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

[Aruna_ln]

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

[Seth_C_Bauer]

@Aruna_ln See here for limitations You can access all 20 million, but you can only return up to 1 million per query of aggregated data, read through the limitations to understand specifics.

[Aruna_ln]

Hi Seth,

thanks you replay,but still i am not understand.

that link they given 

• There is a 1 million row limit for returning data when using DirectQuery. This does not affect aggregations or calculations used to create the dataset returned using DirectQuery, only the rows returned. For example, you can aggregate 10 million rows with your query that runs on the data source, and accurately return the results of that aggregation to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. If more than 1 million rows would be returned from DirectQuery, Power BI returns an error.

please can explain clearly

thanks

aruna

[Seth_C_Bauer]

@Aruna_ln You can run a query against an unlimited sized dataset. The output of the query, the data returned to Power BI, must be less than 1 million rows.