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 

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI nickyvv 

     

    Direct connection to the source data isn't possible at this time unfortunately. Would reducing the .dat files from monthly to weekly files change anything?

    • nickyvv's avatar
      nickyvv
      Icon for Most Valuable Professional rankMost Valuable Professional
      6 years ago
      Hi Anonymous,

      that would certainly help. You'll have to join them back together in Power BI, but that could solve the problem of timeouts.
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        nickyvv 

         

        I currently join them together in M query using the import sharepoint folder option so it's appending all files into one big one before doing data transformations on them. I'll try splitting the data down and report back.