MySql Import Issues with Power BI and OldGuids


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

image

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.

image

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.

image

image

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;

imageimage

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.

image

= MySQL.Database(“myservername”, “catalogname”, [Query=”select * from calls”, ReturnSingleDatabase=true, OldGuids=true])

There is some high level documentation on the MS Site about importing from mySQL here and here (including perquisites).

Alternatively

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

image

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.

image

Advertisements

2 thoughts on “MySql Import Issues with Power BI and OldGuids

  1. Pingback: MySql Import Issues with Tabular and OldGuids | Paul te Braak

  2. Very Helpful, thank you for taking the time to explain clearly. With your fix, I was able to “Get Data” from my external cloud based; commerce, CRM databases & integrate with our internal sources for some amazing insights.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s