Need help in changing my Azure Data lake connection string dynamically with API call

[Anonymous]

Hi All,

I have a requirement to change the connection string to the Azure DataLake Gen 2 dynamically based on the tenant id.

My data source is Azure Data Lake Gen2.

We are maintaining a storage in the Data Lake where in each of the tenant's data will be maintained in a separate file  and the name of the file is the tenant id itself. Now the folder structure in the connection string  is as  "https://xxxxxxxxx.core.windows.net/abcdefg/xxxxxxxxxData/" & TenantID & "/Gxxxxxxxxx/Latest"

For mock up we have created a normal parameter in Power BI Desktop with the name TenantID. So now if I need to generate a report to any specific tenant, I will go edit the parameter TenantID manually , click OK and refresh the report, SO now the &TenantID  in the above connection string gets changed by picking up the TenantID given in the parameter and generates report with that particular tenant's data only, since the &TenantID is  representing the filename in the DataLake.

NOTE: Here in the report we are no where importing all the Tenant's data from DataLake to Power BI. The &TenantID  in the above connection string is picking up the TenantID from the parameter given manually, changing the connection string accordinly, and getting me data only from that file in data lake.

Now we need to embed this report into an Azure application. In this case, based on the user logged in, the related Tenant ID has to be identified, and that Tenant ID has to be automatically written in the $TenantID of the connection string. Followed by that, report has to be refreshed and displayed to the user with only that Tenant Data.

In this regard, can anyone help me out achieve this requirement?

1. How to automatically pass the logged in user's Tenant Id to $TenantID in the connection string? Do we need to develop any API call which can construct the connection strings dynamically and then should I use that API call as a web connection in my Power BI desktop file?

2. Can the above API call be built programatically? If so, how and any code references please.

3. Or else, is there any other alternative approach?

4. Also, for the embedded report, should I configure the Data Lake source to Power BI Gateway for scheduled refreshes? If so, how? (this question is for last priority, but my immediate requirement is for above questions only)

Thanks

Lakshmi Koduri

[Anonymous]

Hello @Anonymous , did you received any solution for this? I also have a similar kind of requirement where I need to change the connection string dynamically for different AzureStorage Connector names.

[amitchandak]

@Anonymous , see if this can help

https://radacad.com/change-the-source-of-power-bi-datasets-dynamically-using-power-query-parameters

[Anonymous]

Hi Amit,

The reference which u have provided is not suitable to my requirement, because it is showing me a procedure to set the parameter in Power BI desktop in Power Query and later on provide the tenant id in the parameters section of Power bi service and refresh the report. My requirement is completely different here. No manual provision of parameter should exist. Based on the user login info in Azure, it has to capture the tenant id and provide it to the tenant id parameter.

Thanks

Lakshmi Koduri 

[amitchandak]

@ImkeF , can you help on this.

[ImkeF]

Hi @Anonymous ,

unfortunately I'm not aware of any API that returns tenant IDs.
The Graph API could return the userprincipalname of the currently logged in user (you need a custom connector for it: https://www.thebiccountant.com/2019/04/17/graph-api-custom-connector-power-bi/)
Not sure if you have any "translation" table that could lookup the tenant ID on that basis?