cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ammartino44
Helper III
Helper III

direct query vs. import

When querying a database, what's the difference between direct query and import? Thanks. 

1 ACCEPTED SOLUTION

@ammartino44This page will help. But essentially here are the basics

Import - extracts the data from your database and pulls it into the Power BI Desktop

Direct Query - leaves the data in the database and sends queries to pull the information needed.

 

Other things to note:

Import -

Gives you the full suite of transformation and data manipulation in the Desktop

There is a 1 GB limit to the Desktop if you plan on publishing to the PBI Service

Consumes and pushes the data into the Power BI Azure backend (Azure DB/Blob Storage) (In the Service)

Can only be refreshed up to 8x a day by setting up a scheduled refresh (In the Service)

 

Direct Query -

Limits your ability to manipulate data in the Desktop (removes the Data section)

Leaves the data in the SQL database

Is "live", no need to schedule a refresh (In the Service)

 

Both methods require a gateway to be installed, unless you are talking about Azure SQL.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

8 REPLIES 8

@ammartino44This page will help. But essentially here are the basics

Import - extracts the data from your database and pulls it into the Power BI Desktop

Direct Query - leaves the data in the database and sends queries to pull the information needed.

 

Other things to note:

Import -

Gives you the full suite of transformation and data manipulation in the Desktop

There is a 1 GB limit to the Desktop if you plan on publishing to the PBI Service

Consumes and pushes the data into the Power BI Azure backend (Azure DB/Blob Storage) (In the Service)

Can only be refreshed up to 8x a day by setting up a scheduled refresh (In the Service)

 

Direct Query -

Limits your ability to manipulate data in the Desktop (removes the Data section)

Leaves the data in the SQL database

Is "live", no need to schedule a refresh (In the Service)

 

Both methods require a gateway to be installed, unless you are talking about Azure SQL.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

I dont understand the points that re pointed out here, can you give me brief details on some major differences b/w import and direct query???

@Anmolgan I have a blog series describing the connections that might help.

Overview - https://powerbi.tips/2017/10/power-bi-connection-types/

Import - https://powerbi.tips/2017/11/power-bi-connections-import/

Direct Query - https://powerbi.tips/2017/12/power-bi-connections-direct-query/


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

The more I research DirectQuery (Both SQL Server and SAP HANA), the more I debate the true differences between DirectQuery and Import.  When I watch the queries coming out of Power BI Desktop, it's clear to me that it's requesting data at the lowest grain regardless of the filters you've chosen.  Essentially, there's no way to get the WHERE clause, so it's completely dependent on you choosing only dimensions that are aggregated in some way (i.e. year).  I have no ability to intially filter on date and then aggregate by year.

 

I kind of understand this from the perspective of SQL Server is not meant to be an aggregation mechanism, but then what's the point of having DirectQuery in the first place?  The only major differences are what you've already stated.  The big one being the data is stored in memory instead of in the pbix file.

@wmorris20 I can't say I've spent alot of time examining the difference in what is being passed to the database when you are comparing the two methods as you have, but the major difference between the connections is frequency and/or where the data lives.

Import pushes the data into Azure (behind Power BI), and only let's you refresh 8x per day. (48x with Premium)

Direct Query - the data stays in the data source. As a user interacts, it is live, no need to refresh.

 

From what I have analyzed with DQ against SQL, there is additional syntax that is added to SQL queries when doing direct query when I've explicitly executed SQL commands from Power BI. Import just executed the command itself. But I never took a look further in DQ at the actual messages as I interacted with the reports.

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Thanks for the response.  I have a feeling the Diagnostic Trace isn't exposing exactly what's being sent to the data source.  I have SQL Server Profiler running while interacting with a DirectQuery connection, and I see requests each time I interact.  I don't see any up front filtering though.  For example, if I filter on year with "2017", none of the SQL requests have a WHERE clause containing that.  I have Product, Group, and Year with one bar chart of Sales Dollars in my test.  If I filter on Year, you'll see three SQL requests - two for the Product and Group filters and one for Sales Dollars sum.

@wmorris20 That makes sense to me... It pulls back the different objects and allows you to do the filtering on the Report side. If it didn't do that, then it would have to understand the filter context of everything on your report, in your measures, etc. Will it generate an aggregate value if you create a measure summing up the sales of something? Or will that just query all sales?. I should probalby dig into testing this out to determine what is happening.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Anonymous
Not applicable

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.