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.
With a SQL data source, when I add a filter to a query on a date or date/time column with a literal value, it works as expected.
When I instead use a date or date/time parameter as the value for the filter on my date or date/time column, it ALWAYS retrieves top 4096 rows, then, hits the database again to actually perform the query.
I tried the same thing with a Decimal (int) type filter/parameter combination and there were no issues. It immediately queried the correct number of rows (not first 4906) as in the intial "literal" case...
I did not try any other parameter types.
I traced the queries against my SQL server.
This SQL:BatchCompleted happens:
select top 4096 [$Ordered].[Id], [$Ordered].[CenterId], [$Ordered].[BusinessDate], [$Ordered].[IDCash], [$Ordered].[CashDrawerName], [$Ordered].[OpenTime], [$Ordered].[OpenUser], [$Ordered].[CloseTime], [$Ordered].[CloseUser], [$Ordered].[ReportAmount], [$Ordered].[Shift_Id] from ( select [Id], [CenterId], [BusinessDate], [IDCash], [CashDrawerName], [OpenTime], [OpenUser], [CloseTime], [CloseUser], [ReportAmount], [Shift_Id] from [dbo].[CashTurns] as [$Table] ) as [$Ordered] order by [$Ordered].[Id]
Then a few events later, I see my actual query, SQL:BatchCompleted:
execute sp_executesql N'select [_].[Id] as [Id],
[_].[CenterId] as [CenterId],
[_].[BusinessDate] as [BusinessDate],
[_].[IDCash] as [IDCash],
[_].[CashDrawerName] as [Drawer Name],
[_].[OpenTime] as [Open Time],
[_].[OpenUser] as [Open User],
[_].[CloseTime] as [Close Time],
[_].[CloseUser] as [Close User],
[_].[ReportAmount] as [Amount],
[_].[Shift_Id] as [Shift_Id]
from
(
select [Id],
[CenterId],
[BusinessDate],
[IDCash],
[CashDrawerName],
[OpenTime],
[OpenUser],
[CloseTime],
[CloseUser],
[ReportAmount],
[Shift_Id]
from [dbo].[CashTurns] as [$Table]
) as [_]
where [_].[BusinessDate] = convert(datetime2, ''2018-06-04 00:00:00'') and [_].[CenterId] = 2'
While it does eventually work, Power BI could potentially, unnecessarily, download 33,013,760 bytes (~33MB = 8,060 max bytes * 4096 rows) for every table in my pbix report. I happen to have about 8 tables, so I'm looking at potentially downloading ~264MB of data from my data store that is almost instantly discarded...
Obviously, my 8 tables don't have rows of maximum size, but ANY extraneous data downloaded/imported seems quite unnecessary and incorrect behavior.
Please advise,
Thank you,
~Bill
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.