cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
itchyeyeballs Established Member
Established Member

Connecting to SSRS dataset using API is very slow

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

2 REPLIES 2
Community Support Team
Community Support Team

Re: Connecting to SSRS dataset using API is very slow

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
itchyeyeballs Established Member
Established Member

Re: Connecting to SSRS dataset using API is very slow

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