Is it Possible to Parameterize Server Type - Switch From MySQL to SQL Server?

[jrieman]

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 

[jrieman]

Running native queries I write.  Thank you for the links/info. 

[lbendlin]

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.

[jrieman · Accepted solution]

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

[lbendlin]

Your code is redundant - you could fold the SQL server into the Else branch.

Does the refresh work on the Power BI Service?

[jrieman]

Yes it refreshes, though the visual didn't reflect the new data until I went into edit mode (within service) and selected refresh.  

[lbendlin]

Are you using Query Folding?  Are you using native queries?

[jrieman]

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?  

[lbendlin]

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