Can't perform scheduled refresh when connecting Spark database directly to Power BI Report Server

[pbinewbie101]

Hey team, my customer wants to connect their Spark database directly to Power BI Report Server with scheduled refresh. But it looks like PBI doesn't support it. Is there any good suggestions for this situation? I have suggested the customer to first connect their data to SQL server/MySQL and then connect to PBI. But the customer doesn't want to do it this way. Also, the customer wants to keep their data on-prem, so I can't leverage Spark connector (Databricks).

Many thanks to your answers.

[d_gosbell]

Power BI Report Server only supports scheduled refresh against a subset of the data connectors and Spark is not one of these (see https://docs.microsoft.com/en-us/power-bi/report-server/data-sources). Your only option at the moment would be what you've already suggested and that is to "tunnel" through one of the supported connectors. 

I don't have access to a local Spark instance so I can't test this, but PBIRS supports ODBC for scheduled refresh. Have you tried using the ODBC driver? You would need to install the driver on both the PBIRS server and on the workstation building the PBIX report.

[pbinewbie101]

Hi @d_gosbell ,

Thank you so much for your quick response. Really appreciate it.

Please bear me with another follow up questions - do you know will the two options (1) use supported connector; 2) use ODBC ) have a huge impact of response time of data? I am asking because the customer has hundreds of millions of data going through everyday. 

[d_gosbell · Accepted solution]

---

@pbinewbie101 wrote:

...do you know will the two options (1) use supported connector; 2) use ODBC ) have a huge impact of response time of data? I am asking because the customer has hundreds of millions of data going through everyday. 

---

No, sorry I have no idea. You'd have to run some tests yourself and see. But that scale might be beyond what is reasonable for PBIRS. If you have to stay on premise I'd suggest looking at using an Analysis Service instance in tabular mode. Then you have the option of creating paritions so you don't need to pull through all the data every time you refresh. You can then create a Power BI Report in live connect mode point at SSAS.