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

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