cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
quentin_vigne Senior Member
Senior Member

Too slow with DirectQuery, too big to import

Hi everyone, 

 

I'm working on PowerBi since 2 weeks now and I have been using the DirectQuery option.

One of my report is very complex (Comparison of the same results with differents criterias) and when I change criterias it takes 5-10 minutes to load the results in my card box 

 

So I tried to import my data into the software but I'm encountering some issues. 

I have 25 table from my DataBase and some of them have 5 millions rows (approximately), and everytime I try to import them it stops with differents message depending of table names

For example I have : (translated from French)

 

"Load was cancelled by an error in loading a previous table"

 

"OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E."

 

"There's not enough memory to complete this operation. Please try again later when there may be more memory available"

 

I don't know what to do to import thos data without problems ?

1 ACCEPTED SOLUTION

Accepted Solutions
quentin_vigne Senior Member
Senior Member

Re: Too slow with DirectQuery, too big to import

@v-yuezhe-msft

 

I've solved my problem

 

If someone encounter the same problem here what I did : 

 

First I've created a view of my big table, with only the needed columns.

As my datas are dated from 1994 to today, I have 2 view : one for datas before 2014 and for datas since 2014 (They don't really need very old datas in my company)

 

Then i've tried to DirectQuery but it was still very slow.

I've disabled the options of type detection / relations in the Loading Data part

 

Then I've imported it and it worked perfectly fine, it's very fast (1 or 2 seconds of loading with millions of rows)

9 REPLIES 9
quentin_vigne Senior Member
Senior Member

Re: Too slow with DirectQuery, too big to import

Okay, so I think i've just succeeded importing my data to PowerBi (I've deleted table that I was not using yet and deleted columns that were useless too)

 

But now I have another problem : there is black window popping 'sqldumper.exe' and PowerBi doesn't want to display my values 

"Can't load model schema" 

It tell me 'Check your connection to the server and try again'

Moderator v-yuezhe-msft
Moderator

Re: Too slow with DirectQuery, too big to import

@quentin_vigne,

When and where do you get the error message? Do you use he latest version of Power BI Desktop(2.57.5068.721)?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
quentin_vigne Senior Member
Senior Member

Re: Too slow with DirectQuery, too big to import

@v-yuezhe-msft

 

I get that message when I try to load my data into my card boxes / graphs 

 

I'm trying something else at the moment (I've created a view with all my tables joined and useful columns, i've created my filter while creating my view and not on PowerBi)

 

I'm using 2.57.5068.721 64-bit (april 2018)

Moderator v-yuezhe-msft
Moderator

Re: Too slow with DirectQuery, too big to import

@quentin_vigne,

Please go to File -> Options and settings -> Options -> Diagnostics and click "enable tracing" in Power BI Desktop, then load data into your graph and check the detailed logs in the traces folder. Also please help to post the detailed logs here.



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
quentin_vigne Senior Member
Senior Member

Re: Too slow with DirectQuery, too big to import

@v-yuezhe-msft

 

I've solved my problem

 

If someone encounter the same problem here what I did : 

 

First I've created a view of my big table, with only the needed columns.

As my datas are dated from 1994 to today, I have 2 view : one for datas before 2014 and for datas since 2014 (They don't really need very old datas in my company)

 

Then i've tried to DirectQuery but it was still very slow.

I've disabled the options of type detection / relations in the Loading Data part

 

Then I've imported it and it worked perfectly fine, it's very fast (1 or 2 seconds of loading with millions of rows)

Jancarlos_Ascen Regular Visitor
Regular Visitor

Re: Too slow with DirectQuery, too big to import

Friend, good morning and tell me when publishing your data to the service of power bi, do not have problems with the time of publication? or what is the time it takes

quentin_vigne Senior Member
Senior Member

Re: Too slow with DirectQuery, too big to import

@Jancarlos_Ascen

 

It takes between 5 and 10 minutes to publish to Power BI service (my file weight 300 - 400Mo) and I keep them updated with the Power BI gateway. When scheduled the reports takes 10-20 minutes to refresh at the same time (I have 4 reports)

 

- Quentin

Jancarlos_Ascen Regular Visitor
Regular Visitor

Re: Too slow with DirectQuery, too big to import

I have a 200 MB bi power file (BD SQL connection - IMPORT) and one hour and media in power bi services that are somewhat 2 million records What recommendations would you give me for this publication process to be streamlined? The database that I connect is remote and is within the network. and another consultation with the power BI gateway is the update faster?
Highlighted
quentin_vigne Senior Member
Senior Member

Re: Too slow with DirectQuery, too big to import

@Jancarlos_Ascen

 

Maybe your network connection isn't very good ? 

 

I have 3 tables that contains 3 millions + rows and others one that contains 300k rows and I don't have problems

 

- Quentin