Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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!
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-w... .
Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
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
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 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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
20 | |
19 | |
18 | |
9 |