Forum Discussion
Post PatronPower Query Preview (Auto-Sorting?!?!)
In the initial connection screen robarivas you can write your own SQL statement. I activily recommend against this in production as it will break 100% of all future query folding. But in this scenario, it can assist in trouble shooting.
Does it do this with any table on that server, or just this one? if it just this one, it might be an insanely massive table and the driver isn't coping with it well. If that is the case, consider working with your DBA to privide a view to you to use vs the table. Then connect to the view. All transformation later that Power Query can convert to SQL statements will fold.
If it is all tables though, then there is definitely a problem with the server configuration or your driver (ensure you have the latest Oracle driver for Power BI.)
Community ChampionI believe what Power Query is doing in the data preview window is trying to return meaningful results. I know when you are doing transformations in SQL Server data it will do some ordering and get the top 4,096 rows as well. This is a sample of what Power Query does when accessing the AdventureWorks database in SQL and returning the results to the Power Query window while I am editing queries:
select top 4096
[$Ordered].[CustomerKey],
[$Ordered].[GeographyKey],
[$Ordered].[CustomerAlternateKey],
[$Ordered].[Title],
[$Ordered].[FirstName]
from
(
select [_].[CustomerKey],
[_].[GeographyKey],
[_].[CustomerAlternateKey],
[_].[Title],
[_].[FirstName]
from [dbo].[DimCustomer] as [_]
where (([_].[GeographyKey] <> 7 or [_].[GeographyKey] is null) and ([_].[GeographyKey] <> 9 or [_].[GeographyKey] is null)) and ([_].[GeographyKey] <> 10 or [_].[GeographyKey] is null)
) as [$Ordered]
order by [$Ordered].[CustomerKey]
That is to attempt to provide you the most meaningful data when doing transformations. When the actual referesh is happening though, the code is much smaller and does no automatic ordering.
select [_].[CustomerKey],
[_].[GeographyKey],
[_].[CustomerAlternateKey],
[_].[Title],
[_].[FirstName]
from [dbo].[DimCustomer] as [_]
where (([_].[GeographyKey] <> 7 or [_].[GeographyKey] is null) and ([_].[GeographyKey] <> 9 or [_].[GeographyKey] is null)) and ([_].[GeographyKey] <> 10 or [_].[GeographyKey] is null)
I do not think there is a way to turn that off, and performance is certianly better than if it were to return all rows (1M, 100M?) in an unordered way during the Power Query editing process.
Post PatronThanks edhans. That's interesting. As a test I tried connecting to a SQL Server table instead and when we looked at the back-end SQL code it (extremely strangely) did not show any kind of ordering/sorting (and thus the preview generated instantly).
Even if the auto-sorting (despite no sign of it in the M code) before any transformations has some value in certain cases it would only have value/benefit if it can do it quickly. In my case it seems to hurting performance not helping it. Because if such auto-sorting takes an indefinitely long time and times-out (as it appears to in my Oracle case) then that would seem to render large tables useless as one cannot ever see an initial dataset to begin working with. Loading/Refreshing comes later. Let's worry about building the Power Query first.
What am I missing or not understanding. And why is my experience so tangibly different with Oracle?
- edhans
As a temporary measure, pre-filter the table to be a really small set of data so it isn't sorting a bazillion rows before returning it. You can later remove that filter. And again, the M code has no sorting. But for the preview window, Power Query does its own stuff to optimize that. Thus the ordering and the Top XXXX rows filters. That is why my code example above is much larger for the preview window vs the final query sent during refreshes.
I've not heard of this issue before, so I am wondering if it is a specific configuration on your oracle server or its performance. Sorting is not a big task so not sure why it is taking your server so long. Lots of people use Oracle with Power Query and Power BI and I've never seen performance complaints about it like this.
- robarivas
Hello edhans I agree with you that it "feels" like it has more to do with the database itself and/or the database driver. Not sure where that leaves me though as far as an action I can take.
However, when you select a table in the Navigator window where is there an option to apply any filter? Without that then doesn't it still have to generate a preview before you can apply a filter step? Problem is I can't get to that initial preview because it'll time-out/fail eventually.
- edhans
In the initial connection screen robarivas you can write your own SQL statement. I activily recommend against this in production as it will break 100% of all future query folding. But in this scenario, it can assist in trouble shooting.
Does it do this with any table on that server, or just this one? if it just this one, it might be an insanely massive table and the driver isn't coping with it well. If that is the case, consider working with your DBA to privide a view to you to use vs the table. Then connect to the view. All transformation later that Power Query can convert to SQL statements will fold.
If it is all tables though, then there is definitely a problem with the server configuration or your driver (ensure you have the latest Oracle driver for Power BI.)