Direct Query...Performance Issue

[codyraptor]

Hey everyone,

I have a large dataset and I'm trying to decide between 'import' and 'direct query'.  Import takes forever to refresh this model.  It sometimes works and sometimes can timeout, so it isn't stable.  The direct query method seems like it would be a better fit, but I'm running into an issue.  The 'editor' view pulls my sql results extremely fast.  As soon as I hit 'close and apply', then Power BI 'evaluates' for multiple hours until it finally crashes.  

My model consists of 4yrs worth of data that is aggregated in sql.  I'm calculating a sum of price, sum of revenue, and count of contracts in my aggregates against many dimensions.  The sql is basic..and the editor works great.  Also, import works..just takes forever.  

Any thoughts?

[ricardocamargos]

hi @codyraptor,

Have you tried to run this query on the database ? Is it ok ?

Are you writing your SQL or just selecting the tables ?

Thanks,

Ricardo

[codyraptor]

I have ran this query against the DB and it's fine.  I can also run the same exact query in 'import' mode...and it works fine.

[Anonymous]

The default timeout on each query is 10 minutes. I have a couple of queries that need a bit longer than that.  You are able to choose a longer timeout time if required.  When you are in your Query Editor, find the Source line of the table.  Click on the Cog icon.  In the window that appears, open up the 'Advanced Options' and the first box is "Command Timeout".

I generally work with Import where possible.  It seems to give a better level of user performance and I'm able to manage server strain a little easier.  Fortunately i'm working with data that only needs to be up to date as of close of business, so i don't have that direct query need.

[codyraptor]

I have the query set to 120 minutes.  Definitely not an issue with timeout.  I'm also receiving a schema error.

[Anonymous]

My next question would be what happens between the Source statement and when the query finalises.  What operations are happening on the table after you get it from the source?

Also are there other tables that will rely on this source, for either merging or as its own source?

[codyraptor]

I am not doing any manipulation of the data after the source statement.  I'm not changing any of the headers, formats, conditional columns, etc...  I'm literally just pulling the single sql statement in and loading it.

Other tables eventually could be used from this table using the referencing function in order to create dimensions, but I am not currently doing that.  I'm just loading the 1 single table right now.

I should mention....Our warehouse is an Oracle warehouse.  We are currently on 11g I believe is what our DB guy told me.  Could any of our issues be caused from the version of Oracle we're using?   I've also noticed our 'auto refresh' feature for my other models fail almost daily due to timeout issues.

[Anonymous]

I've only had to connect Power BI to oracle once, it was awful. It worked though, but i'd rather eat my own head than try that again.

[codyraptor]

ha...nice.

[Anonymous]

Hi @codyraptor

As far as I understand,

In direct query mode you can't see editor window, you see this only in import mode.

The editor view only pull the first n number of rows it doesn't pull the entire data set after evaluating your query. When you hit close and apply now it's trying to pull the entire data set which fails to pull may be due to many number of rows and finally it's getting crashed.