MySql Import Issues with Tabular and OldGuids

In my last post, I looked at a MySql connection property that causes loads to fail in Power BI and how you can work around that issue (see here).  In this post, I’ll look at those implications in SSAS Tabular.  In this example, I’ve downloaded the MySql ODBC drivers (both x32 and x64) from here and created ODBC connections (x32 and x64 with the same name) to my MySQL database.

Data is initially imported through the Table Wizard where I have to choose the relational source Others ( OLEDB/ODBC).  This brings me to the Table Import Wizard window (below) where I can choose my ODBC Connection.  Testing the connection proves successful.  So you navigate next a few times until you can chose to import from a list of tables or write a query.

image

If you choose to select tables (and any) of the tables have Guids in them, the entire load will fail and you are presented with the following errors.  image

Here the error is raised by the SQL generated because it is not compliant with MySQL (that is the SQL generated is “Select [calls].* from [calls]” and MySql does not like square brackets).

Far enough, so you continue.  Now that the connection has been created in the model all you need to do to import data is Open the existing connection (existing connections in the model can be viewed by clicking the Connections button in the ribbon (image) or by selecting “Existing Connections..” from the Model Menu group).  You can then open that connection (either Double Click it or highlight it and press the Open button) and you are presented with the select from tables or write a query).  This time you specify the query (Select * From Calls).  The query validates however, a new error is presented when you try to load data.  Although not specified, it does relate to the OldGuids property.

image

Now we have to manually edit the connection to specify its properties.  When we do so, we are presented with the following window.  Now I want to build the connection string so specify connection string and select the build option.  Then I can choose the machine data source (btw I set up my ODBC connections as machine sources).

image

Now I am taken to the drivers creation window (as below).  I don’t need to change anything here (since the connection works anyway), so I will accept the defaults and hit OK.  (Note that the OldGuids property is not an option in the details section of the drivers configuration)

image

My Connection String has now been specified (note the additional properties in the window and snippet below) and I can add other properties as I need.

image

Before:

DSN=dsnname;SERVER=server;UID=username;PWD=password;DATABASE=databasename;PORT=3306

After:

DSN=dsnname;SERVER=server;UID=username;PWD=password;DATABASE=databasename;PORT=3306;OldGuids=true;

Then I simply specify my impersonation and I am right to go (noting that I must write a query).

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