cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft MP_123
Microsoft

Direct Query or Import data

hi all!

 

question i alwayes wanted to ask - 

when you want to create a new report - what questions are you asking yourself in order to choose the direct query option or import data?

 

thanks!

9 REPLIES 9
mridul Member
Member

Re: Direct Query or Import data

@MP_123 :  Size of the Data Set

                      Frequency of the data refresh

                      Complexity of the DAX queries involved in your reports (becuase few DAX calculation is not supported in direct query)                       Creation of 2 ways relationship in table is not supported in direct query 

                      Which database you are using , Example (When i used HIVE tables in HDInsight Cluster in Azure for West Europe ,                               direct query was not supported, i raised the issue with the Microsoft Team )

                      Q & A section is not enable for all the Database , please check that for you.

 

I think these are the Imp points you should consider. Also please have a look into Power BI Documentation for more clarity. Thanks

 

Microsoft v-micsh-msft
Microsoft

Re: Direct Query or Import data

Hi MP_123,

 

This should be an open discussion. : )

Well as Direct Query are still being prepared for new data source, the limitations of it might be overcome in the future.

What I would consider mostly should be the data source side.

First check the dataset size, if too large then consider use Direct Query, after that check if this type of data source is supported by Direct Query, currently.

When settles down, check the other parts which might have influence on reports and dashboards performance, such as refresh requirements, DAX expression and so on.

The article below shares the limitation of Direct Query, see:

Use DirectQuery in Power BI Desktop

Regards

Microsoft MP_123
Microsoft

Re: Direct Query or Import data

@mridul@v-micsh-msft

thanks 🙂

i know the limitations of Direct Query. But, let's say i'm ok with these limitations (dax function, etc.)

what is a big data set?

i mean, big data set can also affect the query time on live, so in big data sets, import may take some time, but in live mode user experience wil affect. right?

 

thanks again

Microsoft v-micsh-msft
Microsoft

Re: Direct Query or Import data

Hi MP_123,

For me a big dataset should be those size are too large for Power BI desktop to handle, and would casue the system in bad performance.

Yes, I agree with that. And Direct Query in Power BI are somehow not very smart to optimize the Query from the data source, it will translate the query language in a "simple" way. Well I suspect the product team are already deal with that part. Hope this part could be better in future.

Regards

 

Robbisweden Frequent Visitor
Frequent Visitor

Re: Direct Query or Import data

Hi,

I'm evaluating Power Bi and are trying to understand how to do the best setup and I ran in to serveral problems that i dont really understand.

"For best performance use the import instead of direct query."

 

Direct query
Direct query will will translate the query language in a VERY VERY "simple/bad" way.

 

Import

The import/publish have a size limit of 1 024mb for a desktop file but the PRO account gives me 10 GB????

 

We have One file that has a table that contains 22 million rows and it contains 2 years of transactions, why is that to BIG for Power BI? 

 

Best Regards

 

Robert

mannu Member
Member

Re: Direct Query or Import data

Size of data, support for scheduling data refresh and feasibilty of using import mode. Import mode is my preference ant day
Mannu
wmorris20 Regular Visitor
Regular Visitor

Re: Direct Query or Import data

Has anyone furthered this researched?  If I choose the exact same attributes and measures for DirectQuery and Import, the same data is being "pulled in"; it's just done in a different way.  With DirectQuery it's just compressed and put into memory (for Desktop).  I imagine it's put into Azure for Online.  With Import it's put directly in the pbix file.  Since it appears that the compression method with DirectQuery is better, I do see some benefit there.

 

Obviously for SQL Server - we'll choose Tab or MD over connection directly for the datamart.  With SAP HANA though, this presents a major obstacle.  I can't filter on date and then aggregate by year effectively, because Power BI wants to pull in every record since I have chosen date as an attribute.  My expectation would be multiple SQL requests to populate distinct attributes and then subsequent calls to populate the other visualizations.

 

We're working with billions of records (without aggregateion), so this is something we are really trying to get a handle on.  For example, we have an SAP HANA database that has 1.2B distinct records when aggregated by date with primary key and 8M records when aggregated by year with primary key.  If I simply want to display total sales (exactly 1 value) while filtering, I have to "pull in" all 1.2B or 8M records.

 

To me the true point of DirectQuery is missing here.

wmorris20 Regular Visitor
Regular Visitor

Re: Direct Query or Import data

Based on this text I don't believe DirectQuery is behaving the way it is intended - or I'm not using it correctly.

 

"The differences between selecting Import and DirectQuery are the following:

Import – the selected tables and columns are imported into Power BI Desktop. As you create or interact with a visualization, Power BI Desktop uses the imported data. You must refresh the data, which imports the full data set again, to see any changes that occurred to the underlying data since the initial import or the most recent refresh.

DirectQuery – no data is imported or copied into Power BI Desktop. The selected tables and columns appear in the Fields list.

As you create or interact with a visualization, Power BI Desktop queries the underlying data source, which means you’re always viewing current data."

 

Antony New Member
New Member

Re: Direct Query or Import data

If I open a .pbix file, how can I recognize if it was created using Import Data or Direct Query?

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,090)