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.)
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?
Community ChampionAs 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.)
- 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.