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