Forum Discussion
Passing Parameter to warehouse
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.
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.
Community SupportHi 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.
Thank you for the clarifications v-ssriganesh.