Parameter Setting for Data Source(Snowflake)

Hi, I added parameter (Snowflake sever instance, data warehouse) and configured in Advanced Editor. When I am trying to add new source i..e, Snowflake in Power BI I am unable to see the option to choose parameter. The same process is working for SQL Server Database and able to choose Parameters

Need Help

Manendra
3 years ago
SruthiK

I follow the below-mentioned method to stitch between different DBs environments i.e. DEV, Test, Prod.

Before creating the report, I import all the tables which are necessary by default approach i.e
Get Data > Snowflake > Enter the URL > Warehouse Name.

After importing all necessary tables. I create 3 parameters
1. Server Name (ServerName)
2. Data warehouse (DWH)
3. Database Name (DB)

Select Advance Editor for any table and replace the code as mentioned below

Before:

let
Source = Snowflake.Databases("XXXXXXXXXXXX.snowflakecomputing.com","extralarge"),
XX_XX_DEV_Database = Source{[Name="XX_XX_DEV",Kind="Database"]}[Data],
Your_Schema = NAME_OF_Database{[Name="XX",Kind="Schema"]}[Data],
TABLE_NAME_YOU_IMPORTED = Your_Schema{[Name="TABLE_NAME_YOU_IMPORTED",Kind="View"]}[Data]
in
TABLE_NAME_YOU_IMPORTED

After:

let
Source = Snowflake.Databases(ServerName, DWH, [CreateNavigationProperties=null, ConnectionTimeout=null, CommandTimeout=null]),
Database = Source{[Name=DB,Kind="Database"]}[Data], // XX_XX_DEV_Database changed to Database, XX_XX_DEV changed to DB
Your_Schema = Database{[Name="XX",Kind="Schema"]}[Data], // Just use the word Database don't change it
TABLE_NAME_YOU_IMPORTED = Your_Schema{[Name="TABLE_NAME_YOU_IMPORTED",Kind="View"]}[Data]
in
TABLE_NAME_YOU_IMPORTED

No need to make any changes in line number 4; I just gave you for reference.

You need to do the above things in each table for the first time only. When ever you want to change the database or server name just go to parameters which you created and switch them.

Let me know if this is helpful.

16 Replies

Manendra
Advocate I
3 years ago
SruthiK

I follow the below-mentioned method to stitch between different DBs environments i.e. DEV, Test, Prod.

Before creating the report, I import all the tables which are necessary by default approach i.e
Get Data > Snowflake > Enter the URL > Warehouse Name.

After importing all necessary tables. I create 3 parameters
1. Server Name (ServerName)
2. Data warehouse (DWH)
3. Database Name (DB)

Select Advance Editor for any table and replace the code as mentioned below

Before:

let
Source = Snowflake.Databases("XXXXXXXXXXXX.snowflakecomputing.com","extralarge"),
XX_XX_DEV_Database = Source{[Name="XX_XX_DEV",Kind="Database"]}[Data],
Your_Schema = NAME_OF_Database{[Name="XX",Kind="Schema"]}[Data],
TABLE_NAME_YOU_IMPORTED = Your_Schema{[Name="TABLE_NAME_YOU_IMPORTED",Kind="View"]}[Data]
in
TABLE_NAME_YOU_IMPORTED

After:

let
Source = Snowflake.Databases(ServerName, DWH, [CreateNavigationProperties=null, ConnectionTimeout=null, CommandTimeout=null]),
Database = Source{[Name=DB,Kind="Database"]}[Data], // XX_XX_DEV_Database changed to Database, XX_XX_DEV changed to DB
Your_Schema = Database{[Name="XX",Kind="Schema"]}[Data], // Just use the word Database don't change it
TABLE_NAME_YOU_IMPORTED = Your_Schema{[Name="TABLE_NAME_YOU_IMPORTED",Kind="View"]}[Data]
in
TABLE_NAME_YOU_IMPORTED

No need to make any changes in line number 4; I just gave you for reference.

You need to do the above things in each table for the first time only. When ever you want to change the database or server name just go to parameters which you created and switch them.

Let me know if this is helpful.
- SruthiK
  New Member
  3 years ago
  Hi Manendra, thank you for the solution. It helped 🙂
- Rubanenko
  Regular Visitor
  3 years ago
  I have found the following solution to work for me.
  First connect to your Snowflake and get the table the shows all the Databases like below
  Then Filter your database (This filter will actually will be your parameter later)
  Then DELETE all other columns except for 'Data' Column and press the table
  Then you will get the Schema table repeat the same steps as above for the schema and table and ensure to delete all fields and keeping only the 'Data' field
  
  Then what is left is to create 3 parameters
  Database = DBE
  Schema = SCM
  Table = TBL
  
  Go to the 'Advance Editor'
- BhargavYeluri
  Regular Visitor
  3 years ago
  I tried the same steps and got this error. Can you please explain what's wrong here. Any Insight is greatly appreciated.
  
  Thanks,
  Bhargav
  - marcingalazkaMO
    Frequent Visitor
    3 years ago
    I have same code in Advanced editor, the only difference is in 1st row after DWH I have Role specified and you have some different things.
amitchandak
Super User
4 years ago
SruthiK , Please check always allow it check
- SruthiK
  New Member
  4 years ago
  Hi Amit, I already checked it in Power BI Desktop. Even after that it's not working for snowflake connection.
- anandkadirvel
  Frequent Visitor
  4 years ago
  amitchandak Do we know if we have any update on the Paremeter settings in Power BI for Snowflake data source?
  My dataset has around 12 tables from Snowflake data source, where I need to switch from DEV to PROD environment at one shot, but currently, we dont have any option to pass parametrized data sources like SERVER, DATABASE and SCHEMA, instead we have to change the source from DEV to PROD for each and every table in the Advanced Editor code manually. Also, the "Change Source" button is disabled in the Data source settings. Please advise.
  How can we raise this query to microsoft support team to address this issue?
  - marcingalazkaMO
    Frequent Visitor
    3 years ago
    You have to create parameter with text type and set of values i.e. ENV = DEV/TEST/PROD - there is dedicated window to create parameter in Power Query.
    After creating parameter, create new query to Snowflake data source and then go to advanced editor and replace part of code with your parameter. You may need to replace part of hardcoded values of Warehouse, Role and database schema by your parameter.
- marcingalazkaMO
  Frequent Visitor
  3 years ago
  Hi,
  
  do you happen to know if that allow to change parameteres in deployment pipelines in Power BI Service?
sarava0483
Frequent Visitor
4 years ago
any update on this above request (Parametrise Snowflake source)
- anandkadirvel
  Frequent Visitor
  4 years ago
  Do we have any update on the Paremeter settings in Power BI for Snowflake data source?
  My dataset has around 12 tables from Snowflake data source, where I need to switch from DEV to PROD environment at one shot, but currently, we dont have any option to pass parametrized data sources like SERVER, DATABASE and SCHEMA, instead we have to change the source from DEV to PROD for each and every table in the Advanced Editor code manually.
  How can we raise this query to microsoft support guys to address this issue?
Anonymous
Not applicable
3 years ago
If I got your question right then please create the parameters and then replace the respective strings in the M laguage(advanced editor)

Sample:

let
Source = Value.NativeQuery(Snowflake.Databases(p_SF_Server,p_SF_warehouse,[Role=p_SF_role]){[Name=p_SF_database]}[Data], "<your Snowflake Query here>", null, [EnableFolding=true])
in
Source