Forum Discussion
Passing Parameter to warehouse
Hi,
For managing dashboards, we have created a dev workspace and a prod workspace. I created all datasets in warehouse: dataflow (source data) -> Warehouse (stored Procedure) -> Pipeline (to run the procedures daily). Then I copied those in Prod workspace keeping the same naming convention. The idea is to set 'env' parameter which will pass dev warehouse ID in Dev and Prod warehouse ID for prod. In PBIX of my final dataset, I created 3 parameters:
Env (Dev and Prod), WarehouseIdDev (for Dev env Id), WarehouseIdProd (for Prod env Id).
Then I updated the 'Applied Steps' of my final dataset as follows:
let
wsId = if Env = "Prod" then WarehouseIdProd else WarehouseIdDev,
Source = Sql.Database(wsId, "WarehouseName"),
dbo_dataset = Source{[Schema="dbo",Item="DatasetName"]}[Data]
in
dbo_dataset
The default value of Env is set to be Dev. The plan is to set the parameter as Dev in dev workspace and set it up as Prod in prod workspace.
I saved the updates in the PBIX and published in to the dev workspace. Now I am getting following error for the refresh schedule that I had set for this semantic model:
This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.
Hi AG_PBI ,
Thank you for your questions. I’d like to clarify a few points regarding your setup:
- The variable name you choose (e.g., wsId or ConnectionString) does not impact functionality as long as it is used consistently throughout your queries. The key is to ensure that the value assigned accurately reflects the connection string for your environment (Dev or Prod). You appear to be using the server information from the semantic model correctly, but please ensure that the connection strings in your parameters match the server details for each environment.
- The Current Value setting in Power BI Desktop specifies which value the parameter will use during development. Once published to the Power BI service, you can override this Current Value with a different setting for the same parameter. This flexibility allows you to maintain a single PBIX file while managing different environments (Dev and Prod) within the service.
- Setting the Env parameter directly in the Power BI service will take precedence over the value set in the PBIX file during report refreshes. This is particularly useful for managing deployments without needing to republish the PBIX file.
- If removing the if statement resolved your refresh errors, it indicates that the parameter setup in the service was not compatible with the logic in your query. By directly using IDs for Dev and Prod, you have simplified the logic, which can streamline your workflow.
- The parameters defined in the PBIX file and the settings in the Power BI service can interact, but the service settings will override the parameters in the PBIX. For example, if you publish a PBIX with a Current Value set to DevId but later change the Env parameter in the service to ProdId, the service will utilize ProdId for refreshes.
I hope this addresses your concerns. If you have any further questions or need additional assistance, please do not hesitate to ask. Kindly Accept this as a solution and give it a “Kudos” so others can find it easily.
Thank you.
6 Replies
Hello AG_PBI - The problem is the if statement in your source query. If you want to parameterize the data source depending on the stage (Dev/Prod), you could skip the evaluation and just use a parameter for the connection string. Alternatively, you could create a new semantic model from within the lakehouse and then use deployment pipelines to deploy the warehouse and additional semantic model from Dev to QA and Prod. The warehouse and model stay connected without needing parameters at all. If this method will not work for you can you please provide some additional information explaining why and I'll be glad to help more.
Hi jennratten, Thanks for your reply. We are using different workspaces cause we are assigning different space even for the transitions instead of just the semantic models. Can you please elaborate how to use a parameter for the connection string?
Hi AG_PBI
Thanks for posting your query in community forum.
To pass the environment parameter dynamically and also refresh your dataset in the Power BI service, here is how to do it:
1. Open your PBIX file and go to transform data. Create three parameters: Env, ConnectionStringDev and ConnectionStringProd.
2. In the Power Query Editor, use the parameters in your query:let
ConnectionString = if Env = "Prod" then ConnectionStringProd else ConnectionStringDev,
Source = Sql.Database(ConnectionString, "WarehouseName"),
dbo_dataset = Source{[Schema="dbo",Item="DatasetName"]}[Data]
in
dbo_dataset
3. Save and publish your PBIX file to the Power BI service. In Power BI service, navigate to the settings of your dataset. Change the Env variable to Dev for the Dev workspace and Prod for the Prod workspace. Schedule a refresh for your data source. You have to correctly update the data source credentials.
If this helps then please Accept it as a solution and dropping a "Kudos" so other members can find it more easily.
Thanks.Hi v-ssriganesh, Thanks for these details. But I am not sure how is this different than what I entered in the steps. Just that I used variable name as 'wsId' and your code suggested it to be 'ConnectionString'. From semantic model's Settings -> Gateway and cloud connections I get following and I assume the "server" becomes the connectionstring (which I entered as wsId). Is that a correct way to identify the dev and prod connection strings?
Also, when I setup 'Env' parameter as a list with Dev and Prod options, it mandates to select 'Current Value'. If Current value is being setup in the PBIX itself, does setting it up again in Power BI service make any difference? I entered dev and prod IDs directly in Env parameter and removed the 'if' statement from the Power Query (with if statement, the refresh schedule was erroring out). Then I selected the current value as DevId in PBIX and published it in Dev workspace. Then I setup the current value as ProdId in the same PBIX and published it in prod workspace. That worked with refresh schedule setup. But I am still confused how the parameter setup from BI service model setup interacts with 'current value' within the PBIX.
Hi AG_PBI ,
Thank you for your questions. I’d like to clarify a few points regarding your setup:
- The variable name you choose (e.g., wsId or ConnectionString) does not impact functionality as long as it is used consistently throughout your queries. The key is to ensure that the value assigned accurately reflects the connection string for your environment (Dev or Prod). You appear to be using the server information from the semantic model correctly, but please ensure that the connection strings in your parameters match the server details for each environment.
- The Current Value setting in Power BI Desktop specifies which value the parameter will use during development. Once published to the Power BI service, you can override this Current Value with a different setting for the same parameter. This flexibility allows you to maintain a single PBIX file while managing different environments (Dev and Prod) within the service.
- Setting the Env parameter directly in the Power BI service will take precedence over the value set in the PBIX file during report refreshes. This is particularly useful for managing deployments without needing to republish the PBIX file.
- If removing the if statement resolved your refresh errors, it indicates that the parameter setup in the service was not compatible with the logic in your query. By directly using IDs for Dev and Prod, you have simplified the logic, which can streamline your workflow.
- The parameters defined in the PBIX file and the settings in the Power BI service can interact, but the service settings will override the parameters in the PBIX. For example, if you publish a PBIX with a Current Value set to DevId but later change the Env parameter in the service to ProdId, the service will utilize ProdId for refreshes.
I hope this addresses your concerns. If you have any further questions or need additional assistance, please do not hesitate to ask. Kindly Accept this as a solution and give it a “Kudos” so others can find it easily.
Thank you.
