Forum Discussion
Parameterized data source (Azure Databricks) connection with the powerBI.
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
Thank You Anonymous 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]
)
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