Connecting to SSRS dataset using API is very slow

[itchyeyeballs]

Hi All,

Has anyone been able to set up a connection to an SSRS dataset that works well? 

I have used the following

let
    Source = OData.Feed("https://(6aaa863e-xxxx)/data", null,[MoreColumns=true]),
    #"Expanded More Columns" = Table.ExpandRecordColumn(Source, "More Columns", {"col1")
in
    #"Expanded More Columns"

It works but its really really slow, seems to hang for ages before data finally downloads (only 250k rows from a table)

Any ideas how to fix? (SSRS reports run fine) Is there a better way to connect to SSRS data?

Direct connection to SQL Server DB not possilble for our end users so I'm trying to set up a staging area in SSRS that can be used for Power BI and Excel. Thought the above was the solution but not feasiible if I cant speed it up

[v-yulgu-msft]

Hi @itchyeyeballs,

Per my research, connecting to SSRS dataset via Odata feed is the best choice I can find till now. As a workaround, maybe you could export the dataset to other file format, like Excel, CSV, then, connect to those local files in desktop.

Besides, here is an article talking about how to improve the performance using Odata connector.

OData Performance Improvements In The June 2018 Power BI Desktop Release

Best regards,

Yuliana Gu

[itchyeyeballs]

Thank you,

Exporting the datasets is not an option unfortunatly, I'm looking for a way to enable users to get access to live datsets without giving them access to the SQLserver itself. 

Using Odata via a dataset seems like the perfect solution but its crazy slow, feels like its hanging whilst trying to connect

This page - https://docs.microsoft.com/en-us/power-bi/report-server/access-dataset-odata seems to indicate im doing it the right way so I'm stuck