Getting data from sql options (query folding, native queries or sql views)

[akhaliq7]

I have been using native queries for the past 10 months in my power bi reports. But I have came across a lot of slow refreshing queries. I only found out that using query folding in power query is a better option and so is sql views. I wanted to ask which option I should go with as the native queries I am using are very long and will take a long time to implement in Power Query. In this case would it be better to just use sql views? 

Please only answer if you have experience with this type of situation as too many people answer with it is upto you or give us more information and then others with more experience are reluctant to answer a already answered question. 

[BA_Pete · Accepted solution]

Hi @akhaliq7 ,

It generally IS up to you the choices that you make. Some things will work better for you and your scenario, there's not always an absolutely 'right' answer to things like this.
That being said, here's my tuppence-worth on each of the SQL connection methods.

1) Native queries: If your queries are already using these, then you may not need to change what you are doing completely. On the Value.NativeQuery step, you can enable query folding, like this:

Value.NativeQuery(previousStep, "select * from table etc...", null, [EnableFolding=true])

Pros:
- Can use 'with(nolock)'
- Supports folding with argument update (above).
- Provides timely (at time of refresh) data.

Cons:
- Puts a lot of work resource (creating the SQL query efficiently) out of reach of future reports i.e. they aren't easily reusable in a new report.
- Work done 'not visible' to future maintenance users as opposed to query steps.
- Can be more difficult to debug/understand for future maintenance users.

Comments:
- If you have to use native queries, consider putting them into dataflows so they are reusable (at the cost of data timeliness).

2) Power BI connection:

Pros:
- Clear, standard, and easily understood by future maintenance users.
- No amendments required to enable folding.
- All transformation steps on the source are visible as query steps.
- Provides timely (at time of refresh) data.

Cons:
- Can't use 'with(nolock)'.
- Query steps not immedately reusable.
- Can be difficult/burdensome to maintain folding over many queries and merges etc.

Comments:
- Consider putting into dataflows so M queries are reusable (at the cost of data timeliness).

3) SQL Views:

Pros:
- Can use 'with(nolock)'.
- Fully supports folding.
- Provides timely (at time of refresh) data.
- Server performs large proportion of the work before you even need to worry about folding.

Cons:
- Transformations/operations done may be considered 'invisible' to future maintenance users.
- Not easily updated by future maintenance users - not everyone speaks SQL, nor will have write permission on servers etc.
- Not easily updated in general - renaming or removing columns may break other reports that use the same view.
- There may be small performance losses due to views having to be materialised on the server before PQ can bring the data in.

Summary:
I personally use a lot of views and Power Query steps. I use views when I need to create a very specific and often complicated dataset over multiple tables, as it makes sense to make the server do the work without trying to manage query folding over a lot of queries and merges etc. I then make small adjustments to the view in PQ where it's easy to see the steps performed and maintain folding.

I also often create views that are just [select * from mytable with(nolock)], just to be able to avoid locks when connecting directly from PQ, then use folding in PQ to filter and shape the data to prevent undue server load.
For more generic/simple queries, such as dimension tables, I'll often connect directly from PQ as there's very little chance of locks being applied to these tables.
I avoid native queries almost entirely as I feel the work done in the SQL query is far too hidden and difficult to update and reuse.

Pete

[undefined0495]

Hello @BA_Pete, I'm trying to avoid native queries myself because of the exact reasons you mentioned above, namely the difficulty of debugging / supporting by future users of my workbook. In my workbook, all of my queries are quite simple, the final result from each query could be folded into a native query; however, I'm wondering if there is actually any benefit in doing so.

From what I can tell, it seems that Power BI folds my simple queries to native SQL queries in the background, so is there any difference between keeping my queries as a set of M steps vs folding all of the queries into the "Source" query?

[BA_Pete]

Hi @undefined0495 ,

Just to be clear, when we reference "Query Folding" or "Folding" we're talking specifically about the process or action of Power Query translating M code into SQL in the background, not manually writing a native query connection.

If you're able to get your query to fold using M query steps then, to my mind, this is the most beneficial implementation overall, for the reasons I discussed above. I would never, for any reason, attempt to translate a fully-folding M query into a native query connection. There's just no point.

Pete

[akhaliq7]

My main concerns are around data refresh issues I am having as I only started using Power BI last year and was not aware that if you use a native query and applied steps on top in power query that would slow down the data refresh and break the query folding, But yes will probably take the same approach as yourself by using views when the sql gets complicated and for simpler queries just use power query. Thank you for giving a detailed reply this makes sense.