Forum Discussion

quentin_vigne's avatar
quentin_vigne
Solution Sage
8 years ago
Solved

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 ?

  • quentin_vigne's avatar
    quentin_vigne
    8 years ago

    Anonymous

     

    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

  • quentin_vigne's avatar
    quentin_vigne
    Solution Sage
    8 years ago

    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'

      • quentin_vigne's avatar
        quentin_vigne
        Solution Sage
        8 years ago

        Anonymous

         

        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)