Forum Discussion
How to get the sql database table name in the dataset
Martin,
Your answer is amazingly helpful. There are a lot of new things that I will need time to digest and put into practice. Is it possible to suggest the most feasible way for the scenario below?
Our organization is moving the database from an existing on-premises server to Snowflake cloud. We will need to update the dataset in Power BI to redirect to the new location. We will have a one-to-one match list of the existing table/view name, database, and server name to future Snowflake table/view name. Since there are hundreds of Power BI reports that would be impacted, I am working to write a script either in Python or PowerShell to automate the transformation.
A little more detail: our company is on the premium capacity, and I am on the fabric administration role. The Power BI spaces that I would need to work with to redirect the dataset resource location include both premium workspaces and pro workspaces. The power query is typically simple without incremental change (but some of the tables are refreshed on schedule). That is to say, most of the datasets only have one query (one query could load multiple SQL table/view to do ETL in power query). I will need to write a script to find all SQL tables/views in Power Query to redirect them to the future Snowflake storage location. Do you have any suggestions for the best route? I don't mind to work on the power query parsing as long as I can get the power query script and update it with new table name in the Snowflake.
Thanks for your kind help in advance,
Boji
Hi Boji ,
Since the PowerQuery command to load from SQL Server is SQL.Database or SQL.Databases and for Snowfalke it's Snowflake.Databases you need to change the PowerQueries anyway. You cannot easily use steps ike "change datasource", which is more useful like for switching from dev to prod database withing the same technology.
If you are lucky, then you just need to replace the first lines of a PowerQuery that gets it data from SQL Server into lines that get the same table from Snowflake. Create one PowerQuery from Snowflake manually and compare the code pattern for SQL Server and Snowflake sources. Then you can write code to extract the database and table/view names, generate the Snowflake PowerQuery code and replace the lines in the PowerQuery code.
Now, the tricky part might be how to access, read, and change the PowerQuery code from a script. Basically you have two options:
- XMLA API, see above. But I'm not sure whether Power BI allows to change the PowerQuery code through XMLA. You would have to try. You can do an intial try easily using Tabular Editor and Power BI Premium dataset. This approach has two pitfalls: a) Will definitely not work with Pro workspace. b) You cannot download the pbix file anymore after modifying the dataset metadata from the XMLA API.
- Use pbi-tools Welcome to pbi-tools | pbi-tools You can write script to automatically download the pbix files, disassemble them into text files using pbi-tools (which is scriptable), modify the PowerQueries, reassemble the pbix file using pbi-tools, publish pbix file back to the workspace. Once set up, as long as you have pretty standard PowerQueries to load from SQL Server, this way you can fully automate the process.
Kind regards,
Martin