How to parameterize Server & database name while using Snowflake database

[Anonymous]

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

[YashikaAgrawal]

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

[marcingalazkaMO]

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

[Syndicate_Admin]

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.

[marcingalazkaMO]

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]

[Vijay_A_Verma]

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-8... 

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