none
The Data Model connection could not be created RRS feed

  • Question

  • Hello, 

    Using powerquery, I am trying to access daily logs conveniently, the are over 24m records in a month. 

    I have selected the columns i need, and checked the box ' load data to Model', Once the query has ran it returns the error, "The Data Model connection could not be created".

    Thank you for any help on this :)

    Thursday, February 20, 2014 4:59 PM

Answers

  • Hmm ok, that is interesting. Would you be willing/able to share one of these files? If so, please click the Send Feedback button and choose Frown. (This should be from the Power Query ribbon, NOT the feedback button for Excel that is in the very top right of the screen.) Mention my name in the feedback message and then we can sync up on a way to transfer the file (if it's too big to attach to the email.)
    Friday, February 28, 2014 7:28 PM
    Moderator

All replies

  • Are you using 32-bit or 64-bit Excel? What is the working set size when this happens?
    Thursday, February 20, 2014 11:13 PM
    Moderator
  • I am using 32bit excel, this seems to happen whenever i check the box 'load to data model'
    Friday, February 21, 2014 10:49 AM
  • This is a tough one because as an add-in, we can't get much information from the API for failures like this. Our general recommendation for people working with big data sets is to use 64-bit Excel. I realize that's a big hammer, but it might be your only option if you need to work with a 24 million row data set.

    Another option (which you may have already done) is to filter and aggregate that dataset as much as possible before adding it back into Excel. The analysis done inside the Power Query editor is less likely to negatively affect the working set on your machine. (It does depend how the query is built and where the data is come from. If we have to pull all the data into memory to do some of the operations then it won't help at all.)

    Friday, February 21, 2014 6:34 PM
    Moderator
  • Is there anything i could do alternatively? I need to build dashboards which would require views which prevents me from aggregating up the data, is there another software i could potentially use to enable me to do this?

    I believe you could do this on MS Access, Is there a way i could import files automatically into the database using a add-on or similar.

    In terms of filtering, when i work with a smaller set <1m, i still receive a similar issue. 

    Best regards

    Monday, February 24, 2014 10:21 AM
  • I do think that 64-bit Office would probably help (and if it doesn't then we could look at the memory pressure on the machine and determine if your machine has enough memory to handle a dataset that big.)

    Monday, February 24, 2014 3:01 PM
    Moderator
  • Are you able to use the import facilities in Power Pivot to import data successfully? That would tell you whether you're hitting a limit of memory/resources. I realize that Power Pivot may not necessarily support they type of data import scenario you are after - so this is a shot in the dark from my side, I realize.

    Hope this helps.

    thanks.

    Faisal Mohamood | Program Manager | Data Platform Group - Microsoft

    Monday, February 24, 2014 5:54 PM
  • Just tried to have 64 bit excel installed- with IT so may take a few days!

    In terms of processor power I have a reasonable machine (8GB RAM) 

    @ Faisal, The import seems to be running fine, the issue only crops up when I select 'load to data model'

    Tuesday, February 25, 2014 10:35 AM
  • Just tried the file on 64 bit excel, and the same issue crops up, is there another solution?

    Is there an alternative to this? theres to much Data for MS Access.

    Wednesday, February 26, 2014 5:06 PM
  • The last thing is to check to see if Power Pivot can load the data (don't use Power Query anywhere in the loop.) If that works then this is a Power Query issue. If not then this is just a case of too much data for your machine to handle. I don't know of any Excel technology that will help you in that case. I would probably try to load the data into a SQL database and then operate on the data from there but that's probably going to require custom code that is beyond the scope of Power BI.
    Wednesday, February 26, 2014 6:54 PM
    Moderator
  • I am able to load the files individually in power pivot. is there anything else i could try.
    Thursday, February 27, 2014 3:54 PM
  • Can you load them individually in Power Query? (ie. Only load a single file in Power Query?)
    Thursday, February 27, 2014 5:11 PM
    Moderator
  • Yes, however i can't load to data model
    Friday, February 28, 2014 2:01 PM
  • Hmm ok, that is interesting. Would you be willing/able to share one of these files? If so, please click the Send Feedback button and choose Frown. (This should be from the Power Query ribbon, NOT the feedback button for Excel that is in the very top right of the screen.) Mention my name in the feedback message and then we can sync up on a way to transfer the file (if it's too big to attach to the email.)
    Friday, February 28, 2014 7:28 PM
    Moderator
  • Hi sorry for the late response, 

    I am unable to share these files for privacy reasons, is there any other way we could approach this- could you possibly send me files which do process correctly and i could use these on my power query to pinpoint if the issue is on the file or software.

    Wednesday, March 12, 2014 11:15 AM
  • As far as I know, every CSV file works. :) But obviously that's not true. If you make a file that is something like the following, doesn't it work?

    a,b,c
    1,2,3
    4,5,6

    Wednesday, March 12, 2014 1:24 PM
    Moderator