Forum Discussion

gazumpt's avatar
gazumpt
Frequent Visitor
9 years ago

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 serve...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi gazumpt,

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


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


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

  • gazumpt's avatar
    gazumpt
    Frequent Visitor
    9 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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

      • gazumpt's avatar
        gazumpt
        Frequent Visitor
        9 years ago

        Hi Anonymous, 

         

        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