topic Re: Connect to Power Bi dataset from report server in Report Server

Connect to Power Bi dataset from report server

jesusrincon — Wed, 18 Jan 2023 22:27:07 GMT

Hi, this a fair recent issue I haven't been able to work around. There's a Power BI dataset I need to connect to and create reports that need to be published into a Report server. So far I can connect to it, either through the get data from Power Bi Dataset or from Azure analysis services, using the provided server link such as

powerbi://api.powerbi.com/v1.0/myorg...

It works in desktop September 2022, and it allows me to do what I need. The problem goes when I publish these into Report Server. It allows me to do so, and it even allows me to connect using a non MFA account and Basic authentication. However, I get this error when I open the report:

Non-Windows user credentials were supplied for an non-http connection to Analysis Services. The Microsoft SQL Server Analysis Services data extension requires Windows Integrated Security unless connecting to Azure Analysis Services.

That's ok, l can switch it to a Windows account and it seems to pass also the test connection side of the configuration. But if I open the report, this is what I get:

We couldn't connect to the Analysis Services server. Make sure you've entered` the connection string correctly.

The connection string is built like this:

Data Source="powerbi://api.powerbi.com/v1.0/myorg/workspacename";Initial Catalog="datasetname";Cube=Model;Access Mode=readonly

I'm not really sure where the problem is, and I feel this is something that is supported right now. The main reason for this is to allow us to publish massively reports on our report server from a data source created by analysts on the Power BI Online Service

Re: Connect to Power Bi dataset from report server

d_gosbell — Thu, 19 Jan 2023 23:17:43 GMT

jesusrincon wrote:

I'm not really sure where the problem is, and I feel this is something that is supported right now. The main reason for this is to allow us to publish massively reports on our report server from a data source created by analysts on the Power BI Online Service

No, this is not supported. This list of supported data sources for Report Server is documented here Power BI report data sources in Power BI Report Server - Power BI | Microsoft Learn and data sets in the Power BI service are not supported on Report Server.

If you want to publish reports like this where the dataset is on the service you should publish the reports to the service also.

Re: Connect to Power Bi dataset from report server

jesusrincon — Fri, 20 Jan 2023 14:22:41 GMT

So I still will need to deploy the data sources? I'm interested mostly on the relationships / measures / calculated columns. I can deploy the raw PBIX from our analysts, as I can configure the connection strings for the databases, but in case a measure or any calculation requires any changes, they will need to make changes to each of the reports that are connected to this dataset. Is there no other way around for this? I'm mostly interested on the tabular model behind the PBI file.

Re: Connect to Power Bi dataset from report server

Shahfaisal — Fri, 20 Jan 2023 15:56:58 GMT

jesusrincon wrote:
So I still will need to deploy the data sources? I'm interested mostly on the relationships / measures / calculated columns. I can deploy the raw PBIX from our analysts, as I can configure the connection strings for the databases, but in case a measure or any calculation requires any changes, they will need to make changes to each of the reports that are connected to this dataset. Is there no other way around for this? I'm mostly interested on the tabular model behind the PBI file.

The dataset you intend to source data form sholuld be in Power BI Service (not on Power BI Report Server). And once you consume the data set in Power BI desktop, you can only publish it to Power BI Service (not Report Server)

Re: Connect to Power Bi dataset from report server

d_gosbell — Sat, 21 Jan 2023 22:16:28 GMT

jesusrincon wrote:

So I still will need to deploy the data sources? I'm interested mostly on the relationships / measures / calculated columns. I can deploy the raw PBIX from our analysts, as I can configure the connection strings for the databases, but in case a measure or any calculation requires any changes, they will need to make changes to each of the reports that are connected to this dataset. Is there no other way around for this? I'm mostly interested on the tabular model behind the PBI file.

If you deploy a report to the service based on a dataset on the service then any fixes you make to measures will flow through automatically. If your report builders add their own measures to their reports they would need to fix those themselves.

The only way to achieve something similar on-prem is to install a SQL Server Analysis Services instances and have your developers deploy any shared data models to that. But that is more of a BI pro scenario as you cannot use Power BI Desktop to deploy to SSAS, you need to use something like Visual Studio or Tabular Editor.