cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gazumpt
Frequent Visitor

MYSQL connection - changing database source requires M query

Hi,

 

I am developing Power BI reports off a Dev MYSQL Database and now want to point the reports at the UAT database. Both databases have the same tables/structure and are hosted on the same server.

When I create the connection to MYSQL, I have to enter a database name. The database name is then added to the start of every table name, eg. Dev Table1, Dev Table2, Dev Table3. A connection to UAT would have table names UAT Table1, etc.

 

Therefore I cannot simply change the datasource database and refresh the data. I have to change the datasource AND change the M query for each table individually in the Advanced Editor, example below.

 

Original Table Query

let

    Source = MySQL.Database("000.000.00.00", "Dev", [ReturnSingleDatabase=true]),

    Dev_Table1 = Source{[Schema="Dev",Item=" Table1 "]}[Data]

in

    Dev_ Table1

 

New Table Query

let

    Source = MySQL.Database("000.000.00.00", " UAT", [ReturnSingleDatabase=true]),

    UAT_ Table1 = Source{[Schema=" UAT",Item=" Table1 "]}[Data]

in

    UAT_ Table1

 

This becomes more complicated if steps were applied in the query editor (column renaming, etc). Changing each table is fine for one small report but when you want to change 30 large reports from Dev to UAT it is time-consuming and error-prone.

 

In comparison, a SQL server connection does not add the database name to the start of the table name, so I can simply change the database in the data source settings and refresh.

 

Any help/advice would be appreciated, even if it is just a better workaround then the one I am using.

 

Thanks

7 REPLIES 7
Microsoft
Microsoft

Re: MYSQL connection - changing database source requires M query

Hi @gazumpt,

In  your scenario, you can create parameter for database names in Query Editor as shown in the following screenshot.
1.PNG

After you create reports for your Dev MySQL Database, you are able to switch the database to UAT database by changing the parameters’ values.
2.PNG

For more details about creating parameter in Power BI Desktop, please review the following blog:
http://biinsight.com/power-bi-desktop-query-parameters-part-1/


Another method is to paste the codes of Advanced Editor to Text file, press Ctrl+H, replace "Dev" with "UAT" in the codes, then copy the codes back to Advanced Editor.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gazumpt
Frequent Visitor

Re: MYSQL connection - changing database source requires M query

Hi Lydia,

 

Thanks for your reply. Unfortunately, that does not solve the issue - the table names remain with the original database prefixed and must be changed one by one.

I think that the issue here is that there is no difference between a schema and a database in MYSQL; they are synonymous. 

 

MYSQL is almost unique in this regard. If I make a connection to SQL Server, Oracle, etc with staging and dimension schemas and import stage.table1 and dim.table1, the table names will keep the schema prefixed in order to differentiate tables from different schemas, ie. stage table1, dim table1, which makes sense. In MYSQL the schema and the database are the same thing; so the database name is prefixed to the table.

So, I need Power BI to accept the database name while ignoring the schema name (which is the same thing) when importing from Dev and also when I change the database to UAT. 

Not sure if this can be done using parameters or by adding a SQL query to the connection?

 

Thanks

Microsoft
Microsoft

Re: MYSQL connection - changing database source requires M query

Hi @gazumpt,

Could you please post a screenshot about your scenario when using parameter to define database name of MySQL? We are not able to make Power BI to accept the database name while ignoring the schema name.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gazumpt
Frequent Visitor

Re: MYSQL connection - changing database source requires M query

Hi @v-yuezhe-msft

 

Thanks again for your help with this. However, I cannot provide screenshots as the data is sensitive. 

I can tell you that the issue is exactly the same using parameters as without using parameters. 

For example, I created a database parameter with Dev and UAT. 

I set the parameter to Dev and import the data without issue. The table query in Advanced Editor is below - 

let

    Source = MySQL.Database("000.000.00.00", "Dev", [ReturnSingleDatabase=true]),

    Dev_Table1 = Source{[Schema="Dev",Item=" Table1 "]}[Data]

in

    Dev_ Table1

 

Then I change the parameter value to UAT and apply the changes. The data fails to refresh, giving the error 'The key didn't match any rows in the table.'

Looking at the table query, we can see why - 

let

    Source = MySQL.Database("000.000.00.00", "UAT", [ReturnSingleDatabase=true]),

    Dev_Table1 = Source{[Schema="Dev",Item=" Table1 "]}[Data]

in

    Dev_ Table1

 

The database name has been changed to UAT, but the schema name and table prefix remains on Dev. 

 

Just to mention, I am also working with SSRS on this project and can change mysql databases without issue because it does not prefix each table with the database/schema name.

With Power BI, however, I have to update each table query.

 

Thanks

Microsoft
Microsoft

Re: MYSQL connection - changing database source requires M query

Hi @gazumpt,


The parameter works without issues when I use SQL Server databases.

I made a test using MySQL databases and utilizing parameter, and I get the same error as yours. As Power BI Desktop prefixs each MySQL table with the database/schema name, we are only able to change the code in Advanced Editor using the second alternative in my original post.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft
Microsoft

Re: MYSQL connection - changing database source requires M query

@gazumpt,

After creating the databasename parameter in your Query Editor, change the code in your Advanced Editor to the following:

let
    Source = MySQL.Database("000.000.00.00", ""&databasename&"", [ReturnSingleDatabase=true]),
    Table1 = Source{[Schema=""&databasename&"",Item=" Table1 "]}[Data]
in
    Table1


This way, you don't need to manually edit the schema name in Advanced Editor.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ServiceAcntGvg
Regular Visitor

Re: MYSQL connection - changing database source requires M query

Hello,

 

I do face this issue. This one is the critical issue for me. When ever i do changes to reports on demo and to make same changes to the production reports, i will be renaming the table names which is painfull to do every time since the schema name is not dynamic and also the table name prefixed by the schema name. i have a parameter for Server and Database name but for schema i don't have any. In advanced editor source schema is not dynamically changing as and when db/server changes. How to over come this.

 

Is their any thing like passing schema name through parameter and also disable the prefixing schema name with table name?

 

Thanks and Regards

Pallavi

 

Helpful resources

Announcements
June 2020 Community Challenge: Can You Solve These?

June 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors