Forum Discussion

Krishnakumar_C's avatar
Krishnakumar_C
New Member
2 years ago

Direct Query Mode -> Azure Databricks -> Creates empty dummy databricks connections (stops refresh)

The below is my Power Query that connects to multiple databricks clusters through slicer selections via ManagedParameters. SourceName and HttpName are variables that gets set dynamically based on th...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi  Krishnakumar_C ,

 

Based on the Power Query screenshot you provided on the desktop, it appears that you’ve used a ‘country’ parameter as part of the connection string to link to a data source. 

After publishing the dataset, you should also see this ‘country’ parameter value in the dataset settings and verify whether it corresponds to the data source you intend to connect to.

Edit parameter settings in the Power BI service - Power BI | Microsoft Learn

Regarding the credentials you’ve entered for authentication, make sure to confirm the type and format of credentials your Power BI desktop is using to connect to this data source. Consider using the same authentication type and credentials to connect to the data source.  If you’re using a gateway, ensure that the gateway is within the same local domain as your Power BI desktop.

 

Best Regards,

Liu Yang

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

  • Krishnakumar_C's avatar
    Krishnakumar_C
    New Member
    2 years ago

    Hi Liu, Thanks for replying.
    The "country" managedParameter has a default value of Canada. However, its dynamically set at run time by the users through "CountryTable" slicer setting. The "country" column in "CountryTable" has been binded to the "countryParameter" managed Parameter as show in the given screenshot.

    So if you see the below screenshot, the slicer enabled user to set "Canada" or "Australia". 
    Since the slicer is bound to the countryParameter, as per the "if" condition in the Power query in Direct Query mode, it dynamically switches to different databricks.
    ////// snippet from power query

    CountryCode = #"countryParameter",    // ManagedParameter


    SourceName =if CountryCode = "Canada" then "adb-12345.azuredatabricks.net"
    else if CountryCode = "Australia" then "adb-6789.azuredatabricks.net"
    else error "adb-12345.azuredatabricks.net",

     

    Source = Value.NativeQuery(Databricks.Catalogs(SourceName, HttpName, [Catalog="hive_metastore", Database=null, EnableAutomaticProxyDiscovery=null]){[Name="hive_metastore",Kind="Database"]}[Data], "select code, #(lf) item, #(lf) type_desc, #(lf) unit_desc, #(lf) price, #(lf)product,#(lf) #(lf) quantity from " & SchemaName & ".tableA", null, [EnableFolding=true])

    /////


    This dynamic switching of datasources in direct query mode is working fine without any problems.
    The expectation is under "Data source credentials" in the workspace, it should prompt credentials for "2" Databricks (Canada, Australia). Instead as shown in my original screenshot, its prompting credentials for an empty databricks where host and httpPath are empty.

    extensionDataSourcePath 

    {"host":"","httpPath":""}