Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Reducing data size - remove columns or merge with 2nd table size

Hi,   I have data table A that seems to be too large for powerbi service to load (when I refresh it just loads and loads). It's over 10gb.    However I'm only interested in a sub set of Table A, ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous 

 

I'm not using incremental refresh. 

 

It's a recently created report. I created it in desktop with a weeks worth of data, now I've published it to service for 3 months worth of data and the file isn't loading (the circle spinning icon just goes for an hour+). I'm trying to use the manual refresh icon in Service "dataset + dataflows" tab.

 

Table A is a several .dat files on sharepoint appended in M query. 

 

I've used this advice to create Table A, then use it as an internal source before merging with Table B in a seperate query

 

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

Anonymous's avatar
Anonymous
Not applicable
6 years ago

No network issues I'm aware of. It wasn't loading Friday when i first tried, and still isn't today. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    The dataset is currently connected to 3 .dat files (in csv format). each .dat file is a month worth of data and around 5-5.5gb each.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous 

       

      So I've had a look in the detail and it is actually through up an error. 

       

      Table C is the result of merging Table A and Table B (Table A has "enable load" disabled is the PowerBI Desktop Query mode).

       

      Data source error:{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host."}}],"exceptionCulprit":1}}} Table: TABLE_C.
      Cluster URI:WABI-NORTH-EUROPE-redirect.analysis.windows.net
      Activity ID:9784f625-0fc4-4b01-ade0-530045abb358
      Request ID:b151ec94-bd95-bf75-42ce-047262dca8de
      Time:2020-05-22 18:42:26Z
      • nickyvv's avatar
        nickyvv
        Icon for Most Valuable Professional rankMost Valuable Professional
        6 years ago

        Hi Anonymous,

         

        with importing files this big you might run into query timeouts.

        An important question to me is: What is the source of your .dat files? Is it a database? Are you able to connect to that source directly, that would be much better. Depending on the source, you could even use the concept of Query Folding, which can potentially push the transformations back to the source system and give you much better performance and hence no issues with refreshing.