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
mgente
Frequent Visitor

PowerBI Embed, App Owns Data, Direct Query

Hi all - I'm trying to deliver a PowerBI report to a TV/Info screen within our company. The report uses auto refreshing and is connected to on-premises Hana database where it gathers live data.

 

As there is no interaction on the TV screen possible (no way to sign in and enter credentials) I had to implement the 'app-owns-data' approach which is documented here: https://docs.microsoft.com/en-us/power-bi/developer/embedded/embed-service-principal

 

So I've done all the steps and I am running a sample report, embed to a website based on the sample code microsoft provides without any problems. However as soon as I switch to the direct query report I am getting broken visuals and I'm not able to gather data from the source systems. This is somehow expected because the azure service principal is not able to gather data from the hana source, but how am I implementing this correctly? Is there a way to setup a proxy account for this or can I impersonate the service principal at any stage? What am I missing?

 

Hope you can help, best regards, Mario

6 REPLIES 6
RakeshSinghr
Resolver I
Resolver I

Hi Mario,

 

              I'm looking into something similar at the moment and so here are few pointers if they are of any use:

 

1) When you say direct query, have you set up gateway datasource to have direct query option ticked? 

2) If you are using SSO, have you done all that is required on the gateway machine to delegate?

    https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sso-kerberos-sap-hana 

3) With Direct Query there are 2 cases

     i) do you want the report to be run using one single account - so if userA or userB or userC is opening the report the underlying datasource is hit with a predefined username/password which is set in the datasource of gateway

   ii) do you want the report to be run in the context of userA if userA has opened the report and userB if userB has opened the report and so on? if so you'd have to configure kerberos SSO

 

Assming you want the most upto date info on your TV screen, you need direct query report and if you have kerberos SSO enabled your credentials will be passed over to the datasource (App will not have access to the datasource) and the report will keep refreshing (assuming you've enabled page refresh every xx mins). Provided all the settings are configured correctly report should refresh.

 

-Rakesh

Hi @RakeshSinghr - I will try to answer your questions to my beste knowledge:

 

1) Yes Direct Query is up and running

2) We are sucessfully using SSO (with Hana and other sources), so I imagine our admins have correctly setup kerberos

3) Our gateway is configured that user is passed through to source, so we don't have to overthing row/item level security in PBI anymore. This is then probably the most interesting clue yet. Do we need an additional Gateway setup to be able to impersonate at Gateway level?

 

You assumption is correct, I setup a direct query report with auto page refresh and embedded it in an "APP owns Data" approach. Thank u, this was very helpful!

Just to eliminate access level issues - can you 

 

1) create a pbix file with just one visual - say a table. Use direct query and deploy to service ( deploy it using app - by invoking the api) so the owner is App

2) you open the report in power bi service and record the session in sap hana to see whether your username is being used to run the query when u open the report. If yes then kerberos SSO has been set up correctly.this also means that on the gateway in advanced options 'use sso for both direct query and import' is ticked and no username/password provided on the datasource within gateway clustee)

 

Now perform the same for the visual which is cracking. What errors do u get? Share the snapshot of error pls. Also enable verbose logging on gateway and look at the error log and info log.

I did, let say something similar, as I'm not having access to all ressources needed and requested on your end.

 

I setup a simple report were I'm querying for the logged on username. I specifically created the report with a Database user I own and I retrieve this user from the database on my local machine. I then published it to the PBI Service and run the report off my browser and see that It is know taking my user account for connection via SSO to the source system. I think we have no troubles over all with kerberos as our service is up and running for a lot of users for about half a year. 

 

As expected embedding and running the report with the service principal (app registration) breaks the visual - retrieving this error:

mgente_0-1601018140926.png

 

So Service Principal / registered APP is not able to retrieve (or logon) on on-premises Systems. So I think we need a new Gateway connection with preconfigured database access.

 

v-rzhou-msft
Community Support
Community Support

Hi @mgente 

Did you set the gateway ,authorization and credentials in the right way?

Try to test whether you can see the visual in Service.

For more info to Direct Query and Gateway: Direct Query Gateway

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

Hey @v-rzhou-msft - Report is working fine in PBI Service, for all users we shared the report with. As soon as we bring embeding and the azure app registration in place, visuals brake. It seems like that the report is not able to gather any data when running in the AzureAPP context (which is somehow expected) however - how do we impersonate at the gateway level?

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
Top Kudoed Authors