Forum Discussion
Is it Possible to Parameterize Server Type - Switch From MySQL to SQL Server?
edited for clarity
Is it possible to parameterize the database type I'm connecting to? I want to change from MySQL server to SQL Server.
My reports query databases hosted on a MySQL server. My company is transitioning away from MySQL to SQL Server. We've replicated all server objects found on our MySQL server in SQL Server (tables, views, sprocs all use same names). As table, view, and sproc names haven't changed the PBI report queries shouldn't need to change. Some queries are simple select *s against views, some are more complex with joins and such. There aren't any syntax issues or the like (using functions not found in T SQL, etc.) to worry about in my queries.
I simply want to point the queries towards a SQL Server datasource with the option of choosing between MySQL and SQL Server. Then, if I needed to I could switch back and forth between the data hosted on MySQL vs. the data hosted on SQL Server.
Below: I want to change connection prefix from MySQL.Database to Sql.Database
I ended up doing it by making Source conditional, based off value of parameter, within the Power Query advanced editor.
I'm using the approach below.
let Source =if server= "xerxes" then MySQL.Database("xerxes.corp.brillo.com", "brilloweb_api", [Query="select *#(lf)from products;", ReturnSingleDatabase=true]) else if server = "alexander" then Sql.Database("alexander.corp.brillo.com", "brilloweb_api", [Query="SELECT * FROM products p;"]) else MySQL.Database("xerxes.corp.brillo.com", "brilloweb_api", [Query="select *#(lf)from products;", ReturnSingleDatabase=true]) in Source
8 Replies
Are you using Query Folding? Are you using native queries?
I believe folding. Simple select * against data warehouse table. When I right click 'Source' under APPLIED STEPS, view native query is greyed out. I think that indicates I'm folding?
unfortunately it (mostly) means the opposite. You can only be certain though when you run the query diagnostics. Sometimes the "View native query" is greyed out but the diagnostics still show a folded query call.
Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn
Are you crafting your own queries? Are you setting the "EnableFolding" attribute?
Query folding on native queries - Power Query | Microsoft Learn
Running native queries I write. Thank you for the links/info.
Ok here's my proposal:
in your Power Query script have both the SQL and the MySQL Sources. Make the query dynamic so that the "inactive" connection runs something like "Select top 0 ... ", and the "active" connection runs your actual query. Append both queries.
I ended up doing it by making Source conditional, based off value of parameter, within the Power Query advanced editor.
I'm using the approach below.
let Source =if server= "xerxes" then MySQL.Database("xerxes.corp.brillo.com", "brilloweb_api", [Query="select *#(lf)from products;", ReturnSingleDatabase=true]) else if server = "alexander" then Sql.Database("alexander.corp.brillo.com", "brilloweb_api", [Query="SELECT * FROM products p;"]) else MySQL.Database("xerxes.corp.brillo.com", "brilloweb_api", [Query="select *#(lf)from products;", ReturnSingleDatabase=true]) in Source
