Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How to parameterize Server & database name while using Snowflake database

Hi,

I have created Power BI Report with the data connected to Snowflake database. Incase if i want to parametrize the Server name and database name using query parameters how do i do that?  While using SQL Server, Get data-> SQL Server, we get option New parameter, where we define the Servername and database name parameters. But while connecting to Snowflake from Get data option I am unable to parameterize. 

Under Files& Options, Power Query, I have checked the Allow parameter option.

Kindly need help in understanding how Query parameters can be used for setting Server /DB name when conencted to Snowflake database.

Appreciate if anyone can guide on this!

 

Regards,

SGP

 

 

General Comment
Need Help
Show and Tell
Tips and Tricks

5 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    You will have to use Parameters to create paramters for server and db name and use that. Below I have created 2 parameters Server and DB and passing to Snowflake.

    If you have never created parameters in PQ - https://support.microsoft.com/en-us/office/create-a-parameter-query-power-query-5eb365bc-3982-4ab2-8830-b205a69e0f33 

    let
    Source = Snowflake.Databases(Server,DB"),
    SNOWFLAKE_SAMPLE_DATA_Database = Source{[Name="SNOWFLAKE_SAMPLE_DATA",Kind="Database"]}[Data],
    TPCDS_SF10TCL_Schema = SNOWFLAKE_SAMPLE_DATA_Database{[Name="TPCDS_SF10TCL",Kind="Schema"]}[Data],
    WEB_PAGE_Table = TPCDS_SF10TCL_Schema{[Name="WEB_PAGE",Kind="Table"]}[Data]
in
    WEB_PAGE_Table

     

  • marcingalazkaMO's avatar
    marcingalazkaMO
    Frequent Visitor
    3 years ago

    but how to parameterize Schema? The bold part in below code? It cannot be by concatenation, becasue later we have a list...

    TPCDS_SF10TCL_Schema = SNOWFLAKE_SAMPLE_DATA_Database{[Name="TPCDS_SF10TCL",Kind="Schema"]}[Data]

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    After you have created the parameter for server and database, you can get the database normally, in this point you can't specify the parameters but after select the table and load you can go to View section and open the Advanced Editor.

    There you will see the lines to load the source, database, schema and table, so there you can update the hardcode text by your parameters.

    On above image I let highlight in yellow the parameters that I used.

     

  • YashikaAgrawal's avatar
    YashikaAgrawal
    Post Patron
    1 year ago

    Hi, How to use this while passing from DEV to TEST and to PROD, can you please let me know....

    • marcingalazkaMO's avatar
      marcingalazkaMO
      Frequent Visitor
      1 year ago

      create Parameter in Power Query with list of values, add DEV/TEST/PROD as values.
      Then connect to snowflake using embedded connector and then change code in Power Query:
      parameter name: ENV
      old code "DATA_BASE_NAME_DEV"
      new code with parameter "DATA_BASE_NAME_"&ENV