Direct Query or Import data

[MP_123]

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!

[Anonymous]

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

[Robbisweden]

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

[wmorris20]

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]

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]

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

[Anonymous]

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

Answer : In import mode you will find report, data, model tab where as in DQ mode you will not find Data TAB in PBIX file.

[Anonymous]

You will not find this message in case of Import mode.

[v-micsh-msft]

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

[MP_123]

@Anonymous@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

[v-micsh-msft]

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

[Anonymous]

@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