How to change data source setting for deactivated account

[pallavi_r]

Hi Team,

I have a prebuilt dashboard connected to Snowflakes account. It has multiple visuals and dashboard. But since I dont have access to snowflakes account, it does not get loaded. I need to change the data source to azure sql db where same model is present. But change data source setting does not work. It prompts to connect to snowflakes account after changing datasource setting.

Any idea or help where I can easily change the data source setting and avoid working on scratch for visuals without connecting to snowflakes account.

Thank you,

Pallavi

[FarhanJeelani · Accepted solution]

Hi @pallavi_r ,

The issue you're facing occurs because Power BI ties the data source and model to specific connection details. Simply changing the data source setting won't work if the schema or connection type differs significantly, as Power BI needs access to validate and remap the queries.

Steps to Resolve:
1. Export the PBIX File:
- Open the PBIX file in Power BI Desktop. If it prompts for the Snowflake connection, cancel the connection but ensure you can still access the file's structure.

2. Modify the Query in Power Query:
- Open the Transform Data window.
- For each query linked to the Snowflake source:
- Replace the connection string with the Azure SQL Database connection string.
- Modify the query as needed to match Azure SQL's structure.
- Apply the changes. Power BI will ask for credentials; provide those for Azure SQL Database.

3. Ensure Schema Compatibility:
- The table and column names in the Azure SQL Database must match those in Snowflake.
- If there are differences, update the queries or use transformations in Power Query to align the schema.

4. Update the Data Model:
- Once the queries are updated and data loads successfully, verify the relationships and calculated measures in the model.
- Adjust any measures or visuals that may break due to schema differences.

5. Preserve Visuals:
- Your visuals and dashboards should remain intact if the fields and tables remain consistent in the data model.

6. Alternative Using Power BI Dataflows (Optional):
- If schema differences are significant, consider using a Power BI Dataflow to preprocess the data from Azure SQL Database. This helps in standardizing the schema to match the existing Snowflake model.

7. Re-publish the Report:
- Save the PBIX file and republish it to the Power BI Service, connecting it to the new Azure SQL Database source.

If you still encounter issues where Power BI insists on connecting to Snowflake, try clearing the cache for Snowflake connections from File > Options and Settings > Data Source Settings and then proceed with the steps above.

Please mark this as solution if it helps. Appreciate Kudos.

[pallavi_r]

Thank you @FarhanJeelani  for responding and confirming in such an elaborated steps. Really appreciate your effort here. I have tried exactly the same steps and  stuck in some error. But really big thanks to you for confirming the steps. 

Thanks,

Pallavi