Recently, I upgraded our core Azure SQL Database for MySQL Single Server to Azure SQL Database for MySQL Flex Server. The migration was pretty straight forward and I simply did an export with mysqldump and then an import with MySQL. The problem came up when I tried to use Power BI (PBI) and connect to to the Azure SQL Database for MySQL Flex Server instead. When I tried to connect PBI to the Flex server I kept getting “The given key was not present in the dictionary”.
After a ton of searching, and some troubleshooting tips from two differing Microsoft Data Platform MVPs (who both know WAY more about Power BI than I do) I was getting nowhere. A third Microsoft Data Platform MVP suggested that I try using the MariaDB database driver instead of the MySQL Database driver, and after a little tweaking to get the data sources to use the new driver it worked like a charm. Which leaves me in the increadly awkard position of having to write a blog post about solving a Power BI problem.
The first step is going to be to download the MariaDB driver. There’s a bunch of options, but you’ll want the ODBC driver for Microsoft Windows. If you are using the 64 version of PBI (you probably are) you’ll want the 64 bit driver as well.
Once that is done setup a new data source in PBI to your MariaDB database drive, using the same connection information that you use for your MySQL database server. In my case it was “dcac-{something}.mysql.database.azure.com” and the same database, again in our case dcac. You don’t need to select all of the table that you are going to use. Just select one and close the window, you can then delete that table from the PBI editor as we’re going to use the same query objects that we were using before so that we don’t need to make any other edits to the report.
For each query, click the Advanced Editor button that’s within the Query section of the menu bar. That’ll open the advanced editor for that query. There’s going to be two changes that need to be made. The first is the file line of the code, which is the “source=” line. Look for something that looks like this.
Source = MySQL.Database("dcac-{something}.mysql.database.azure.com", "dcac", [ReturnSingleDatabase=true]),
Change that code just a little bit. You’ll want to change MySQL.Database to MariaDB.Contents and change {ReturnSingleDatabase=true] to just true like I have shown below. The database server and database name (dcac) should be yours.
Source = MariaDB.Contents("dcac-{something}.mysql.database.azure.com", "dcac", true),The second thing to change is the next line. In the next line it defines the table that you’re going to download into PBI. wp_users_Table is my variable name. wp_users is the table name. The MySQL version of the line is shown below.
wp_users_Table = Source{[Schema="dcac",Item="wp_users"]}[Data],There’s two changes that we need to make. We need to change the “Item” keyword to “Name” and we need to remove the “Schema” key and replace it with the “Kind” key and give that a value of “Table”.
wp_users_Table = Source{[Name="wp_users",Kind="Table"]}[Data]Once we make these two changes, and refresh the preview, everything should work as normal.
Keep in mind that if you have a scheduled refresh you’ll need to install the MariaDB driver on the gateway server, and assign the data source to the gateway.
Power BI professionals, I hope I did you all proud by writing this.
Denny
One Response