Forum Discussion
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 Maxium limits Direct query using rows size?
any body help on this?
Thnaks
Aruna
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.
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-walkthgrough/ .
Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
8 Replies
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.
Hello Zoloturu,
In case of Azure SQL then you can use only Extended Events(last answer) what is mean?
my datasource is Azure Sql.
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-walkthgrough/ .
Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
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.
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
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.
