Parameterized data source (Azure Databricks) connection with the powerBI.

[Dev_003]

Hello Team,
Hope every one is doing great!

I am finding a way to parameterized multiple connnections(dev, test, stage, prod) of Azure databricks with the power bi desktop, I found we can set the parameter for sql server and after that user can have the dropdown option while choosing the connection(server and database), but its not working same when we choose the Azure Databricks connection.
Can team help me resolving/ guilding on this scenario.
Also, Is there possibility to make SQL Query (Direct Query) and power query parameterized?

Thank You

[Dev_003]

Thank You @v-yiruan-msft for the explanation, in case of Azure Databricks data source connection parameter i have created but it has to be change manually in the power bi service by admin to point to different environment.

I still have doubt regarding the native query parameterization, it will be really appreciable if this doubt resolves.

Here is the scenario, i have written power query mentioned below, where i have hardcoded the Study_ID  in the where clause, but in production/other environments, it should be parameterised and the studyid comes as the query parameter from the web application where the power bi report is embedded. How this issue be resolved in secure manner.
Catalog, Host, HttpPath is parameterised one.

= Value.NativeQuery(
Databricks.Catalogs(Host, HttpPath, [Catalog=Catalog, Database=null, EnableAutomaticProxyDiscovery=null]){[Name=Catalog, Kind="Database"]}[Data],
"SELECT rs.ID, " &
"rs.Name AS siteName, " &
"sub.SubjectID, " &
"sub.SubjectIdentifier, " &
"subStatus.Status_ID, " &
"subStatus.Name AS StatusName, " &
"sub.Study_ID " &
"FROM " & Catalog & "." & Database & ".instance__subject sub " &
"JOIN " & Catalog & "." & Database & ".instance__status subStatus " &
"ON sub.Status_ID = subStatus.Status_ID " &
"JOIN " & Catalog & "." & Database & ".instance__site rs " &
"ON sub.Site_ID = rs.Site_ID " &
"WHERE sub.Disabled = FALSE " &
"AND sub.Study_ID = 111111",
null,
[EnableFolding=true]
)

[v-yiruan-msft · Accepted solution]

Hi @Dev_003 ,

You can follow the steps below to get it:

1. Create a query parameter 'P_StudyID'

2. Update the above codes as below: you can refer this thread

= Value.NativeQuery(
Databricks.Catalogs(Host, HttpPath, [Catalog=Catalog, Database=null, EnableAutomaticProxyDiscovery=null]){[Name=Catalog, Kind="Database"]}[Data],
"SELECT rs.ID, " &
"rs.Name AS siteName, " &
"sub.SubjectID, " &
"sub.SubjectIdentifier, " &
"subStatus.Status_ID, " &
"subStatus.Name AS StatusName, " &
"sub.Study_ID " &
"FROM " & Catalog & "." & Database & ".instance__subject sub " &
"JOIN " & Catalog & "." & Database & ".instance__status subStatus " &
"ON sub.Status_ID = subStatus.Status_ID " &
"JOIN " & Catalog & "." & Database & ".instance__site rs " &
"ON sub.Site_ID = rs.Site_ID " &
"WHERE sub.Disabled = FALSE " &
"AND sub.Study_ID  ="&P_StudyID&" ",
null,
[EnableFolding=true]
)

Best Regards

[v-yiruan-msft]

Hi @Dev_003 ,

You can refer the following links to get it by creating the query parameter:

Solved: Changing Data Source - Microsoft Fabric Community

Using Power BI Desktop Direct Query with Parameters 

Best Regards