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
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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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

      • ServiceAcntGvg's avatar
        ServiceAcntGvg
        Regular Visitor
        7 years ago

        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