Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Leann
Regular Visitor

stucking on creating connection in model

Hi,

I have a data model in power bi desktop, the tables are loaded from ssas server, and it refreshed data well yesterday.

But today when I pulled in more data (400,000 rows to 17,986,280 rows) to ssas server, and click refresh data of the fact table (refresh only one table), it's stucking in "Creating connection in model" 1 hour...(still stucking)

Also, I've exmained the table which has no blank columns. is it normal? 

or any experience to check the problem efficiently?

 

Thanks a lot!

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@Leann,

Do  you choose “Import” or “Connect Live” mode to connect to SSAS database in Power BI Desktop? I would recommend uncheck the following options in Power BI Desktop and then refresh your data model.
1.JPG

If the above error still occurs, please try to re-connect to the SSAS database in Power BI Desktop, pull in columns and rows you need to build report.


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.

View solution in original post

51 REPLIES 51
JE-WeVite
Regular Visitor

What worked for me is:

  1. Manually refresh tables in the "Data" pane until I found which table was not able to create a connection and was stuck
    • Right click, Refresh Data on each
  2. Delete that table from the model
  3. Add that table back by directly connecting to it (again) via "New Source" (and in my case "Dataflows")
  4. Refresh that table manually as a test (worked!)
  5. Refresh all tables at once (worked!)

 

NOTE: In my case, this was a small table (57 rows) from a Dataflows connection to the workspace, and I was able to view the data in PowerQuery preview.

 

What DID NOT work:

  1. Changing settings in the File or Global options related to Data Load
    • Evaluations, memory
    • Set parallel loading of tables to one, and unchecking import/update/autodetect for relationships
    • Turning off time intelligence and background data loading
  2. Clearing the cache
  3. Deleting all relationships to the bad table
  4. Deleting the table and adding it back in by copy/paste of PowerQuery code in Advanced Editor into a blank query did not fix the problem
  5. Downgrading to a previous version
marlark
Frequent Visitor

As of 1/30/24 I started experiencing this issue with none of the proposed solutions being successful. Are there any new updates on this root cause?

I've been driving myself crazy for the last 3 days trying to figure this issue out. The only message I have gotten pertained to my model not being able to refresh due to memory, so I tried unselecting the boxes at data load, I've removed like 60% of the columns across all my tables, and still can't refresh, data load, or publish anything. I can't believe there hasn't been a post or solution from this week regarding this issue!

I identified my issue. There was an Append with two JDE tables and an Excel based file. Someone saved the file with the max number of Columns an Excel Sheet can handle. This caused a cascade effect where the root cause of off my radar because the owner of the file thought they built adequate restrictions on the file. *looks at the new hire*

Since a couple of weeks, I can't open some report because of memory issue ("Out Of Memory").

They probably have reduced their capacity. 😐

 

Take a look at my reply to mwhalen. It may be basic unexpected scenario like mine. *again, looks at the new hire*

 

If you have not looked yet, PowerBI desktop has a memory cache. Your issue may be tied to that if you have not checked already. When I thought that was my issue I had >2GB of cache. As noted in the other reply, it didn't solve my issue when I cleared it.

Not sure what my issue was. It seems to be fine now, either Microsoft fixed something, or it really required me letting a refresh in the Desktop app run for like 8 hours, then hours for publishing. I don't think it has anything to do with Sharepoint or number of columns in Excel, since everything in my semantic model is coming from SQL.

Exactly the same here. I am thinking a global change has happened with Sharepoint.

vijayk416_0-1706906623877.png

 

Vipuls1011
New Member

I was loading data from flat files when i got this error. It somehow created multiple columns in my data sheet and due to which I was stuck at creating connection in model. I just deleted those unnecessory columns (though it showed blank in my original excel file), and problem is resolved. 

DiegoAuvert
New Member

Trying to load a recurrent csv input, and this time is not working. Stuck in Load " Creating connection in the model." I normally have to modify the query i.e, if any header changes or new columns to incorporate w/o problems in the past. This time, new columns were modified in the power query to make the load pass the evaluation but stuck in the creating connection

benstrom
New Member

Hi,

 

just want to add another possible cause for this issue as it has happened to me a couple of times now. If you import data to PBI from an excel sheet make sure to delete any empty columns on the right of the table you're loading (just mark the columns and press delete). It seems like PBI is trying to create connections to all the empty columns as well and then it takes forever to refresh.

 

//Benny

dmaddern
Helper I
Helper I

Hello,

I had the same issue with a new table i added to an existing data set. I removed the new table, then readded it and it worked for me.

Maybe that will help for you

FSmyth
Regular Visitor

Hi I believe it crashes as it's trying to pull endless columns so you need to save the information you have as a table so it just read and puts the data other with the upgrade it's been defaulted to pull endless columns

Taz_Car
Frequent Visitor

Hi, does anyone have any other suggestions.  I am connecting to excel files in sharepoint and am getting blocked at the "Creating connections in model" stage.  I have tried everythgin above, but no joy.

orangeatom
Resolver I
Resolver I

hi @bharath47 in August 2022, this issue is back, advise on solutions. Latest PowerBI client is downloaded as of August 4th, 2022.

September 2022 still doesn't fix the issue for me, still stuck on creating connection in model for bi that import data from web 😞

@orangeatom - Workaround could be to manually refresh the table one by one in Power BI Desktop "InsteadOf" RefreshAll. That helped me progress.

bharath47
Microsoft Employee
Microsoft Employee

Hi Team, started having the same issue since this week's Aug 2022 update. Any resolution? 

Same here! Don't know what to do. 😞

@inesnovo7 try manual refresh of individual tables once and then try RefreshAll. Not incurring this issue after this step.  

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.