Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to dynamically point a Power BI Embedded Report to different Azure Analysis Services

Hi all,

 

I'm currently developing an Embedd Power BI solution. The solution is an App Owns Data situation and our data is stored in Azure Analysis Service, so we will be doing Live Connect. In our system each customer has their data in a seperate Analysis Service which means each embedded report has to point back to the correct data source. Having data in seperate databases means Row Level Security (RLS) isn't the correct solution for us.

 

The Power BI structure I expect to have is a single App Workspace with a single Report that all customers use. What I want to know is:

 

  1. Do I need to create a Dataset per customer and assign that Dataset when accessing the Report?
  2. Or is it possible to have a generic Dataset which can be given a connection string during runtime so that the report connects back to the correct Analysis Server?
  3. Or going even further, is it possible to have no Dataset, and instead create an in-memory Dataset (which can be assigned the correct datasource) which can be assigned dynamically.

 

I'm a C# developer - currently I'm working on developing a client that interacts with the Power BI REST API and this particular problem is stopping me from making progress. My preferable solution is option 3, though option 2 is always more than acceptable. I only want to do option 1 if there is no better way to solve my problem. Of course if there is other options available I'm not aware of I'd love to hear them.

 

Thanks

5 REPLIES 5
auser
New Member

Hello @Anonymous

 

did you find a solution for this? Or an alternative way of doing it?

 

I'm currently in the same situation as you.

 

Thanks

Anonymous
Not applicable

Hi,

 

We abandoned our adoption of Power BI for other reasons than the above. But as far as I can remember doing a report/dataset per customer seemed like it was going to be the only option available to me to achieve what I wanted. Though I'm not sure how well this would have scaled. I don't know if there has been any improvements in the last half year that allows for a better approach.

 

If you do find a nicer approach I'd appreciate if you let me know what you went with since at some point  I'd like to revisit moving our old reporting system over to Power BI.

 

Best of luck!

 

v-ljerr-msft
Employee
Employee

Hi @Anonymous,

 

Could you try the solution below to see it it works in your scenario? Smiley Happy

 

1. Use Query Parameter in Data Source dialogs(server and/or database) to create a single dataset, so that you can change the data source by just modifying the corresponding Parameter.

 

2. Use the the  Set parameters operation(Power BI REST API) to dynamically change the Parameters in your code, which will also change the actual connection (server and/or database).

 

3. Use the  Patch data source operation to update the credentials for the changed data source. Reference: https://community.powerbi.com/t5/Developer/Patching-an-Online-DirectQuery-datasource/m-p/351230

 

Regards

Anonymous
Not applicable

Also on further investigation, looking into Set Paramters and Patch Data Source, neither of these functions are available to me. It appears Set Parameters only works against direct query datasets and my Analysis Services are in In-Memory mode. Also Patch Data Source is an On-Premises Gateway feature and since I'm working entirely in Azure I won't need a gateway - or at least that is my current understanding of gateways.

 

I'm actually starting to get a little worried that Power BI can't deliver the level of functionality I expected of it. Even my least prefferred approach, having a dataset per customer, doesn't seem to be easily achievable. The only way that I can see to point a Report to another DataSet is to rebind it which it is just switching the embedded dataset to another dataset - there is nothing being redireted during runtime.

 

In the scenario where I have multiple customers using the Report at the same time I need to be able pass through their query to the correct Analysis Service - changing the hardwired dataset for the report will just cause conflicts between customer such that only one of them will ultimately be able to access their data.

 

I seem to be in a position where I might have to create a Report per customer such that each Report has it's own embedded dataset. But it seems crazy to me that Embedded Power BI doesn't allow a standard report that can connect back to different datasources depending on the consumer of the report.

 

Anonymous
Not applicable

Thanks for the response @v-ljerr-msft. This approach looks like the style of approach I was looking for but I can't even get query parameters working. My existing reports which access Azure Analysis Services via Connect Live don't have an option for changing the data source - the option is just greyed out. So it's not possible to get to the Data Source dialogs.

 

To get around this I create a new report, and if I pick an Analysis Service as the data source and specify a parameter for the server, when attempting to connect I get either a silent fail (i.e. after pressing ok on the dialog the loading spinner spins forever) or I get the following error:

 

The connect live option for this file is disabled because it already contains data from another data source. You cannot explore live data and connect to another type of data source in the same file.

 

It appears to me that this error occurs because when you specify query parameters for a data source then the Current Value that you have to specify is stored as a data source. Connect Live does not seem like having an embedded data source and this result in the error I provided. Or at least that is my interpretation of what is happening.

 

After getting the above error, if I try to connect again the Connect Live is greyed out. If I use the Import option, just to see if I could get something working, I get told the parameter must be a host name or a http url. My paramater value starts with asazure://xxxx so it not a host name or http url.

 

Considering the problems I'm getting. Do you know if query parameters for the data source and Connect Live actually are know to work together? And if so, I would be interested in figuring out what it is I'm doing wrong.

 

It's somewhat frustrating that there isn't config files where these configuration options can be edited as I'd be a lot more comfortable editing XML or json instead of clicking lots of buttons.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors