Power BI Connector Speed

graeme murdoch

Hello everyone,


Has anyone  found the Power BI connector to be incredibly slow? I've used Power Bi with a lot of different connectors and this one is pretty painful so I was wondering if it was something I'm doing or wanted to reach out to see if there were any tweaks anyone could suggest? I've cut down the datasets as much as possible all the usual stuff but I'm trying to load in 200 ish rows and its taking 10/15 minutes. 


Cheers,


Graeme 

Ben Lambert

Hi Graeme,


Can you provide any more details?  The Power BI connector drives the underlying SKY API, so would be interested to know which data sets and APIs you're ultimately using to pull data into Power BI.  Obviously 10/15 mins to pull 200 rows is not good, so any details you can provide will help us troubleshoot on our end.

graeme murdoch

Hi Ben,


It appears to be the gift part that's slowing everything down. I've filtered it down to 1 campaign around 1 months worth of data during the ETL  process and chopped off some data that's not required. I've also got a connection to Google analytics and a python script that that pulls twitter data on the same report. These were both added after I posted this and works totally fine with no performance issues. I'm also noting that I'm getting signed out of the connector a lot and I need to relog in when refreshing the data. 


To be honest it's been pretty slow since I installed the connector which I put down to data volume at first. Hoping you can give me some pointers. 


Cheers,


Graeme 

graeme murdoch

Just as a quick test I've opened a new report with a new connection only loading gifts and filtering for after November. It's still taking a long time to load sitting at 89 rows after about 5/6 minutes 

Carol Grant

Hi Graeme- are you creating a report in Power BI Desktop because I have found it extremely slow with gifts. I have it filtered in the Transform part of Power BI Desktop (power query) and I go to Close and Apply and it will sit there for hours. Are you filtering the data before it loads into Power BI ?


Best,

Carol Grant

Middlebury College

graeme murdoch

Hi Carolyn,


I've loaded the gifts with no filters as a test and it still takes ages to load. It's as if it can only load maybe 10 rows or 20 rows at a time before it has to hit the API again or something. I think to be honest I'm just going to take the data and move it into our onsite database as it will be quicker.

Carol Grant

Hi Graeme-I have no filters because I can't figure out how to limit the data in the connector before loading it into Power BI. Power BI Desktop will only load 1,000 rows but I believe that is a setting in Tools-Options. Once it's loaded into Transform I try to Close and Apply my changes and it hangs for hours. I wonder if there is a syntax I could use in the Advanced Editor to improve performance.


I wish there were more tutorials about how the Power BI connector is supposed to work and how to limit the rows or have better performance. I have to wonder if anyone is using this connector in Power BI Desktop. Or is the connector meant for vendors/3rd party applications?


Hi Ben Lambert‍ - I would be glad to help troubleshoot this with someone or if you know anyone that is using the Connector successfully in Power BI Desktop with giving fields.


Thanks,

Carolyn

Dan Snyder

Carolyn Grant‍ and graeme murdoch not sure this will completely solve your problem as you will likely have to add several data sources, but I came across this Power Automate blog post from almost 4 years ago and while the looks of the products have changed, I was able to set up a streaming dataset and get a gift to come through to the dataset I created in Power BI Service. Then since you will likely need to create relationships and transform your data in Desktop you can just use that dataset as your data source in Desktop.


This way you could set an NXT gift list or constituent list to limit the results to cut down on the time it takes to refresh. You may still run into not having all the fields you want, but it might be a start for some reports?

Carol Grant

I knew I should have asked you first! Thanks Dan Snyder‍ - that exactly what I've been looking for. Trying to get a dataset out of this connector.

I'll be in touch!


-Carol

Dan Snyder

Quick update Carolyn Grant‍ and graeme murdoch‍, it appears you cannot do data transformations in Power Query on a streaming dataset and you can only add one dataset to a Desktop report so while I am sure there is a way in Power Automate to get all the information you are looking for, it might take a lot of work to get all the data you want in one dataset and then you will not be able to use the Query editor. Sorry for the misleading excitement and happy to be wrong about this is there is someone out there with more knowledge about this.