Direct Query custom column and poor performance on large dataset

[kzantat]

Hi, I am a beginner user of Power BI, and I have a couple quick questions regarding to direct query that I couldn't find the answer when browsing through the forum.

Background info:

I am importing data from SQL server under Direct query mode because there are well over 40 millions row in the tables. I need to perform some simple data manipulations, such as IF(col1 = value1 && col2 = value2,1,IF(...)), nested If statements. My client only needs the report to be updated at least once a day.

Questions:

1. I saw that people suggest using Import data will improve the performance when comparing to Direct Query mode. I understand that each visuals I created under Direct query mode will send some queries to SQL server to directly get the data needed, and in Import data mode, the whole dataset tables will be imported to MS Azure database/cloud? Then each visuals will get the data from there instead. How exactly should that be faster? My visuals created under direct query mode took like 10 minutes to load in the Power BI desktop. By estimation, how much faster will it be if I use import data mode?

2. To process the raw data in Power BI desktop, I simply created calculated columns using DAX to extract useful info. from the raw columns. However, using some of those calculated columns to create visual will result in an error, "internal error an expression services limit has been reached, please simplify the complex queries". I am wondering if making some of those calculated columns into custom columns will fix this issue. However, it seems that it is impossible to make a custom column under direct query mode. Is there a workaround for that?

Thank you so much for any help. I have been stuck here for about a week. Any help is highly appreciated!!!

[kzantat]

Hi, I am a beginner user of Power BI, and I have a couple quick questions regarding to direct query that I couldn't find the answer when browsing through the forum.

Background info:

I am importing data from SQL server under Direct query mode because there are well over 40 millions row in the tables. I need to perform some simple data manipulations, such as IF(col1 = value1 && col2 = value2,1,IF(...)), nested If statements. My client only needs the report to be updated at least once a day.

Questions:

1. I saw that people suggest using Import data will improve the performance when comparing to Direct Query mode. I understand that each visuals I created under Direct query mode will send some queries to SQL server to directly get the data needed, and in Import data mode, the whole dataset tables will be imported to MS Azure database/cloud? Then each visuals will get the data from there instead. How exactly should that be faster? My visuals created under direct query mode took like 10 minutes to load in the Power BI desktop. By estimation, how much faster will it be if I use import data mode?

2. To process the raw data in Power BI desktop, I simply created calculated columns using DAX to extract useful info. from the raw columns. However, using some of those calculated columns to create visual will result in an error, "internal error an expression services limit has been reached, please simplify the complex queries". I am wondering if making some of those calculated columns into custom columns will fix this issue. However, it seems that it is impossible to make a custom column under direct query mode. Is there a workaround for that?

Thank you so much for any help. I have been stuck here for about a week. Any help is highly appreciated!!!

[zoloturu]

Hi @kzantat,

I see a full duplicate of this thread from you here - http://community.powerbi.com/t5/Desktop/Direct-Query-custom-column-and-poor-performance-on-large-dat...

Regards,
Ruslan

[zoloturu]

Hi @kzantat,

1. You can write the same Native Query (advanced option is available when you do Get Data from a server) to a database in both modes (Direct Query or Import). Do as much transformation as you can on source inside of query. Import mode will load data to a report and then it can be rendered in seconds (but to be sure it should be tested by yourself, because it has direct proportion from report structure).

2. Direct Query has a lot of limits. See details - https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about , http://radacad.com/directquery-live-connection-or-import-data-tough-decision. So simplifying calculations can help.

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

[kzantat]

Any help is highly appreciated!