Dynamic Parameter Setup to Denodo in Power BI

Hi Vasanthrk,

Thank you for reaching out to the Microsoft fabric community forum. Also thanks Akash_Varuna, for his inputs on this thread. I have identified few workarounds that may help resolve the issue.

Based on your description, it appears you are developing a reusable Power BI template that connects to Denodo via ODBC, and you aim to make the connection dynamic by using parameters for the server, database, etc. Although this approach works well for file-based sources, you are encountering difficulties when trying to implement the same technique with Denodo over ODBC.

Note: Power BI does not support directly parameterizing ODBC connection strings through the standard UI in a dynamic way. As a result, you're unable to switch servers or databases dynamically when connecting to Denodo using parameters especially within reusable .pbit templates.

Create Parameters: Go to Home > Manage Parameters > New Parameter, and create: ServerName (e.g., denodo-dev.company.com). Port (e.g., 9999). DatabaseName (e.g., my_db)

Create a Dynamic Connection String: In Power Query Editor, add a Blank Query and paste the following code:

let

    ServerName = ServerNameParameter,

    Port = PortParameter,

    DatabaseName = DatabaseNameParameter,



    ConnectionString =

        "Driver={DenodoODBCDriver};" &

        "Server=" & ServerName & ";" &

        "Port=" & Number.ToText(Port) & ";" &

        "Database=" & DatabaseName & ";" &

        "UID=your_username;" &

        "PWD=your_password;",



    Source = Odbc.DataSource(ConnectionString, [HierarchicalNavigation=true])

in

    Source

Replace your_username and your_password as needed. For production purposes, it is advisable to store credentials securely using Power BI Gateway or Windows Credential Manager instead of embedding them.

Navigate to Your Denodo Tables: After connecting, expand the source to locate your tables. Apply any necessary transformations.

Save as a Template: Go to File > Export > Power BI Template (.pbit). Upon opening, users will be prompted to enter the parameters, such as those for different environments like Dev, Test, or Prod.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.