Forum Discussion
Parameter Setting for Data Source(Snowflake)
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_IMPORTEDAfter:
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_IMPORTEDNo 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.
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?
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.
