One of the great things about self service BI is the ability to model data from different sources. Recently, I have been using MySql sources against different versions of Tabular Models and have run into some issues. This post shows those issues and how to get around them in Power BI Desktop designer (well at least how I did).
The import into Power BI Desktop appears straight forward, with MySQL listed as a source. There are some dependencies on drivers (which are not installed by default) however, the installations of those is quite simple. So lets move directly to the Import Data screen (as below).
After specifying the server, database and connection information, you come up with a familiar list of tables applicable to the database (like the s/s below). Selecting some tables gave the usual preview however, at one selection, an error arose relating to an OldGuids option in the connection and then, no tables were available in preview mode.
So I chose to Edit the M query and are straight away presented with the same error. Note that the query setting errors on the Navigation step (or Applied Steps) and, had it navigated to source, I could see a list of tables within the database schema.
The source of the table specifies the database (both server and catalog). In fact, you can see this in generated M as shown below.
= MySQL.Database(“myservername”, “catalogname”, [ReturnSingleDatabase=true])
To enable OldGuids in the connection string, simply specify it as a property in the source. Therefore, the ‘Source’ syntax now becomes;
= MySQL.Database(“myservername”, “catalogname”, [ReturnSingleDatabase=true, OldGuids=true])
Depending on the schema of the database, this would have to be done for any table that you want to import which has throws an error.
Now, I cant help feeling that that the simple import wizard creates an unnecessary step in the M script. After all, why specify the source database and then restrict the output to a single table in it? The natural way around this seems to would appear to import just a table (via an SQL statement). The Code for this is more elegant (IMO) however, the requirement for setting the OldGuids property is still required. This time, when I add data, I’ll specify the query (as an SQL statement). My import import and error looks like the following;
Unfortunately, when I Edit the error here, I am returned to the basic import screen (on the left) and can not do any ‘advanced editing’ (like setting the OldGuids property). The only option I have here is to specify my query as a custom M Script using a blank query, then use the advanced editor to provide the M syntax for a query.
= MySQL.Database(“myservername”, “catalogname”, [Query=”select * from calls”, ReturnSingleDatabase=true, OldGuids=true])
One alternative method to creating a blank query was to force a valid SQL statement to and then edit the query. Here, I specify that I will get data from a MySql database, and choose to enter the query. Note my query (below) is simply “Select 1 from dual”
I can then choose to edit that query which brings me directly to the Power Query editor and I can hack the M from there. That is, the window below opens. I can then chose the advance editor option to edit the M (changing the query and adding the OldGuids Option) or type those changes directly into the formula bar.