Outdated Version

You are viewing an older version of this section. View current production version.

Connecting to Power BI

This topic describes how to connect Microsoft Power BI to MemSQL.

The certification matrix below shows the latest versions tested for MemSQL and Looker:

Certification Matrix Versions
Power BI Desktop 2.6x, 2.7x, 2.8x
MemSQL Database 6.x, 7.x

Data Connectivity Modes

Using the MemSQL Direct Query Connector in Power BI, you can connect to a MemSQL data source in Import or DirectQuery mode.

When you use Import mode, PowerBI imports the data from the data source. If the data in the data source changes, you will need to refresh the data source to see the changes.

When you use DirectQuery mode, you work directly with the data source.

For a further explanation of the Import and DirectQuery modes, see this article.

Prerequisites

Follow these steps to download and install the prerequisite software.

  1. Download and install the latest version of Power BI Desktop listed in the certification matrix above.

  2. Download the MemSQL Direct Query Connector 1.0 file. After you have downloaded the file, put it in directory C:\Users\YourUserName\Documents\Microsoft Power BI Desktop\Custom Connectors.

  3. Download and install the MySQL ODBC driver as explained in the following section. The MySQL ODBC 5.3 driver requires that the Visual C++ Redistributable Packages for Visual Studio 2013 be installed first.

Download and Install ODBC Connector for MySQL

  1. Navigate to the MYSQL ODBC Connector site.

  2. Select Product Version as 5.3.12 and then choose your Operating System and OS Version from the drop-down lists. Newer 8.x versions of the ODBC connector may require additional configuration changes when connecting to a MemSQL cluster. See the MemSQL documentation for more information.

    image

  3. Pick the appropriate installer option for your system and then click Download. After the download finishes, install the driver.

Configure MySQL ODBC Connector

You need an existing ODBC connection before connecting Tableau with MemSQL.

  1. If you are using Windows, open the search bar and type ODBC.

  2. Click Set up data sources (ODBC).

  3. Configure a new ODBC data source for MemSQL.

    a. Specify TCP/IP Server and Port as 3306.

    b. Specify User, Password, and Database.

    image

  4. Click Test to test the connectivity and click OK.

Connecting Power BI to MemSQL

  1. Open Power BI Desktop. On the menu bar, select File | Options and Settings | Options. Then, on the Options screen, select Security on the left pane. In the Data Extensions section, select (Not recommended) Allow any extension to load without validation or warning. This will allow PowerBI to use the MemSQL Direct Query Connector 1.0 file that you installed previously. image

  2. In Power BI Desktop, navigate to the ribbon and click Get Data. image

  3. Select More. image

  4. In the Get Data window, select MemSQL Direct Query Connector 1.0 (Beta) on the right. image

  5. The MemSQLDirect Query Connector 1.0 screen appears. On the left pane, select Basic. Then specify the user name and password used to connect to MemSQL and click Connect. image

  6. For server, specify the IP address or host name where the MemSQL master aggregator node resides. Then, select a Data Connectivity mode and click OK. image

  7. Select the table(s) that you want to import and click Load. image

You can now use Power BI to explore your MemSQL data.

To change the credentials used to connect to MemSQL, select File | Options and settings, followed by Data source settings. On the Data source settings screen, select MemSQL DirectQuery Connector 1.0 and click Edit Permissions….