Power BI Desktop Loads Data forever

[alm5084]

I'm trying to import data into Power BI desktop from an analysis services data source on premise. We don't have a data gateway set up, so the data needs to be published with the reports. It takes FOREVER to edit queries from our cube data source - like 4 or 5 hours to add all the filters, merges, etc. Now when I try to load the data to the model it gets hung on a specific number of rows and just sits there spinning. Why isn't querying from cubes in Power BI just as quick and easy as in Power Pivot query designer? It can take a full 5 minutes just to load all the distinct options for one dimension column in Power BI when trying to add a filter. In Power Pivot, those dimensions are just so quick and easy to filter on. What gives? And how do I get the data to load into my model? It's only a couple hundred thousand rows it shouldn't be this hard....

[Phil_Seamark]

HI @alm5084

Are you using the latest version of Power BI Desktop?  I found earlier versions to be slow but now they are quite snappy.

You probably will want to get a Gateway setup.  They are pretty handy.

[alm5084]

I am using the most recent version of the desktop app. I have a 64 bit machine with 8G of RAM and the appropriate version of Power BI desktop installed. Even with the gateway setup, I'll still have to set up the dataset and report in the desktop app before deploying it to the PowerBI server.

[v-caliao-msft]

@alm5084,

If dataset is large and you've used import you may have performance issue plus longer times for data refresh. DirectQuery would take away the need of data refresh but that means there maybe bit of delay in getting data for visuals as queries would be sent to on-premise data source. For example when you apply a filter queries would be sent to on-premise data source.

In your scenario, Please try to use DirectQuery mode to get data in your Power BI report and check if this issue persists or not.

Regards,

Charlie Liao

[alm5084]

Unfortunately, the data gateway will not be deployed for probably another year at my organization and it is out of my control. But I need to be able to create reports in the meantime. It just seems that Power BI is interacting with the cube differently than Power Pivot does and I want to figure out if it is by design or I have some setting wrong on my Power BI setup.

[alm5084]

Oh! And I need to add that if I connect directly to the fact table as a SQL Server type of connection, the same exact query runs in a few minutes. At first that sounds like an issue with the cube itself, but interacting with it in Power Pivot as a cube works fine, which makes me think it's the way Power BI is interacting with it.

[Sunkari]

I don't think there will be a huge performance difference between Power BI and Power Pivot. If it working with Power Pivot in 5 mins, will work with Power BI as well. 

We will see performance issue in a case of huge data, to avoid this. Use direct query and have gateway in place, which might solve your problem