What are the best ways of getting data using SQL

[akhaliq7]

I have become experienced in using Power Query in getting data with fact and dim tables. I wanted to know using SQL what are the different methods I can bring in data. I know of one way using native queries. Is there any other ways I have heard the words data warehouse, sql views, stored procedures can someone shed some light into what method is the best to use thanks.

[BA_Pete · Accepted solution]

Hi @akhaliq7 ,

"Best" is a relative term. What is best at any given time will change depending on the task at hand.

In terms of preparing and delivering SQL DB data for use in Power BI, here are some of the most common methods:

1) Native Queries - writing your own SQL script within the PBI connection string to present to the SQL server.

Pros:

- Can write complex and specific requests to the DB without having to have access to the DB itself

- Can use query folding but requires a specific argument to be added ([EnableFolding=true])

Cons:

- Will not fold unless specific argument added to connection string ([EnableFolding=true])

- May be messy/confusing for other users who inherit your queries/code

2) Import Connections - Connecting to the source from within Power Query and importing required data.

Pros:

- Can be folded with minimal interference to transformation requirements

- Simple and easy to follow query steps

- Intuitive queries/code for inheritors

- End-user report experience fast and responsive

Cons:

- Requires all data needed for reporting to be imported and stored in the data model i.e. slower refresh, larger file size

3) Direct Query Connections - Connecting to the source in PQ but importing no data. Visuals create ad hoc SQL queries to the source.

Pros:

- Can provide near-real-time data updates in reporting

- Bypasses need to import and store very large datasets i.e. faster refresh, smaller file size

Cons:

- Limited PQ and DAX functions supported

- End-user report experience may feel slow and unresponsive

4) Dataflows - Pre-preparing and storing PQ queries in the MS cloud to be used for PBI reporting later.

Pros:

- Prepares and stores queries ahead of time

- High-traffic windows can be avoided

- M code does not need to be replicated across many PBIX files

Cons:

- Moves further away from real-time data (as Dataflow needs to be refreshed, then the report)

The other items you mentioned i.e. Data Warehouse, Views, Stored Procedures, are not connection methods and are not related to Power BI specifically. Here's how I would briefly describe each:

Data Warehouse: An on-prem or cloud database where data is stored in a near-final, processed, state, ready for reporting on.

SQL Views: SQL queries written on the SQL DB that transform data in the native tables into more 'useable' formats.

Stored Procedures: SQL queries written on the SQL DB that can perform more mechanical functions, such as INSERT, UPDATE etc., and can be executed by triggers or on schedules.

Pete