Parameter Setting for Data Source(Snowflake)

[SruthiK]

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

[Manendra · Accepted solution]

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

[fjellfisk]

This haven't been solved! you're not able to use parameters as us can use then on for instance SQL connection. You have to dive into the advanced editor. this is a workaround. You should be able to set parameters same way as in SQL connection dialogbox

[BhargavYeluri]

I tried the same steps and got this error. Can you please explain what's wrong here. Any Insight is greatly appreciated. 

Thanks,

Bhargav 

[BhargavYeluri]

I have figured this out and my issue is resolved. The below code worked for me. I was using a importing from a table instead of a view as posted by the OP and that's where the confusion was. 

let
Source = Snowflake.Databases(ServerName, DWH, [CreateNavigationProperties=null, ConnectionTimeout=null, CommandTimeout=null])
,Database = Source{[Name=Database,Kind="Database"]}[Data]
,MAIN_Schema = Database{[Name="MAIN",Kind="Schema"]}[Data]
,YOUR_OBJECT_NAME = MAIN_Schema{[Name="OBJECTNAME",Kind="Table"]}[Data]
in
YOUR_OBJECT_NAME

[marcingalazkaMO]

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.

[Rubanenko]

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'

[SruthiK]

Hi Manendra, thank you for the solution. It helped 🙂

[Anonymous]

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

[sarava0483]

any update on this above request (Parametrise Snowflake source)

[anandkadirvel]

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?

[amitchandak]

@SruthiK , Please check always allow it check

[marcingalazkaMO]

Hi,

do you happen to know if that allow to change parameteres in deployment pipelines in Power BI Service?

[anandkadirvel]

@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]

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.

[SruthiK]

Hi Amit, I already checked it in Power BI Desktop. Even after that it's not working for snowflake connection.