Power Query Preview (Auto-Sorting?!?!)

[robarivas]

I'm connecting (thru Power Query in Power BI) to a very large table in Oracle. The preview is not generating (fails and/or times out). Turns out its because Power Query (as part of the Navigation step apparently) is asking Oracle to sort the table first. Sorting or "Order By" is a heavy operation. Instead of increasing the timeout value and waiting x minutes just the to see the table is there some way to prevent Power Query from automatically asking Oracle to sort the data? And why does this seem to be an issue only with Oracle? I have not run into this with other large tables in different sources.

[NickMac]

Hi all,

We are also seeing the same behaviour with the Denodo Connector with Power BI. We have a Denodo Virtualisation over a SQL Server. When I connect to a large table via Denodo it wants to sort the preview / sample data with the primary key. When I go direct to the SQLServer I notice this is not the behaviour. So one solution for us was to remove the Primary Key meta data from Denodo and bang!! it works. So its Power BI behaviour with certain data sources... 

Have been looking for a solution everywhere ... nothing.

Let me know if someone has a solution to modify this behaviour in Power BI... crazy, several billion rows in a table in Direct Query mode and it wants to sort by the primary key before giving me the first 200 rows ????

Nick

[edhans]

I 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. 

[robarivas]

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

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.)

[robarivas]

Hey @edhans When I supply a written SQL statement the problem goes away. The preview is instant (more or less). I'm not sure I actually have access to any other tables but I'll see if I can find out and I'll also look into the possibility of getting a view set up. The table is roughly about 250 million rows and around 120 columns.

[edhans]

Great @robarivas - glad you are at least able to see some data. Can you mark one of these as the solution so this thread can be marked as solved - at least for this minor mystery?

Be sure to get a view, or add a filter in your SQL statement. Otherwise, when this loads, Power Query will read all 250,000,000 records before doing transformations as it will not be able to fold any other steps since you hardcoded the connection query.

[robarivas]

Thanks @edhans I'll mark one as a solution as a favor to you for helping out and because I think this issue (if its really on my side) can't be addressed by the Community. But in actuality I feel this is very unresolved. Until I can be shown (like by a DBA on my side perhaps) that the database and/or its driver have some kind of configuration problem, I won't see this issue as resoved. Nowhere in any Power Query documentation, articles, books, etc. have I seen a requirement or recommendation that views and/or hand-written SQL be used. I'd say I've probably only ever seen the opposite recommendation.

[edhans]

I fully understand and can appreciate your issue @robarivas - and you are right. Avoid native SQL where possible. Put it in a View on the server, or use Power Query for all transformations.

But again, I've been on these boards for a few years and while I have obviously not seen every post, I've not seen this issue before, and we know that there are people using Power BI and Power Query with tables much larger than what you have and are not getting these time out issues. So it seems to me to be resolved in that, barring other people coming in and going "Yeah, same with me and my Oracle database" this isn't a bug in Power Query, nor a design flaw, but something with your database or this specific table.

[Jimmy801]

Hello @robarivas 

I never worked with Oracle, only SQL and I don't know if they behave differently. But I never saw Power Query sorting something automatically. Did you put the sorting in your code somehow? Or why do you know that Power Query is sorting the data?

BR

Jimmy

[robarivas]

Thanks @Jimmy801

I did not do any sorting in Power Query. Here is the M code in my query:

let
Source = Oracle.Database("Oracle", [HierarchicalNavigation=true]),
SCHEMAX = Source{[Schema="SCHEMAX"]}[Data],
TABLENAME = SCHEMAX{[Name="TABLENAME"]}[Data]
in
TABLENAME

I know its sorting because the Oracle DBA sent me the SQL being run in the database when the M code above executes. Here is the SQL code:

SELECT "FIELD1",
"FIELD2"
FROM ( SELECT "$Ordered"."FIELD1",
"$Ordered"."FIELD2",
ROW_NUMBER () OVER (ORDER BY "$Ordered"."FIELD1")
AS "$Row"
FROM "SCHEMAX"."TABLENAME" "$Ordered"
ORDER BY "$Ordered"."FIELD1") "$Paged"
WHERE "$Paged"."$Row" <= 4096
ORDER BY "$Paged"."$Row