"Creating Live Connections" taking hours - how long should I wait?

[KyleT]

This is my first time using DirectQuery and since it said it was good for working with large datasets, I went ahead and told it to connect to all 777 tables in our SQL database.  It has been six hours now and PowerBI still states "Creating Live Connections..." for all 777 tables and just shows a little 'spinning circle' next to all of them.

Is there some way for me to get a sense from Power BI as to how much time might be left, or at least an indicator that progress is being made?

In task manager I can see Power BI is using around 15 to 25% of my CPU consistently and 7 to 8GB of RAM, with very little disk usage most of the time.   I also see a "Microsoft Mashup Evaluation Container" process that I'm guessing is related to Power BI, though it isn't hitting CPU or RAM very hard. This is a new Dell Precision Windows 10 workstation with Core i7-6700k CPU, 32GB of RAM and 1TB SSD.

Is there a log file I could check somewhere or other sort of connection or status monitor for Power BI specifically?

In either case, what's the longest I should be waiting before I give up?  Is a DirectQuery to 777 tables just over the top ridiculous?  If so what should be my target max?

[Seth_C_Bauer · Accepted solution]

@KyleT What is it you are trying to do?... You should only be connecting to, or pulling in information that you will need to build reports. And if you really would need to model 700 + tables, Power BI Desktop is not the place to try to do that, there is no way it will support the load you are throwing at it.

[KyleT]

Thanks for the feedback guys.

Part of my problem is I don't know what data I don't need... if that makes sense.  I was hoping that DirectQuery would let me visualize the connections and possibilities and then remove what I could see wasn't useful.

The other part of my problem is I never really saw anyone say how many tables/relationships/etc was too many for Power BI, and couldn't see any way to determine an estimated processing time or status based on my initial query.

For now I still don't have a way to get a good understanding of whether my query is unreasonably large or has an estimated completion time of 3 minutes or 3 days, 

But at least I have a baseline idea that I need to start my "don't know what data I don't need" thought process on paper and narrow it down considerably before moving forward.

By the way DirectQuery did end up finishing... I think it took about 7 hours, or at least that's when my VPN to SQL disconnected due to inactivity.  My PC may have continued processing the data locally all night after that for all I know, but it's here in front of me this morning... very laggy to respond, but it's there! 🙂

[ankitpatira · Accepted solution]

@KyleT

As @Seth_C_Bauer mentioned you should only be using tables you need. Powerbi desktop is not the tool you use to import your whole database. I can only imagine how long it will take if you ever go past the stage of publishing your dataset and refreshing from the cloud. There is no specific max number of tables you can connect with DirectQuery but depends on your requirement. If you actually need all the tables then only use limited number of columns and try reducing the number of rows in there. I think import would actually be better if you want all the data instead DirectQuery.

[KyleT]

Well good-ish news?  Since I made this thead, all tables have moved on to "Creating connection in model..." ... but again no status update or timeframe.

About a week ago I tried pulling a local copy of this same database into Power BI (not using directquery) and it sat at "creating connection in model" for literally 5 days straight before I cancelled out of it.  Hoping DirectQuery will improve times, but I also don't want to let it sit there for days wondering.

For what it's worth, I just updated to the latest version of Power BI earlier this morning (it prompted me) before I ran DirectQuery.

Also Looking at my task manager now, "Microsoft SQL Server Analysis Services" is taking up 15-20% CPU and aboutt 3GB of RAM.  Power BI's CPU is down to under 1% and 5GB of RAM.

[Seth_C_Bauer · Accepted solution]

@KyleT What is it you are trying to do?... You should only be connecting to, or pulling in information that you will need to build reports. And if you really would need to model 700 + tables, Power BI Desktop is not the place to try to do that, there is no way it will support the load you are throwing at it.